Tuning Dynamic SQL by Hand with Short Circuits


When we think about building dynamic SQL, we usually think about a stored procedure like this that takes input parameters, builds a string, and then executes that string. Here’s a simple example:

The more parameters you have, the crazier the code gets, and the harder it becomes to produce really fast plans across all the parameter combinations.

But what if a lot of the queries look similar?

For example, what if your search page has a set of defaults, and the vast, vast majority of searches just use the defaults?

In our example above, what if the vast majority of the time, people always searched for users in London whose Reputation = 1? There’s no reason we can’t put a short-circuit at the top of our stored procedure:

In complex stored procedures, this short circuit buys me a few cool advantages:

You can build a hand-crafted query. You can restructure the T-SQL to be more performant, add index hints, query hints, trace flags, you name it, things you wouldn’t normally want to add to dynamic SQL on the fly.

You can get the data from somewhere different. For example, at one client, 99% of product searches were looking for the “Item of the Day” on sale – and that product list only changed once per day. We simply built a new table with the necessary product info, populated it once per day on a schedule, and changed the search procedure to something like this (obviously with a different table name):

For that client, it made a phenomenal difference in eliminating joins, memory grants, permissions checks, and sorts. (The table only had one row in it.)

You skip the overhead of building a dynamic string. I know it sounds trivial, but when you’re dealing with tens of thousands of queries per second, this can be a big deal.

How to know if this tactic makes sense

Warning: run-on sentence incoming.

  1. If you run sp_BlitzCache and your top most resource-intensive queries use dynamic SQL,
  2. And a little further down in the results, you see a dynamic string that was built by one of these procs,
  3. And when you compare the outer proc’s resource utilization numbers (CPU, reads, duration, etc) number to the dynamic SQL’s resource utilization, and it looks like that one dynamic SQL line is a big chunk,
  4. And you think you could do better tuning that particular branch of dynamic SQL by hand,
  5. And the other branches wouldn’t benefit from you just tuning the main string itself,
  6. Then it’s a good fit.
Previous Post
New Fundamentals of Index Tuning Course
Next Post
Watch Brent Tune Queries: Fixing Nested Functions

7 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.