j.miner (4/8/2013)
Maybe I'm misunderstanding param sniffing, but isn't the optimizer going to generate a plan and keep it until something changes (index rebuilt\reorg, stats update, table records added\deleted, etc...), and then generate a new plan based on the updated objects? Where the variable value never changes, param sniffing won't come into play here?
It's got nothing to do with having the wrong plan in cache, but everything to do with the estimate row counts.
When you use a literal, SQL Server will estimate the row count using statistics. When using a local variable, SQL Server has no choice but to do a thumb suck. The thumb suck is row count / 3 when there are more than 2 distinct values in the column and row count / 2 when there are 2 distinct values.
Consider this example:
I have a table with 100,000 rows. Let's say 90% of the rows contain the value 'Yes'. When using a literal the estimated number of rows will be 90,000. When using a variable the estimated number of rows is 33,333.3
The following code illustrates this:
CREATE TABLE #Test (ID INT, Value VARCHAR(3));
INSERT INTO #Test (ID)
SELECT TOP 100000
ROW_NUMBER() OVER (ORDER BY a.object_id)
FROM
sys.all_columns a
CROSS JOIN
sys.all_columns b;
UPDATE #Test SET Value = 'No' WHERE ID <= 5000;
UPDATE #Test SET Value = 'Mbe' WHERE ID <= 10000 AND ID > 5000;
UPDATE #Test SET Value = 'Yes' WHERE ID > 10000;
DECLARE @yes VARCHAR(3) = 'Yes';
SET STATISTICS XML ON;
SELECT * FROM #Test WHERE Value = 'Yes';
SELECT * FROM #Test WHERE Value = @yes;
SET STATISTICS XML OFF;
DROP TABLE #Test;
