While recently conducting a performance analysis for a client I, again, came across an interesting phenomenon relating to plan caching and the potentially sordid effects of parameter sniffing on performance.
Parameter sniffing allows SQL Server to create an execution plan for reuse by analyzing the parameter values passed during the first execution of a SPROC. The ‘first execution’, that is the key to this phenomena, results in a cached execution plan based on the statistical distribution of the values of the parameters passed. Specifically, the selectivity of the parameter values included during the first execution may be dissimilar to the selectivity of parameters passed in subsequent calls resulting in poor performance of the SPROC. If you’ve determined that a SPROC’s execution plan is being polluted by parameter sniffing, then you have a couple options: use the WITH RECOMPILE option, or assign the parameters passed to the SPROC to local variables within the SPROC thereby blinding the parameter sniffing process.
Discussion of the methods to examine the plans in the cache as well as their performance implications are topics for a paper not this medium so I leave you with this oft repeated pearl of wisdom: “no plan is better than a bad plan”.