Follow Datanami:
August 20, 2020

Make it Automatic: Tuning SQL with AI

John Pocknell

(Shutterstock)

The database administrator (DBA) is typically tasked with making applications run more efficiently in order to meet service level agreements (SLAs) or just to ensure optimum user experience. From the users’ perspective, this means faster execution times and quicker application response times. However, from a database systems management perspective, the DBA must pay close attention to overall workload resource usage, both in real-time and long-term.

With multiple resource types across multiple hardware platforms, and add to this, trends like DevOps CI/CD automation and cloud adoption, the DBA’s job and the pressure on them to track SQL execution performance can be quite intensive.  Utilizing Artificial Intelligence (AI) for SQL tuning can help DBAs and developers reap the benefits of a true automated process.

SQL performance tuning, which 78% of database developers say they do manually, can take up large amounts of time; 73% cited it as their biggest time commitment. Automating SQL performance tuning with AI, on the other hand, accounts for changing database workload patterns and identifies opportunities to improve overall performance.

This proactive approach can make the lives of DBAs a whole lot easier. With AI, the time spent on tuning can be exponentially shortened, allowing for more focus to be put on items that add more value to the business.

Here’s what’s important to understand about SQL: How AI can improve SQL performance tuning and why DBAs should implement it, plus what the future of AI-based SQL tuning looks like, particularly as demands from DevOps CI/CD and the cloud create an increased need for it.

SQL statements are at the heard of analytics

Understanding SQL Statements

A SQL statement is something a DBA or a developer writes to extract, insert, update or delete information from the database. Depending on how that SQL statement is written influences how fast it runs in the database. The more complex the statement, the more hours it will take for someone to tune it manually. And this is no simple feat; many SQL statements run slowly in the database and require a lot of experience and judgement from DBAs.

Traditional SQL tuning requires DBAs and developers to understand the execution behavior (per an execution plan) in the database and try to modify the statement, or use an index, to improve its response time. Not only is this labor intensive, it’s also not always successful. Most relational databases have an internal optimizer, which tries to improve SQL execution performance. However, if the SQL statement is not written in an optimal way from the beginning, that approach is limited.

Moreover, internal optimizers suffer from two limitations. First, table and index statistics, used to determine the optimum execution path, may not be up-to-date. Running jobs to collect this information during peak periods uses system resources that can slow down the application. Secondly, inaccurate cost estimation leading to a poor execution plan which will cause slow SQL execution.

Even as autonomous database technology is being touted as a way to automate the process of keeping database running optimally (as well as automated patching, backups, security), they still suffer from the two limitations listed above.

While some popular database vendors provide SQL tuning capabilities, they generally only predict SQL execution behavior and provide advice to the DBA or developer, who then has to use their experience to solve the problem. In the end, this too also only takes up more time and leaves DBAs with a headache.

SQL Tuning with AI

Using AI, tuning of SQL through automatic rewrite technology, is a much more effective approach and can be accomplished in seconds as opposed to what might typically takes several minutes or hours.

(Jozsef Bagota/Shutterstock)

AI provides a way to automatically rewrite the original SQL statement, mimicking human expertise and offers alternative (semantically equivalent) versions of the SQL that will improve its performance together with graphical comparisons and analysis, providing evidence so the DBA or developer can choose the best alternative. This process augments the way the database internal optimizers work so they make better decisions whenever that SQL statement executes in the database.

This makes the DBAs job easier as it provides much better visibility into how SQL is executing in the database and helps to simplify and automate their job. The DBA can still leverage their experience of SQL tuning where appropriate, but the heavy lifting is done using AI. Ultimately, DBAs save massive amounts of time and see better application resiliency. This leaves DBAs with the ability to spend more time on other critical tasks that support service delivery and add business value.

By tuning SQL with AI, companies are much more likely to meet SLAs and have satisfied end-users, which can help improve competitiveness and drive revenue and growth.

Trends Pushing on Future Wide Use of AI in SQL Tuning

As more organizations adopt DevOps and employ agile processes like Continuous Integration and Continuous Development (CI/CD) with much faster release cycles, there will be more pressure to address SQL execution performance more effectively than manual tuning.

Developers may increase reliance on AI to tune SQL in the future

We’ll also see SQL performance become more critical in the area of cloud adoption. Cloud services can end up being more expensive if application performance is slow due to excessive database resource consumption (as a consequence of slow SQL execution). This will require effective performance monitoring, diagnostics and tuning tools to help address this issue and avoid “bill shock” from high cloud subscription bills.

With automated SQL tuning using AI, developers are less likely to need DBAs to assist them, thereby reducing DBA disruption, a common issue in most companies. It also benefits developers by providing them the ability to spend more time building higher quality code to reduce defects and technical debt, further increasing application resiliency. The use of AI for SQL tuning can also make it easier for developers to work more effectively in a CI environment where they are working in short agile sprints.

With around 83% of new structured data coming from e-commerce and other transactional applications, the level of importance of tuning SQL statements is clear. Customers and users are likely impacted directly by poor-performing SQL in those types of applications.

It’s clear that–with the large majority of the effort of tuning SQL appearing to be spent in manual processes–this way of addressing performance tuning takes more time, is likely more prone to error, and is not as comprehensive when compared to automated processes. Tuning SQL using AI is the best approach and, while not exactly new, is now emerging with the evolution of technology as the preferred method for DBAs, and clearly for good reason. Get your SQL working smarter, look into AI.

About the author: John Pocknell is a senior market strategist at Quest Software. Based at the European headquarters in the U.K., John is responsible for synthesizing analyst data and customer interviews in order to create and evangelize solutions-based stories and messaging which relate to major IT initiatives for Quest’s extensive portfolio of database products, worldwide. He has been with Quest Software since 2000, working in the database design, development and deployment product areas and spent over 10 years as product manager for the Toad product line. John has been successfully evangelizing Toad and other database solutions at various conferences and user groups around the world for the last 20 years as well as writing blogs and technical papers both internally and for the media.

Related Items:

Three Machine Learning Tasks Every AI Team Should Automate

How Advances in SQL on Hadoop Are Democratizing Big Data–Part 2

3 Major Things You Should Know About Apache Spark 1.6

 

Datanami