November 11, 2015 at 6:09 am
Hi All
Not sure if I am missing something obvious here.......
Who do these 2 queries generate 2 different execution plans?
Query 1 generates an index seek with a lookup
Query 2 generates a clustered index scan.
This is against the AdventureWorks2012 database.
select SalesOrderID, OrderQty, ProductID
from Sales.SalesOrderDetail
where ProductID = 710
declare @var int
set @var = 710
select SalesOrderID, OrderQty, ProductID
from Sales.SalesOrderDetail
where ProductID = @var
Thanks
November 11, 2015 at 6:19 am
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2015 at 8:03 am
The variable is not able to be sampled by the optimizer, so it generates an estimate from the statistics instead of using a specific value. When you pass a specific value, it uses that value against the statistics. This results in different execution plans. Now, you may see a change if you were to tell that second query to recompile. In that case, it can use the value from the variable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply