Product has been added to your cart.

Watch Brent Tune Queries

Ever wonder somebody else does it? Watch over my shoulder for a series of one-hour tuning sessions, each tackling a different query with different challenges.

If you watched me do it remotely at a user group in 2020, here’s the query I tuned.

At SQLRally Nordic, Copenhagen 2015

Start with this one first, when I explained the B.E. C.R.E.E.P.Y. process and tuned these queries:

 

At SQLSaturday Oslo 2020-08

I presented remotely due to the coronavirus travel restrictions. Here is the query I tuned, and I used the 2018-06 Stack Overflow database. In this session, I do it the “wrong” way, starting with the last parts of the B.E. C.R.E.E.P.Y. process, and it doesn’t go well:

Watch Brent Tune Queries 2020-01 Live Stream

I use the Stack Overflow 2013 database in this one, and here is the query I’m tuning.

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.

Demo scripts with me tuning different queries:

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.