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

  • > 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.

    It’s not the string building that can be sped up. It’s advisable not to use dynamic t-sql in `usp_SearchUsers_WelcomeToLondon` at all so that it skips recompilation.

    Skipping recompilation is especially useful when short-circuit query is expected to return 1 row. For instance if @SearchDisplayName contains no wildcard characters it’s an exact match single-row filtering. On large tables recompilation of dynamic t-sql might take hundreds of ms and then the execution of this single-row query takes less than 1ms.

    So skipping recompilation for single-row queries by not using dynamic t-sql is what we do here all the time.

    • Absolutely agree! That’s what I meant by “skip the overhead of building a dynamic string” – if you’re not doing dynamic SQL in that proc, there’s no string-building overhead in it.

  • Emanuele Meazzo
    January 23, 2019 3:09 pm

    Some time ago I built an inceptional (c) procedure to track the duration of my dynamic SQL Statements, nothing I would use on heavy load OLTP Systems, but it can help with debugging/tracking dynamic code


  • There’s always a trade-off. You may need to update both of those stored procedures when fixing bugs, changing logic, etc… In other words, it may be easy to fix a bug in the main stored procedure and forget to check/fix the specific stored procedure.

  • Great post!
    What about spezifying the where clause to

    (DisplayName = @SearchDisplayName OR @SearchDisplayName IS NULL)
    AND (Location = @SearchLocation or @SearchLocation IS null)
    AND (Reputation = @SearchReputation or @SearchReputation IS NULL)

    Wouldn’t this be any easier solution?