SQL Tuning – Let SQL Optimizer Do The Work For You

The SQL Tuning Problem

If you ask most DBA's what their #1 pain point is the likely response would be database performance issues.  The quickest and least expensive way to solve database performance issues is to try and tune the database workload. Each day I see more and more webcasts, blogs, tweets, etc. that are offering SQL tuning advice. Learning how to tune SQL and becoming proficient at it can be a challenging and rewarding endeavor but takes time and still requires lots of trial and error. There are many software tools that can assist in the process but they still require knowledge of SQL tuning methods. SQL Optimizer offers a different approach which almost completely automates the task of tuning SQL.

How SQL Optimizer Can Help

SQL Optimizer uses a variety of techniques to take your SQL statements and rewrite them in as many ways possible guaranteeing that each rewrite will return the same result set as the original. Rather than relying solely on the optimizer cost, SQL Optimizer can then test each rewrite to see which one in fact performs the best. But performance isn't always the best option if that performance comes at the cost of increased resource usage so SQL Optimizer can help you figure that out as well.  Simply put, SQL Optimizer does the work for you and then let's you decide how to proceed.

A Simple Example

Not too long ago, I was doing some data analysis work against a database running on my laptop. Most of my SQL statements were running fast – especially since my database was using SSD storage and my database server has plenty of RAM available.  Then I came across a statement that was causing me problems. It took over 19 minutes for the statement to execute. I tried a couple of simple changes but they weren't helping. I could have taken some time to try and research the problem and see if I could tune my statement, but I was in the middle of trying to get some data analysis done and taking time out to try and tune SQL was not a good option. Another concern with trying to tune the statement is that I would have had to make sure that each rewrite returned the same results.

From 19 Minutes To Less Than 10 Seconds

I turned to SQL Optimizer to help me solve the problem. Using the Optimize SQL module, I was able to find a rewrite that  executed in less than 10 seconds. Considering that my original ran for more than 19 minutes that is better than 100x improvement. This rewrite gave me an additional bonus by significantly reducing my logical reads.