Lets create a table of about 10000 records and name it as LargeTable.Here is the script for creating the same.
CREATE TABLE LargeTable (
ID INT,
SomeInt int,
RandomDate DATETIME
)
GO
;WITH DataPopulate (RowNo,Strng,ADate) AS (
SELECT 1 AS RowNo, 5 as Strng,DATEADD(dd, FLOOR(RAND()*75454),'1753/01/01')
UNION ALL
SELECT rowNo+1, CASE when rowno%10=0 then 5 else 89 end as Strng,DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),'1753/01/01')
FROM DataPopulate WHERE RowNo<10000
INSERT INTO LargeTable
SELECT * FROM DataPopulate
OPTION (MAXRECURSION 10000)
GO
CREATE CLUSTERED INDEX idx_Large1 on LargeTable (ID)
GO
Now let us create a SP to return no of records from the table.
CREATE PROCEDURE TestSniffing @int int AS
SELECT * from largetable where someint = @int
GO
Let us execute the SP :-
Exec TestSniffing 5
Look at the red part highlighted in the screen shot(Click on the image for better viewing).The Estimate Number of rows and the Actual Number of rows are matching.So far so good.
Now let us change the parameter value to 89 since 89 is another value which has a large number of records in the table.
Exec Testsniffing 89
Now refer to the highlighted red part of the following screenshot
The Estimated No of rows and the Actual No of rows differ by 8000.
Well why did this happen ? The answer is simple.Because of parameter sniffing which can cause a sub optimal query plan.
Now how should this be resolved ?Most of the solutions available tend to state to use pass a value of the parameter to a SP level variable and then pass that variable as a predicate to the actual query in the SP.Let us examine whether it solves the problem.
First let us alter the SP to fit our above requirement.
ALTER PROCEDURE TestSniffing @int int AS
Declare @varint int
Set @varint=@int
SELECT * from largetable where someint = @varint
GO
Now let us execute the SP with values 5 and 89 and examine the graphical execution plan
Exec TestSniffing 5
Again pay attention to the red part marked in the screen shot.The difference between the Estimated and the actual No of rows has decreased but is not exactly the same.
Let us try changing the parameter value to 89
Exec TestSniffing 89
Again there seem to be a huge difference between the actual and the estimated no of rows.
So what should be the solution?
Well one can execute the SP using With Recompile option.Something like this
Exec TestSniffing 89 With Recompile
But before doing that you need to alter the SP to its original state
ALTER PROCEDURE TestSniffing @int int AS
SELECT * from largetable where someint = @int
GO
The query plan generated will have a exact match for both the Estimated and the Actual No of rows.
Let us try for value of 5
Exec TestSniffing 5 With Recompile.
It gives a exact match between the estimated and the actual no of rows.
Adding the "With recompile" option seem to be the only way that to create a exact count between estimated and actual row count.Even updating the statistics did not seem to help in my example.
