November 23, 2011 at 2:13 pm
Hello again,
simple scenario for you gurus out there but first let's make sure I correctly understand some basic concepts.
1) When a local variable is passed in a TSQL statement, the optimizer cannot select the correct index because the variable value is not known at compilation time.
2) By turning the TSQL into a Stored Procedure, the optimizer "should" be able to "sniff" the value of the parameter and therefore compile the correct plan.
I have this index:
CREATE NONCLUSTERED INDEX [Invoice_NULL_provider_24] ON [Data].[transaction]
([INVOICE_NO] ASC, [provider_id] ASC)
WHERE ([invoice_no] IS NULL AND ([provider_id] IN ((2), (4))))
And I have this query:
declare @provider_id int
select min(start_dt) as Min_Start_Dt
from Data.[transaction]
where invoice_no is null
and provider_id = @provider_id
The plan for the above is a clustered index scan on [Data].[transaction]
However if I wrap that statement in a stored procedure I should see the parameter being sniffed and the plan use the index mentioned above:
Create procedure sniffing @provider_id int as
select min(start_dt) as Min_Start_Dt
from Data.[transaction]
where invoice_no is null
and provider_id = @provider_id
Execute the SP as follows:
Exec sniffing 1
Get a table scan again.
If I run the original query with a literal value:
declare @provider_id int
select min(start_dt) as Min_Start_Dt
from Data.[transaction]
where invoice_no is null
and provider_id = 2
I get a plan with the correct index.
One way I can get it to work if by having it recompiled after its execution by adding "option (recompile)"
as in:
Create procedure sniffing @provider_id int as
select min(start_dt) as Min_Start_Dt
from Data.[transaction]
where invoice_no is null
and provider_id = @provider_id
Option (recompile)
This gets me the same good plan with the index.
Please explain to me why the optimizer is not capable of sniffing the value of the parameter at compilation time and why it is not always behaving that way.
Thanks
Andrea
November 23, 2011 at 2:18 pm
Have you tried flipping the columns defined in your index? If you are looking for a specific provider, you should have that column first.
November 23, 2011 at 4:31 pm
Actually yes, even though the highest selectivity is on the other column. The provider only has 5 different values and it is filtered in the index to two. I believe the order of the column is correct but, as I said, it would not make a difference.
So the question remains...how is parameter sniffing really working (or not..)
November 23, 2011 at 11:03 pm
agiangone (11/23/2011)
Actually yes, even though the highest selectivity is on the other column. The provider only has 5 different values and it is filtered in the index to two. I believe the order of the column is correct but, as I said, it would not make a difference.So the question remains...how is parameter sniffing really working (or not..)
Hi agiangone,
Have you tried to see the execution plan with parameter values 2 & 4 i.e. @Provider_id = 2 or @Provider_id = 4 as you have created a filtered index ?
Does stored procedure's exec plan uses the index on those parameter values?
-Sujeet
November 24, 2011 at 5:58 am
Hi there,
yes I have. If you look at the original post there is an example but I used the wrong example posting value =1 in reality I used 2 and the index is picked up as expected. The issue is only when a variable is used instead.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply