Make it Automatic: Tuning SQL with AI

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.

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.

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.