相同的查询 - 不同的执行计划

Question

SQL 2008。
我有一个测试表：

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 是所有这 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&#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"
                    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]&gt;@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="&#xa;declare @LastVersion rowversion = 0x000000000001C310&#xd;&#xa;&#xd;"
                  StatementId="2"
                  StatementCompId="2"
                  StatementType="ASSIGN"/>
        <StmtSimple StatementText="&#xa;select *&#xd;&#xa;from Sale&#xd;&#xa;where RowVersion &gt; @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]&gt;[@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>

Answer 1

查询 2 使用变量。

在编译批处理时，SQL Server 不知道变量的值，因此只能退回到与 OPTIMIZE FOR (UNKNOWN) 非常相似的启发式方法，

对于 > 它将假设 30% 的行最终会匹配（或示例数据中的 3000 行）。这可以在如下的执行计划图中看到。这明显超出了 12 行 (0.12%)，即 此查询的临界点是使用聚集索引扫描还是非聚集索引查找和键查找。

您需要使用 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

Answer 2

尝试为您需要检索的实际数据创建覆盖索引并避免 select *，具体取决于表中的数据，这是强制 SQL Server 不提示的唯一确定的事情< /em> 并回退到扫描。

覆盖索引是指搜索过滤器的顺序相同且每个输出列都包含在索引中的索引。

另外，由于我们正在处理参数化，因此值得尝试看看针对未知进行优化是否对这里的执行计划有任何影响。