If you missed the recent webcast on SQL performance tuning presented by Janis Griffin, you are definitely going to want to watch it on-demand. She packed so much information into 60 minutes, it’s a wonder there weren’t thousands of heads exploding across WebEx – and every nugget of advice was pure gold.
Let’s start with Janis’ 30+ year career as a DBA across multiple platforms, especially Oracle and SQL Server. She knows her stuff and knows how to talk about it. Then add in her specialized expertise in SQL tuning and you’ve got some rock-solid advice that every DBA can use to achieve better query performance.
Janis imparted 5 tips when it comes to SQL performance tuning. Each tip builds on the previous one for a series of steps to follow when you’re looking for what’s causing performance slowdowns. She also briefly discussed how Foglight® for Databases can help, but it was a very short product plug!
Tip 1 – Monitor Wait Time
You need to understand the total amount of time a query spends in the database, and use wait types to figure it out. Dynamic management views (DMVs) generated by the system are a good way to get valuable information about the query. But these are in-memory views, which means they are real-time and don’t persist. Janis went into detail about wait times and provided a query that will store the DMV info for later review.
Tip 2 – Review the Execution Plan
The estimated execution plan can often be wrong for a variety of reasons, so you can use live query statistics to peek in on a running execution plan. When you examine the execution plan, you’ll want to look at CPU/IO costs and row counts, as well as review the predicate information to see how parameters are being interpreted. You’ll also want to review join methods as you look for the most expensive steps in the plan.
Tip 3 – Gather Object Information
Once you’ve identified those expensive steps, Janis recommends looking at the objects underlying them. Get the table definition, review the column information and get row counts. You’re looking for performance degraders that eat up memory. Be sure to examine the WHERE and JOIN clauses, review indexes and index definitions, existing keys and constraints, and look for implicit conversions.
Tip 4 – Find the Driving Table
You’ll want to know the size of the actual data sets in each step, then compare the size of the final result set with the rows examined. Janis recommends a technique called SQL diagramming to map this out, and she walked through a detailed case study to demonstrate the importance of understanding the driving table.
Tip 5 – Engineer Out the Stupid
Hopefully this is something your developers are already doing, but there is still a lot of bad code out there, like the kind that doesn’t include a WHERE clause or that abuses wild cards. Find the dumb stuff and fix that first.
Database tuning takes time and expertise, both when it comes to finding which statements to tune and then actually improving them. It’s also an ongoing process with numerous opportunities for success and virtually guaranteed job security.
If you find the whole thing overwhelming, this is a great session to give you a step-by-step, repeatable methodology for SQL query optimization. Janis provided a clear approach and combined it with real-world case studies to show how to find issues and fix them.
