Optional Parameters and Missing Index Requests

That’s when it all gets blown away

At one point or another in everyone’s SQL-querying career, they end up writing a query that goes something like this:

These are often called optional parameters, and if you spend any time looking at queries, this will make you shudder for many reasons. Poor cardinality estimates, full scans, etc.

One thing that often gets overlooked is that queries constructed like this don’t register missing index requests.

As usual, using the Stack Overflow database for a demo.

Askance

With literal values, the optimizer goes into index matching mode, finds nothing helpful, and tells you all about it. The missing index request is pretty predictable, on DisplayName and Reputation. Makes sense so far, right?

What about with NULL variables?

Kiss your missing index request goodbye

You may blame the NULLs, but it’s not their fault.

Milli VaNULLi

What about with a RECOMPILE hint? Someone on the internet told me that if I use RECOMPILE I’ll get an optimal plan.

Like Crest on plaque

Someone on the internet was, well, not wrong, but not right either. By most standards, you’ll get an optimal plan. But still no missing index request.

Using a stored procedure doesn’t help with that, either (unless you recompile, but that may not be ideal for other reasons).

Swamp of Sadness

Big Deal?

If we add an index with the correct definition, all of those queries will use it as written.

That’s not exactly the problem. Nor is the missing index request being missing a direct affront to anyone who has been tuning queries for more than 30 seconds. It’s obvious what a good enough index would be for this query.

What could be very misleading is if you’re using the DMVs to round up missing index requests, you’re unfamiliar with the overall schema and current index design, or if the optional parameter searches are part of a larger query where the index usage patterns being sub optimal aren’t apparent.

The bottom line on this type of search is that it’s not SARGable. Like using functions and other icky-messies across joins and where clauses, it will prevent missing index requests from popping up. And while missing index requests aren’t perfect, they are a valuable workload analysis tool, especially to beginners.

Thanks for reading!

Brent says: This is such a good example of why you need at least 3 tuning passes for performance tuning: run sp_BlitzIndex looking for obvious index improvements, then run sp_BlitzCache to tune the queries that are still slow, then after tuning them, run sp_BlitzIndex one more time to catch the new missing index requests.

, , ,
Previous Post
Indexing for Windowing Functions: WHERE vs. OVER
Next Post
How Much Can One Row Change A Query Plan? Part 2

11 Comments. Leave new

  • @Erik I’ve known for a while that this would cause some kind of plan weirdness, but I wasn’t sure what and I couldn’t come up with a better way, especially for SSRS reports with optional params. Can you recommend a way to get the same benefits from optional parameters without breaking the index recommendations?

    Reply
    • Wait — what are the benefits of optional parameters?

      Reply
      • less work in the code that creates your SQL statement. Right now I suggest to my developers to have teh standard where 1=1 clause and then add ” and column1=@parm1″ for every filter that was selected on the UI. The optional parameter makes the code shorter and easier to understand.

        Reply
      • Well, there are something like 143 reasons to use optional parameters if you have 12 params on a sp and they are all optional. Something that happens pretty frequently in SSRS reports backed by a sp. Without optionals, i’d have to copy the sp and report 143 times to have the same effect so that each potential set of params can be ‘perfectly’ optimized. So, my question again is: is there a better way to write a sp where the practical requirements demand optional params?

        Reply
  • Even with both an index and option(recompile), statements that assign to a variable will not make use of parameter embedding. So, queries like:
    select @cnt = count(1) from Stuff where (@thing1 is NULL or whatever = @thing1) option(recompile);
    won’t use the proper index.

    As an aside, Erland Sommarskog has an excellent guide to dynamic search conditions at http://www.sommarskog.se/dyn-search.html

    Reply
    • Yeah — and beyond that, what stinks is people often use those results to supply predicates for another query.

      So not only do they miss the parameter embedding, but then they get the ‘local variable’ effect on the query they use it in.

      I’ve had better luck sticking values I want to reuse in a temp table.

      Thanks!

      Reply
  • What is the fix here? Two have 4 different sprocs for each scenario and call the appropriate one depeneding on the nullness of the parameters?

    Reply

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.

Menu
{"cart_token":"","hash":"","cart_data":""}