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:
|
1 2 3 4 |
SELECT something FROM stuff WHERE (@thing1 is NULL or whatever = @thing1) AND ... |
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.
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?
You may blame the NULLs, but it’s not their fault.
What about with a RECOMPILE hint? Someone on the internet told me that if I use RECOMPILE I’ll get an optimal plan.
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).
Big Deal?
If we add an index with the correct definition, all of those queries will use it as written.
|
1 2 |
CREATE INDEX ix_helper ON dbo.Users (DisplayName, Reputation); GO |
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.
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?
Wait — what are the benefits of optional parameters?
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.
“teh standard” sounds like a good description of this 😉
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?
Sure, start here and here. It’s a common problem that’s been blogged about a lot.
Those are some good articles. We definitely have a bunch of those kinds of queries to clean up. Thanks for the references.
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
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!
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?
Typically, dynamic SQL.