SQL2008。 我有一个测试表:
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 )
我用10k测试行填充它。
declare @RowCount int = 10000 while(@RowCount > 0) begin insert Sale default values set @RowCount -= 1 end
我运行以下两个查询:
-- Query #1 select * from Sale where RowVersion > 0x000000000001C310 -- Query #2 declare @LastVersion rowversion = 0x000000000001C310 select * from Sale where RowVersion > @LastVersion
我不知道为什么这两个查询有不同的执行计划。 查询1确实针对UQ_Sale_RowVersion索引进行索引搜索。 查询2对PK_Sale进行索引扫描。
我想查询2做索引查找。 我将不胜感激。 谢谢你。
[编辑]
尝试使用datetime2而不是rowversion。同样的问题。
我也尝试强制使用索引(查询#3)
select * from Sale with (index = IX_Sale_RowVersion) where RowVersion > @LastVersion
这似乎显示了与查询#1相同的查询执行计划,但是执行计划显示此查询#3是所有这三个查询中最昂贵的查询。
[编辑] 执行计划:
<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>
查询2使用变量。
在编译批处理时,SQL Server不知道变量的值,因此回退到非常类似于的启发式方法。 OPTIMIZE FOR (UNKNOWN)
OPTIMIZE FOR (UNKNOWN)
因为>它将假设30%的行最终匹配(或示例数据中的3000行)。可以在执行计划图像中看到,如下所示。这大大超过了12行(0.12%),这是此查询使用聚簇索引扫描还是非聚簇索引查找和键查找的引爆点。
>
您需要使用OPTION (RECOMPILE)它来考虑实际的变量值,如下面的第三个计划所示。
OPTION (RECOMPILE)
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