Following on from my Learner’s Guide to SQL Server Performance Triage, I’m tackling Query Tuning. In this guide, I’m experimenting with an outline style rather than expanding each paragraph.
I’ve just published a new SQLChallenge course, and I think it’s one of the best ones yet.
Your mission is to:
In the solution videos, I’ll step through multiple strategies to figure out which statement in the procedure is slowing it down the most – because in real life, you need to have a whole bag of tricks in different situations.
I naturally think about the new sys.dm_db_tuning_recommendations DMV when I’m working with the new Automatic Tuning feature in SQL Server 2017.
But I came across a post by Grant Fritchey recently, in which he remarked in the conclusion…
Trace Flag 4199 has been in SQL Server for a while. I’ve long thought of this as the “Bucket of Optimizer Hotfixes” trace flag: enabling it turns on a variety of hotfixes that have been implemented over the years.
I recently got a fantastic question from a reader regarding lock usage in SQL Server:
One of my production databases has a total lock count around 25,000 (select count(*) from sys.dm_tran_locks). The configuration setting for locks is set to the default of zero. This lock count is due to multiple procedures which frequently run and use the same 2-3 tables, repeatedly taking out and releasing locks. Do I need to change the configuration for locks or look into the SP’s so they can finish more quickly, rather than creating locks?
Every query tuner wants to explain exactly how much faster we made a query.
But sometimes SQL Server Management Studio adds noticeable overhead to the query duration. For relatively fast queries that return more than a few rows, just the overhead of displaying the results can skew your duration metric.
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.