About a year ago, as I was digging deeper into SQL tuning issues, I came across this StackOverflow question about SQL books and resources. One of the recommendations was for Dan Tow’s book, “SQL Tuning”. The Amazon reviews were fairly glowing, so I made the purchase, and have since had the opportunity to read and apply its ideas. Here are my thoughts.
Overview
SQL Tuning takes a very unique approach to the problem of optimizing SQL queries. Most of the time, books and articles on SQL server performance outline a method that looks something like:
- Identify slow queries
- Read and understand the SQL execution plan
- ??
- Profit!
Not really, of course, but its very easy to focus on the first two steps of this process, because it much easier to define concrete methods for them. Step 3 frequently ends up being mostly trial-and-error. Update stats. Play with indexes. Update stats WITH FULLSCAN. Change the logic in your WHERE clause. Hey, did you try updating stats yet??
Even if you do have an idea for an execution plan that might be better than the current one, how do you know if it is the best? How do you know you aren’t still leaving huge potential improvements on the table? How do you know when it’s good enough?
Dan Tow’s book teaches a concrete, repeatable methodology for determining the optimal execution plan for a query (or at least a very-near optimal solution), then shows you how to modify your query to use that new plan. The technique is really database agnostic, but he gives good specific methods for applying his ideas in MS SQL, Oracle, and DB2.
Technique
Without giving away too much, Dan’s technique involves creating a visual diagram of the query (based on joins) and with some fairly easy-to-follow rules, helps you determine:
- Optimal table join order
- Optimal indexes on each table
Its based primarily on the theory that you want SQL to retrieve the most selective tables first, so that it is handling the minimum amount of data possible during the entire process, as opposed to joining huge tables and only filtering them out at the end. This idea really is common sense, but for those who like to see the theory behind the technique, he gives a lot of good support to his arguments.
My Thoughts
The method made a lot of sense to me, but did it work? The first day I tried to apply his methods, I was able to take a very long running query (90 minutes or more), and get it down to under 2 minutes!! Can’t argue with that!
Even more than that, though, this book has helped me much better understand what is going on during the execution of a query, and how to bend it to my will (evil laugh). The right index for a table, for example, will depend on where in the query the table is used. Is it the first table retrieved? Is it retrieved in a join?
A few caveats:
- This was published in 2004, so doesn’t contain any of the new features of SQL 2005 or 2008. SQL 2005 Plan Guides, for example, would probably be preferred to using query hints to actually control the plan behavior.
- Continue monitoring your application. Hard-coding query hints could become a detriment instead of a help if your database contents change radically enough.
- Some of the methods can get fairly complex (calculating the proper weight of subqueries, etc).
Overall, though, I thought it was an excellent read, and very worthwhile.
Resources
- O’Reilly Catalog Listing
- SQL Tuning at Amazon (not an affiliate link)
- Dan Tow’s home page (SQL Consulting services and training)