Watch Brent Tune Queries
Ever wonder somebody else does it? Watch over my shoulder for a one-hour query and index tuning session from the SQLRally Nordic 2015 conference in Copenhagen:
The Database We’re Tuning: Stack Overflow
Instead of AdventureWorks, let’s work on something a little more exciting. StackExchange, the people behind StackOverflow.com and DBA.StackExchange.com, make all of their databases available with a Creative Commons license.
I’ve written instructions on how to download the Stack Overflow database via BitTorrent. It’s about 100GB of data, which is much bigger than AdventureWorks, and produces a lot more realistic query plans.
The Demo Scripts
Here’s the demo scripts that I walk through, with my brain dump in the query itself:
- Watch Brent Tune Queries #1: Query #466
- Watch Brent Tune Queries #2: The SQL (TopPeopleForTag)
- Watch Brent Tune Queries #3 2016: The Optimizer Strikes Back
BE CREEPY: The Process I Use
BE CREEPY stands for:
End user requirements gathering – define your finish line, when end users will let you quit tuning this query and go on to something else. Get the business purpose of the query output. Can we run it less often, or run it somewhere else?
Capture query metrics – run the query with SET STATISTICS IO ON, and gather the actual execution plan. Make sure you’ve got the right query and the right plan by double-checking it against the plan cache. Start a separate SSMS window to compare it before and after.
Read the metrics – identify the logical reads, CPU time, duration, and query cost. Which of these is the biggest problem? Does the query’s duration/reads/CPU match up with the amount of work it’s doing, or is there something in the engine that’s taking a long time to do just a little work?
Experiment with the query cost – try various quick fixes to remove common T-SQL anti-patterns. Remove the ORDER BY, remove the list of fields in the SELECT, switch from table variables to temp tables, check the join types, etc. If these easy fixes take you across the finish line, start asking tough questions about what we’re doing in the query.
Execution plan review – look at the plan’s properties for Optimization Level and the Reason for Early Termination. Look at the top right operator in the plan – are there implicit conversion or SARGability problems? Are the estimated versus actual row counts way off? Fix these, and scan through the rest of the plan looking for these problems.
Parallelism opportunities – is the query going parallel, and if so, is it helping? If it’s not, can we go parallel and go faster? Check for parallelism inhibitors in the query.
Index opportunities – yes, technically this is an i instead of a y, but hey, nobody’s perfect. This is last – we don’t want to rapidly change the database to match our query. Indexes are a later resort for individual query tuning, not the first.
Another Episode of Watch Brent Tune Queries
Wanna see another episode with a different query? Here’s a session I did for the DBA Fundamentals Virtual Chapter:
Where to Go to Learn More
Mastering Query Tuning – my live 3-day class where you work in a VM alongside me. I give you a challenging set of workloads using the Stack Overflow database, and you make ’em go faster. Then, you get to watch me do it, and see how I handle it live.
How to Read Execution Plans – 5+ hour video course by Jeremiah Peschka that teaches you how to read plans, understand how different query syntax changes plans, explore the plan cache to find problem queries, and learn solutions to common query problems.
How to Tune Indexes – 6+ hour video course by Kendra Little. You’ll learn how to analyze and understand not just nonclustered indexes, but includes, filtered indexes, DMVs, and anti-patterns.