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 Queries and Tools in the Training
Here’s the demo scripts that I walk through, with my brain dump in the query itself:
- Watch Brent Tune Queries Part 1
- Watch Brent Tune Queries Part 2: The SQL (TopPeopleForTag)
- Watch Brent Tune Queries 2016: The Optimizer Strikes Back
The queries I tune in the webcast are:
- Which posts have had the most up and down votes? Originally written by Sam Saffron – http://stackexchange.com/users/9321/sam-saffron. I’ve taken out the SET NOCOUNT stuff because that doesn’t influence our results here.
- Which recent posts might need to be flagged for moderation? Originally written by MetaFight – http://stackexchange.com/users/276382/metafight. I’ve made a few changes to fix the PostType.Type name field, remove a commented line for clarity, and changed the DATEDIFF filter to look farther back in history since the public data dumps don’t come out too often.
- On average, how often are my answers accepted? Originally written by Sam Saffron – http://stackexchange.com/users/9321/sam-saffron. I turned this one into a stored procedure to demonstrate parameter sniffing.
The demoed tools include:
- StatisticsParser.com – an easy web-based way to add up the total number of logical reads in a query. Written by Richie Rump.
- SQL Sentry Plan Explorer – a much better way to view execution plans. There’s both a free version and the paid version, and every single developer and DBA should have the free version installed.
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
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.
Our In-Person Training Classes – we teach 4-day performance tuning classes that turn you into a performance tuning pro.