Menu
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

select *
from Sale
where RowVersion > 0x000000000001C310

-- Query #2

"
StatementId="1"
StatementCompId="1"
StatementType="SELECT"
StatementSubTreeCost="0.00657038"
StatementEstRows="1"
StatementOptmLevel="FULL"
QueryHash="0xE442FF9A4A2A630A"
QueryPlanHash="0x347569CFDEF2A13F"
StatementOptmEarlyAbortReason="GoodEnoughPlanFound"
ParameterizedText="(@1 varbinary(8000))SELECT * FROM [Sale] WHERE [RowVersion]>@1">
<StatementSetOptions QUOTED_IDENTIFIER="true"
ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="true"
ANSI_NULLS="true"
ANSI_PADDING="true"
ANSI_WARNINGS="true"
NUMERIC_ROUNDABORT="false"/>
<QueryPlan CachedPlanSize="24"
CompileTime="1"
CompileCPU="1"
CompileMemory="136">
<RelOp NodeId="0"
PhysicalOp="Nested Loops"
LogicalOp="Inner Join"
EstimateRows="1"
EstimateIO="0"
EstimateCPU="4.18e-006"
AvgRowSize="28"
EstimatedTotalSubtreeCost="0.00657038"
Parallel="0"
EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="Test1"/>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</OutputList>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
</OuterReferences>
<RelOp NodeId="1"
PhysicalOp="Index Seek"
LogicalOp="Index Seek"
EstimateRows="1"
EstimateIO="0.003125"
EstimateCPU="0.0001581"
AvgRowSize="19"
EstimatedTotalSubtreeCost="0.0032831"
TableCardinality="10000"
Parallel="0"
EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</OutputList>
<IndexScan Ordered="1"
ScanDirection="FORWARD"
ForcedIndex="0"
ForceSeek="0"
NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Index="[UQ_Sale_RowVersion]"
IndexKind="NonClustered"/>
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="0x000000000001C310">
<Const ConstValue="0x000000000001C310"/>
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp NodeId="3"
PhysicalOp="Clustered Index Seek"
LogicalOp="Clustered Index Seek"
EstimateRows="1"
EstimateIO="0.003125"
EstimateCPU="0.0001581"
AvgRowSize="16"
EstimatedTotalSubtreeCost="0.0032831"
TableCardinality="10000"
Parallel="0"
EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="Test1"/>
</OutputList>
<IndexScan Lookup="1"
Ordered="1"
ScanDirection="FORWARD"
ForcedIndex="0"
ForceSeek="0"
NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="Test1"/>
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Index="[PK_Sale]"
TableReferenceId="-1"
IndexKind="Clustered"/>
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[AdventureWorks].[dbo].[Sale].[SaleId]">
<Identifier>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@1"
ParameterCompiledValue="0x000000000001C310"/>
</ParameterList>
</QueryPlan>
</StmtSimple>
<StmtSimple StatementText="
declare @LastVersion rowversion = 0x000000000001C310

"
StatementId="2"
StatementCompId="2"
StatementType="ASSIGN"/>
<StmtSimple StatementText="
select *
from Sale
where RowVersion > @LastVersion"
StatementId="3"
StatementCompId="3"
StatementType="SELECT"
StatementSubTreeCost="0.0328005"
StatementEstRows="3000"
StatementOptmLevel="FULL"
QueryHash="0xE442FF9A4A2A630A"
QueryPlanHash="0x0C6238F821406F2B"
StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
<StatementSetOptions QUOTED_IDENTIFIER="true"
ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="true"
ANSI_NULLS="true"
ANSI_PADDING="true"
ANSI_WARNINGS="true"
NUMERIC_ROUNDABORT="false"/>
<QueryPlan CachedPlanSize="16"
CompileTime="1"
CompileCPU="1"
CompileMemory="144">
<RelOp NodeId="0"
PhysicalOp="Clustered Index Scan"
LogicalOp="Clustered Index Scan"
EstimateRows="3000"
EstimateIO="0.0216435"
EstimateCPU="0.011157"
AvgRowSize="28"
EstimatedTotalSubtreeCost="0.0328005"
TableCardinality="10000"
Parallel="0"
EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="Test1"/>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</OutputList>
<IndexScan Ordered="0"
ForcedIndex="0"
NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="SaleId"/>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="Test1"/>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Index="[PK_Sale]"
IndexKind="Clustered"/>
<Predicate>
<ScalarOperator ScalarString="[AdventureWorks].[dbo].[Sale].[RowVersion]>[@LastVersion]">
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[AdventureWorks]"
Schema="[dbo]"
Table="[Sale]"
Column="RowVersion"/>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@LastVersion"/>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
264
The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan, or execution plan.
Right-click in a blank area of the execution plan and click Compare Showplan. Choose the second query plan file that you would like to compare with. The second file will open so that you can compare the plans. The compared plans will open a new window, by default with one on top and one on the bottom.
SQL server provides three types of execution plans i.e Estimated plan, Actual plan and Cached plan but today we will be discussing two basic plans. The moment you confirm the query to the SQL Server, the query optimizer will show a graphical execution plan that will display estimated execution plan time.
A query execution plan contains the following: The sequence of accessing each source table. The methods of extracting data from each table. The methods used to do calculations on data and to filter, aggregate, and sort data from each table.
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.
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
Try creating a covering index for the actual data that you need to retrieve and avoid select *, depending on the data in your table that's the only sure thing that will force SQL Server to not tip and fallback to a scan.
A covering index is an index in which the search filter is in the same order and each output column is included in the index.
Also, since we're dealing with parameterization, it's worth trying to see if optimize for unknown has any impact on the execution plan here.
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
