Dynamic Index Seek Review
By : Kasim Wirama, MCDBA
Other execution plan pattern that is noticeable is dynamic index seek. A plan is called dynamic index seek when the optimizer doesn’t know variable values at compile time, and the plan generates index seek operator and merge interval operator. Merge interval operator? It sounds like uncommon operator. I would like to describe more about the operator with following query example.
See the execution plan for this query at Northwind database :
SELECT ordered
FROM orders
WHERE shippostalcode IN ('02389-673','01307')
Its execution plan is :
SELECT orderid FROM orders WHERE shippostalcode IN ('02389-673','01307')
|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[ShipPostalCode]), SEEK:([Northwind].[dbo].[Orders].[ShipPostalCode]=N'01307' OR [Northwind].[dbo].[Orders].[ShipPostalCode]=N'02389-673') ORDERED FORWARD)
Because the index is highly selective, optimizer choose index seek. Now compare execution on the similar query but parameterized one below :
DECLARE @var1 nvarchar(20), @var2 nvarchar(20);
SELECT @var1='02389-673', @var2='01307';
SELECT ordered
FROM orders
WHERE shippostalcode IN (@var1,@var2);
The execution plan is :
SELECT orderid FROM orders WHERE shippostalcode IN (@var1,@var2);
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1009], [Expr1010], [Expr1011]))
|--Merge Interval
| |--Sort(TOP 2, ORDER BY:([Expr1012] DESC, [Expr1013] ASC, [Expr1009] ASC, [Expr1014] DESC))
| |--Compute Scalar(DEFINE:([Expr1012]=((4)&[Expr1011]) = (4) AND NULL = [Expr1009], [Expr1013]=(4)&[Expr1011], [Expr1014]=(16)&[Expr1011]))
| |--Concatenation
| |--Compute Scalar(DEFINE:([@var2]=[@var2], [@var2]=[@var2], [Expr1003]=(62)))
| | |--Constant Scan
| |--Compute Scalar(DEFINE:([@var1]=[@var1], [@var1]=[@var1], [Expr1006]=(62)))
| |--Constant Scan
|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[ShipPostalCode]), SEEK:([Northwind].[dbo].[Orders].[ShipPostalCode] > [Expr1009] AND [Northwind].[dbo].[Orders].[ShipPostalCode] < [Expr1010]) ORDERED FORWARD)
The second execution plan is quite different with the first one. Recall the first passage why optimizer generates complex execution plan. Merge interval is to remove duplication between 2 values, but actually optimizer sees the 2 values as 2 ranges. Overlapping is possible between 2 ranges, and that’s the task of Merge Interval operator to make sure no duplication after collapsing 2 ranges. But the question probably arises from your thought, which is wondering why SORT operator doesn’t handle such a duplication. In dynamic index seek, sort operator which is executed right before merge interval just make sure a value is adjacent to its neighbor.
Second execution plan above is similar to execution plan of the following range query:
DECLARE @orderdate1 datetime, @orderdate2 datetime;
SELECT @orderdate1 = '19970101', @orderdate2 = '19970104';
SELECT ordered
FROM orders
WHERE orderdate BETWEEN @orderdate1 AND dateadd(DAY, 7, @orderdate1)
OR orderdate BETWEEN @orderdate2 AND dateadd(DAY,7,@orderdate2);
Merge interval operator is clearly shown at the above range query.