SQL Server Index Internals Part 1 –Dynamic Index seek operation

Hi Friends,

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)

USE AdventureWorks
GO

SELECT SalesOrderId
FROM sales.SalesOrderHeader
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
SELECT SalesOrderId
FROM sales.SalesOrderHeader
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 :)

Long answer:

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