Один и тот же запрос - разные планы выполнения

ProgramBox

Один и тот же запрос — разные планы выполнения

Post author:admin
Запись опубликована:3 мая, 2023
Post category:Вопросы по программированию

#sql #sql-server #sql-server-2008 #query-optimization #sql-execution-plan

#sql #sql-сервер #sql-server-2008 #запрос-оптимизация #sql-execution-plan

Вопрос:

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
)

Я заполняю его 10 тысячами тестовых строк.

 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 #1amp;#xd;amp;#xa;amp;#xd;amp;#xa;select *amp;#xd;amp;#xa;from Saleamp;#xd;amp;#xa;where RowVersion amp;> 0x000000000001C310amp;#xd;amp;#xa;amp;#xd;amp;#xa;-- Query #2amp;#xd;amp;#xa;amp;#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]amp;>@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="amp;#xa;declare @LastVersion rowversion = 0x000000000001C310amp;#xd;amp;#xa;amp;#xd;"
                  StatementId="2"
                  StatementCompId="2"
                  StatementType="ASSIGN"/>
        <StmtSimple StatementText="amp;#xa;select *amp;#xd;amp;#xa;from Saleamp;#xd;amp;#xa;where RowVersion amp;> @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]amp;>[@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>

1. Актуальна ли ваша статистика?

2. Я запустил обновление статики — не помогло. Кроме того, я не уверен, что в этом случае статистика может быть устаревшей, поскольку таблица создается и заполняется с нуля, у нее не было возможности исказить статистику. В любом случае, даже если статистика была испорчена, почему первый запрос работает должным образом, а второй — нет.

3. Я думаю, что это проблема с отслеживанием параметров — просто чтобы подшутить надо мной, возможно, попробуйте сначала объявить @LastVersion (без значения по умолчанию), а затем установить желаемую переменную в следующей строке…

4. Я тоже пробовал это. Тоже не сработало. Я был бы удивлен, если бы это произошло :).

5. можете ли вы повторно запустить оба с включенным «set showplan_xml on»

Ответ №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

Ответ №2:

Попробуйте создать покрывающий индекс для фактических данных, которые вам нужно извлекать и избегать select * , в зависимости от данных в вашей таблице, это единственное надежное средство, которое заставит SQL Server не подсказывать и не возвращаться к сканированию.

Покрывающий индекс — это индекс, в котором фильтр поиска находится в том же порядке, и каждый выходной столбец включен в индекс.

Кроме того, поскольку мы имеем дело с параметризацией, стоит попробовать посмотреть, optimize for unknown оказывает ли это какое-либо влияние на план выполнения.

1. @Mitch Wheat — Изменил это, спасибо, что указал на это. Я сам помешан на аккуратности.

2. @Goran — Нет, не должно. На самом деле проблема операционной системы заключается в том, что он делает что-то очень похожее на OPTIMIZE FOR (UNKNOWN) .

3. Я также согласен с @Matrin в этом вопросе, следовательно, я немного взволнован, есть несколько вещей при работе с параметрами, которые заставляют план выполнения вести себя по-разному, и как только он будет кэширован, вам придется перекомпилировать его, чтобы увидеть эффект любых внесенных вами изменений (аннулировать кэш).

4. Да, охват индекса помог бы, но это не объяснило бы, почему планы выполнения отличаются для двух похожих запросов. Кроме того, охватывающий индекс не является хорошим вариантом в моем случае, поскольку в моей реальной таблице около 20 столбцов, и мне нужны все они в моем запросе. Таким образом, для хранения индекса покрытия потребуется даже больше места, чем для самой таблицы, а таблица огромная. Кроме того, запрос всегда предполагает получение очень небольшого подмножества строк (близкого к нулю).