Same query - different execution plans

SQL 2008.
I have a test table:

create table Sale
(
    SaleId int identity(1, 1)
        constraint PK_Sale primary key,
    Test1 varchar(10) null,
    RowVersion rowversion not null
        constraint UQ_Sale_RowVersion unique
)

I populate it with 10k test rows.

declare @RowCount int = 10000
while(@RowCount > 0)
begin
    insert Sale default values
    set @RowCount -= 1
end

I run these two queries:

-- Query #1

select *
from Sale
where RowVersion > 0x000000000001C310

-- Query #2

declare @LastVersion rowversion = 0x000000000001C310

select *
from Sale
where RowVersion > @LastVersion

I can't figure out why these two queries have different execution plan.
Query #1 does index seek against UQ_Sale_RowVersion index.
Query #2 does index scan against PK_Sale.

I want query #2 to do index seek.
I would appreciate some help.
Thank you.

[Edit]

Tried using datetime2 instead of rowversion. The same issue.

I tried to force using index too (query #3)

select *
from Sale with (index = IX_Sale_RowVersion)
where RowVersion > @LastVersion

This seemed to show the same query execution plan as the query #1, but execution plan showed this query #3 as the most expensive among all those 3 queries.

[Edit] Execution plan:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
              Version="1.1"
              Build="10.50.1600.1">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="-- Query #1&#xd;&#xa;&#xd;&#xa;select *&#xd;&#xa;from Sale&#xd;&#xa;where RowVersion &gt; 0x000000000001C310&#xd;&#xa;&#xd;&#xa;-- Query #2&#xd;&#xa;&#xd;"
                    StatementId="1"


                    
                    
                        
                        sql
                        
                        sql-server
                        
                        sql-server-2008
                        
                        query-optimization
                        
                        sql-execution-plan
                        
                    
                    
                        
                        
                        
                        
                        
                        Edit
                        
                        
                        
                            
                        
                        
                        Report

Query 2 uses a variable.

At the time the batch is compiled SQL Server does not know the value of the variable so just falls back to heuristics very similar to OPTIMIZE FOR (UNKNOWN)

For > it will assume that 30% of the rows will end up matching (or 3000 rows in your example data). This can be seen in the execution plan image as below. This is significantly over and above the 12 rows (0.12%) which is the tipping point for this query in whether it uses a clustered index scan or a non clustered index seek and key lookups.

You would need to use OPTION (RECOMPILE) to get it to take account of the actual variable value as shown in the third plan below.

Execution Plans Image

Script

CREATE TABLE #Sale
(
    SaleId INT IDENTITY(1, 1)
        CONSTRAINT PK_Sale PRIMARY KEY,
    Test1 VARCHAR(10) NULL,
    RowVersion rowversion NOT NULL
        CONSTRAINT UQ_Sale_RowVersion UNIQUE
)

/*A better way of populating the table!*/
INSERT INTO #Sale (Test1)
SELECT TOP 10000 NULL 
FROM master..spt_values v1, master..spt_values v2

GO

SELECT *
FROM #Sale
WHERE RowVersion > 0x000000000001C310-- Query #1


DECLARE @LastVersion rowversion = 0x000000000001C310

SELECT *
FROM #Sale
WHERE RowVersion > @LastVersion-- Query #2


SELECT *
FROM #Sale
WHERE RowVersion > @LastVersion
OPTION (RECOMPILE)-- Query #3

DROP TABLE #Sale

answered 2011-05-06T09:00:24.507

Same query - different execution plans

1 Answer

Script

Your Answer