Watch Brent Tune Queries
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 PASS SQLRally Nordic conference in Copenhagen. For the scripts, database, techniques, and tools, keep reading below.
The Demo Database & Scripts
Download the Stack Overflow database – it’s available in 10GB, 50GB, and much larger sizes. Any of them will work for these demos, but will of course produce dramatically different execution plans depending on the database size, your hardware, your version of SQL Server, etc.
Download the latest version of my demo script – the above video was recorded at a conference in 2015, and I’ve continually fleshed out the demo script more and more over time – tackling more queries, using different techniques.
Download other demo scripts – if you liked the above one, then here are two others you might enjoy: Watch Brent Tune Queries #2: The SQL (TopPeopleForTag) and Watch Brent Tune Queries #3 2016: The Optimizer Strikes Back. Both use the Stack Overflow database as well.
BE CREEPY: The Process I Use
BE CREEPY stands for:
- Blitz first for obvious problems – use sp_Blitz® looking for SQL Server settings that are causing overall slowness. Use sp_BlitzIndex® looking for heaps, disabled indexes, missing indexes.
- 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 query – now that you have the actual metrics, read through the query to see if you can notice common anti-patterns that might be causing the query’s bad metrics, like high CPU, high reads, or high memory grants.
- 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.
Free Tools I Use for Query Tuning
During a Watch Brent Tune Queries session, I often use some of these tools – but depending on the session length, I may not get to all of ’em:
- StatisticsParser.com – copy/paste the output from SET STATISTICS IO, TIME ON into here, and you’ll get an Excel-style grid of total numbers across multiple batches. Be aware that if actual execution plans is turned on for your batch, the stats numbers may double up.
- SentryOne Plan Explorer – free execution plan viewer with more power than SSMS.
- How I Configure SSMS – lots of little tips to make query tuning easier.
- Live Query Execution Plans – see what your query is up to, in real time. Requires 2014 SP1 on the back end and 2016 SSMS (or newer, and really, you should be on the newest SSMS. It’s free.)
- Comparing Execution Plans – best for comparing two single-statement queries, not complex stored procedures.
Where to Go to Learn More
- Fundamentals of Query Tuning – my 1-day class to get you started reading execution plans and determining where to focus your tuning efforts. Available either as a live online class, or an inexpensive recording.
- Mastering Query Tuning – after graduating from Fundamentals, this is the top level: 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.
- Fundamentals of Index Tuning and Mastering Index Tuning – a separate discipline where you modify the database to make crappy queries go faster.
- Consulting: SQL Critical Care® – if you haven’t got time for the pain, you can hire me to mentor you through the process of making your SQL Server apps go faster, quickly.