3/12/2012 8:55:04 AM
Amit Bansal -
Indexing is a wonderful thing. We all know about Index scans and seeks. Today I want to talk about dynamic index seek operation. I find it tough to explain in words so I will straight away use an example without further ado !
Suppose you run the following query (turn on execution plan or press Ctrl + M)
WHERE SalesPersonID IN(268, 275)
You will observe that you get a seek plan, obviously because there is a non-clustered index on SalesPersonID column.
The IN list above is logically equivalent to using an OR predicate with the above two values; thus performing two separate seek operations.
Things are simple because we are using constants. Now, let us use variables instead of constants:
DECLARE @spid1 INT, @spid2 INT
SELECT @spid1 = 268, @spid2 = 275
WHERE SalesPersonID IN(@spid1, @spid2)
And this time you get a pretty complicated plan. Here is the screen shot: (I have just omitted out the SELECT operator)
Now the question is, why do we get such a complicated plan for simple seeks. Short answer: The optimizer is very intelligent :)
The optimizer does not have the values of variables available until runtime. So it does not know if the values will be same for both the variables or will it be different. What do I mean? What I mean is: in the previous case, as I explained, there are 2 seeks that happen and since the optimizer has the values, and if the values are same, it can automatically filter out the duplicates and will not return 2 rows of the same record. In case of the latter, if the variables have the different values, the original seek plan is valid, but what if the values are same? In such a case, since the variable values are not known to the optimizer and it seeks twice, it can return the same record twice if it follows the original plan. That’s why we get such a complicated plan and these plans are an example of dynamic index seek operations.
You must be wondering, why MERGE interval in the plan? Why not SORT DISTINCT? Keep yourself tuned in, that’s coming in the next post.
If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks
Have a SQL Server question? Join the fastest growing SQL Server facebook group at: http://www.facebook.com/groups/458103987564477/
Regards, Amit Bansal
http://www.twitter.com/A_Bansal http://www.twitter.com/SQLServerGeeks http://www.amitbansal.net/ Visit my FaceBook page at http://www.facebook.com/AmitRSBansal Contribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us
Amit Bansal (Member since: 3/12/2011 4:59:54 PM)
Follow Amit at Twitter @A_Bansal : Amit Bansal is the CTO of eDominer Systems & Peopleware India. He is a consultant, trainer, writer, speaker & evangelist on SQL Server & Business Intelligence. A seasoned speaker; he speaks at major summits and conferences of Microsoft worldwide including TechED, MCT Summits, MSDN & TechNet conferences. Over the last 8 years, he has consulted, trained & mentored more than 6000 IT professionals on SQL Server & Business Intelligence and worked with top notch blue chip companies worldwide including Microsoft, Infosys, Wipro, RBS, HCL, HP, Siemens, IBM, Accenture, etc. He has delivered more than 400 workshops on SQL Server & Business Intelligence. Apart from holding many Microsoft credentials, he is also a Microsoft Most Valuable Professional (MVP) awardee on SQL Server. He is also one of the first Microsoft Certified Trainer in India. Amit has worked with Microsoft in India and US as a Subject Matter Expert in various capacities participating in OD sessions, technical reviewing, etc. He is also a very active community lead in Asia Pacific. Visit www.amitbansal.net to know more. Subscribe to Amit’s blog and be a member of his technical forum at www.SQLServerGeeks.com - an exclusive SQL Server portal. Visit www.peoplewareindia.com for Corporate Training solutions.
FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346
LinkedIn – http://www.linkedin.com/pub/amit-bansal/7/121/755
View Amit Bansal 's profile
Leave a comment