What is automated SQL tuning? Time saving DBA tips.

SQL tuning is needed because structured data is here to stay—even as unstructured data becomes more prevalent and important.

Quest Software® partnered with Unisphere Research to study the virtual avalanche of new kinds and quantities of data that technology has unleashed in recent years. We were able to glimpse into data and retell our discoveries in this white paper, “The Real World of the Database Administrator." According to research by Dr. Elliot King, research analyst at Unisphere Research, a Division of Information Today, Inc., data is here to stay and continues to evolve, and that means SQL tuning is needed. Below is a screen shot of a table from that study.

Structured data means that SQL will need tuning

So, structured data leads us to the need for SQL tuning. Tuning SQL is rarely done with any regularity at your organization?  You’re not alone.  But according to Dr. Elliot’s research:

• Over 73% of database developers (no matter their job title) say that SQL tuning takes the largest time commitment
• Only about 5% of DBAs say that performance tuning, including SQL tuning, is generally not performed

That seems to indicate that overall, SQL tuning is happening quite regularly.  But, another finding in the report is that 78% of performance tuning, including SQL tuning, is done manually.   More on that shortly.

The level of importance of tuning SQL statements is evidenced by the knowledge that around 83% of new structured data is coming from e-commerce and other transactional applications.  Customers and users are likely directly impacted by poor-performing SQL in those types of applications (see the graph below, an excerpt from the King research previously cited).

Deficiencies of database vendor internal SQL optimization

Structured data is currently stored predominantly in relational databases.  You might be asking why is SQL tuning so important since most of the commonly used relational database vendors supply internal, built-in SQL optimization mechanisms that handle re-writing the queries internally before execution, for example. 

There are major deficiencies that cause the database’s internal SQL optimizer to fail to find a satisfactory access plan for a SQL statement, making SQL tuning by developers and DBAs necessary.  To read more about those deficiencies, read the Quest® technical brief, “Solving the SQL tuning problem: Secrets of the Quest SQL Optimizer.” 

The deficiencies, for example, lack of up-to-date statistics and inaccurate calculation of costs, hinder optimization of SQL unless a person (or tool) gets involved in the process. If a tool is used alternative rewrites of the SQL statement can be generated and then examined, and even compared visually with the original.  Costs of the new plans are computed, but decisions on whether a new plan is “better” might be decided based on other criteria, such as the time to return the first row or total execution time. Yes, with SQL Optimizer, not only do new alternative SQL structures get generated, but you can easily execute those alternatives side-by-side to prove which is fastest. You will be able to choose which rewrite of the SQL is best to implement.

Performance tuning, including SQL tuning, can be automated

Why not save time of developers or DBAs, whoever in your organization is responsible for tuning SQL?  Automation of tuning is really important—and is doable.

How much of SQL tuning is automated now?

The King study reports that only about 15% of database workload performance tuning (activities such as rewriting SQL and adding indexes) is currently automated. So, the large majority of the effort of tuning SQL appears to be spent in manual processes.  Manual processes take more time, are not as comprehensive, and are more prone to error when compared to automated processes for tuning SQL.

Time savings you should expect by automating your SQL tuning

The most dramatic time savings are the result of the faster generation of rewritten SQL.  Alternatives can be generated far more rapidly.  Quest customers have reported seeing a long list of possible alternatives in a matter of hours where it would have taken many days for someone to actually rewrite by hand.  The actual time savings that you might experience, of course, depends on many factors unique to your environment, database workloads, and goals for tuning.  

Here’s how automation can work

 With Quest SQL Optimizer, automating SQL tuning means important time-saving capabilities listed below as well as others we will cover in a future post:

1. Click a button and alternative rewrites are generated – configuration options allow you to choose how long the tool keeps generating, and so, how many alternatives and how complex their structural differences are (join syntax changes, etc.).
   1. For Oracle, the Toad menu option to “Auto-Optimize”, assuming you own the XPERT Edition or higher, allows quick generation of alternatives and (if desired) execution of alternatives side-by-side while working in, and remaining in, Toad.
   2. With any rdbms flavors of Quest’s SQL Optimizer tool, you can choose to “Batch” Optimize – have the optimization and the execution of alternatives run in the background while you perform other tasks in Toad or elsewhere on your workstation.
2. Akin to the first option, Index Advice can be generated. Click a button to have them generated for viewing and consideration, or have the SQL statement executed using one or more index sets – then analyze the impact (good or bad) of those indexes on one statement or a multi-statement workload even before you physically set them up on the database.
3. At the click of a button, Scan all the SQL statements that reside in selected schema objects, and quickly spot those that need tuning (those that look “problematic” in the parlance of the Quest tool). Scanning, then tuning potentially problem statements, will help you ensure that only optimal SQL makes its way to production. Note that the Scan feature exists for all dbms flavors of Quest’s SQL Optimizer except for DB2 z/OS.
4. Capture SQL as it executes using Inspect features, then scan the statements to find out if particular recently-run statements are problematic.

All of these features add up to time-savings and optimal tuning efficiency and effectiveness.

As always, examples help, and this blog post by my colleague Jeff Surretsky illustrates much of the time-saving capabilities of SQL Optimizer from Quest Software by pointing you to past blog posts covering aspects of SQL Optimizer in his blog, “9 blogs to help tune your most challenging SQL Statements.”(Note that these posts focus onOracle and SQL Server, but the concepts apply to other dbms types, too).

Share on social media

If you think your colleagues would benefit from this article, share it now on social media with the buttons located at the top of this blog post. Thanks!