Usual suspects:
- constants in adhoc, parameters in code
- mismatch of data types in code
- parameter sniffing
Point 1: the optimiser can choose the best plan for the constants.
Change the constants = change the plan. A parametrised plen is resuable
Point 2 will introduce implicit conversions because of datatype precedence
eg varchar column compared to nvarchar parameter
Point 3: use parameter masking or OPTIMISE FOR UNKNOWN
Edit: To test: run stored proc, run sp_updatestats, run again. This will invalidate cached plans which is better then clearing plan cache
Edit: after jcolebrand's comment
You can disable sniffing several ways. The main 3 are
- RECOMPILE. This is silly IMO.
- OPTIMIZE (sic) FOR UNKNOWN
- Parameter masking
Parameter masking:
DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam
SELECT...WHERE column = @MaskedParam
Masking and the OPTIMIZE hint have the same effect (maybe for different reasons). That is, the optimiser has to use statistics and data distribution (Note: still under testing by Mark Storey-Smith) evaluate the parameters on their own merits ? , rather than what they were last call. The optimiser can recompile or not. SQL Server 2005 added statement level recompilation so there was less impact
Now, why a plan with "sniffed" parameters is "sticky" compared to masked/"unknown" parameters, I'm not sure.
I've used parameter masking since SQL Server 2000 for all but the simplest code. I've noted that it is liable to happen with more complex code. And at my old job I has some report procs that I could change the plan parameter defaults. I reckon the "cargo cult" approach was easier than a support call.
Edit 2, 12 Oct 2011, after some chat
Parameter masking and OPTIMISE FOR UNKNOWN have the same effect as far as I can tell
The hint is cleaner than masking but was added with SQL Server 2008.
Parameter sniffing happens at compile time.
WITH RECOMPILE generates a new plan each execution. This means a poor choice of defaults will influence the plan. At my last job, I could demonstrate this easily with some report code: changing parameter defaults altered the plan regardless of supplied parameters.
This MS Connect article is interesting: Suboptimal index usage within stored procedure (mentioned in one of the SO answers below)
- Bob Beauchemin mentions it too
Outstanding issues
Does sniffing still apply with WITH RECOMPILE? That is, if the optimiser knows to discard the plan does it aim for re-use?
Why are sniffed plans "sticky"?
Links from SO:
I'm going to skip past your questions and try to offer broader guidelines/advice instead.
The definitive/canonical guide to dynamic SQL, the situations where it is applicable and where it can be avoided, is Erland Sommarskog's Dynamic Search Conditions in T-SQL. Read it, re-read, run through Erland's examples, make sure you understand the reasoning behind the recommendations.
You're dealing with a fairly common scenario and the approach you've taken is not unusual. A couple of points worth highlighting:
- Using temporary tables is probably unnecessary. Is there a reason they were introduced?
- You have probably over-indexed the table. Read Kimberly Tripp's "just because you can, doesn't mean you should" article on the topic.
- Because you've over-indexed on individual columns, you're probably lacking good covering indexes. With so many aggregations and such a wide range of search conditions, these will be a challenge to get right.
Now the most important part of getting these kinds of searches right... apply the 80/20 rule.
The majority of calls to your procedure are likely to comprise a relatively small number of the possible variations of parameters. You cannot create optimal indexes for all combinations of 15 parameters, so identify the most common patterns, create static stored procedures for these and index for them appropriately. Deal with the remaining combinations with dynamic SQL, following Erland's best practices.
In these scenarios, you will often find the usage patterns closer to 95/5 than 80/20 so the additional work of creating static procedures is not as labour intensive as it seems at first glance.
Best Answer
It is not possible to set
OPTIMIZE FOR UNKNOWN
at the stored procedure level in SQL Server. You could useWITH RECOMPILE
, which I don't recommend using unless you are on SQL Server 2005 or lower, but it doesn't achieve the same thing. In my opinion,OPTIMIZE FOR UNKNOWN
should be avoided. Spend the time to figure out why you are experiencing parameter sniffing. There could be a way to fix it without working around it. I preferOPTIMIZE FOR @variable_name
but have also had to resort toRECOMPILE
at the query level.This article may be helpful.