Почему план выполнения SQL Server зависит от порядка сравнения

Здесь есть ряд вопросов, но наиболее важным является оценка кардинальности (CE).

Более новая («по умолчанию») модель CE испытывает трудности с предикатами, когда пытается вычислить селективность по гистограмме без совпадающих шагов.

Например, начальная оценка кардинальности возвращает селективность, равную 1, для:

select *
from dbo.tblEvent e
where
    1 = e.IntegrationEventStateId
    or 2 = e.IntegrationEventStateId
    or 5 = e.IntegrationEventStateId
    or (4 = e.IntegrationEventStateId and e.ModifiedDateUtc >= dateadd(minute, -5, getutcdate()))

как показано с использованием флагов трассировки 3604 и 2363:

Begin selectivity computation

Input tree:

  LogOp_Select
      CStCollBaseTable(ID=1, CARD=356525 TBL: dbo.tblEvent AS TBL: e)
      ScaOp_Logical x_lopOr
          ScaOp_Comp x_cmpEq
              ScaOp_Identifier QCOL: [e].IntegrationEventStateId
              ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)
          ScaOp_Comp x_cmpEq
              ScaOp_Identifier QCOL: [e].IntegrationEventStateId
              ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=2)
          ScaOp_Comp x_cmpEq
              ScaOp_Identifier QCOL: [e].IntegrationEventStateId
              ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)
          ScaOp_Logical x_lopAnd
              ScaOp_Comp x_cmpGe
                  ScaOp_Identifier QCOL: [e].ModifiedDateUtc
                  ScaOp_Identifier COL: ConstExpr1001 
              ScaOp_Comp x_cmpEq
                  ScaOp_Identifier QCOL: [e].IntegrationEventStateId
                  ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4)

Plan for computation:

  CSelCalcCombineFilters_ExponentialBackoff (OR)
      CSelCalcCombineFilters_ExponentialBackoff (AND)
          CSelCalcColumnInInterval
              Column: QCOL: [e].ModifiedDateUtc
          CSelCalcColumnInInterval
              Column: QCOL: [e].IntegrationEventStateId
      CSelCalcColumnInInterval
          Column: QCOL: [e].IntegrationEventStateId

Loaded histogram for column QCOL: [e].ModifiedDateUtc from stats with id 3
Loaded histogram for column QCOL: [e].IntegrationEventStateId from stats with id 2

Selectivity: 1

Stats collection generated: 

  CStCollFilter(ID=2, CARD=356525)
      CStCollBaseTable(ID=1, CARD=356525 TBL: dbo.tblEvent AS TBL: e)

End selectivity computation

Когда начинается оптимизация на основе затрат и входное дерево имеет немного другую форму, CE просят вычислить селективность более простых предикатов:

Begin selectivity computation

Input tree:

  LogOp_Select
      CStCollBaseTable(ID=1, CARD=356525 TBL: dbo.tblEvent AS TBL: e)
      ScaOp_Logical x_lopOr
          ScaOp_Comp x_cmpEq
              ScaOp_Identifier QCOL: [e].IntegrationEventStateId
              ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)
          ScaOp_Comp x_cmpEq
              ScaOp_Identifier QCOL: [e].IntegrationEventStateId
              ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=2)
          ScaOp_Comp x_cmpEq
              ScaOp_Identifier QCOL: [e].IntegrationEventStateId
              ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)

Plan for computation:

  CSelCalcColumnInInterval
      Column: QCOL: [e].IntegrationEventStateId

Selectivity: 1

Stats collection generated: 

  CStCollFilter(ID=3, CARD=356525)
      CStCollBaseTable(ID=1, CARD=356525 TBL: dbo.tblEvent AS TBL: e)

End selectivity computation

Это эквивалент:

SELECT *
FROM dbo.tblEvent AS TE 
WHERE TE.IntegrationEventStateId IN (1, 2, 5);

В обоих случаях CE оценивает совпадение 100% строк, несмотря на отсутствие шагов гистограммы для значений 1, 2 или 5 (выборочные данные имеют только значения 3). Заманчиво обвинить в этом калькулятор CSelCalcColumnInInterval, поскольку он, кажется, обрабатывает {1, 2, 5} как один интервал {1:5}.

Как это часто бывает, «устаревший» CE выполняет здесь лучшую (более детальную) работу, поэтому вы должны обнаружить, что следующая подсказка создаст гораздо лучшие планы:

OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

С репродукционными данными это приводит к единому поиску и поиску ключа, как можно было бы надеяться.

Обратите внимание, что seek выполняет четыре операции поиска, по одной для каждого непересекающегося предиката.

[1] Seek Keys[1]: Prefix: IntegrationEventStateId = 1
[2] Seek Keys[1]: Prefix: IntegrationEventStateId = 2
[3] Seek Keys[1]: Prefix: IntegrationEventStateId = 4, Start: ModifiedDateUtc >= dateadd(minute,(-5),getutcdate())
[4] Seek Keys[1]: Prefix: IntegrationEventStateId = 5

Новый CE спроектирован так, чтобы быть более предсказуемым и простым в обслуживании/расширении, чем исходный CE. К «устаревшему» были прикручены детали и усовершенствования, которые вносились в течение длительного периода времени. Эта сложность имеет свои преимущества и недостатки. Регрессии и оценки более низкого качества в некоторой степени ожидаются с более новым CE. Это должно улучшиться со временем, но мы еще не достигли этого. Я бы рассматривал поведение, показанное здесь, как ограничение калькулятора. Возможно, они это исправят.

См. раздел Оптимизация планов запросов с помощью мощностей SQL Server 2014. Оценщик.

Вопрос о том, почему форма плана зависит от текстового представления, является скорее побочным вопросом. Процесс компиляции содержит логику (например, правило SelPredNorm) для перезаписи предикатов в нормализованную форму, и оба репро-запроса успешно перезаписываются в одно и то же дерево. Это делается для различных внутренних целей, включая сопоставление индексов и вычисляемых столбцов, а также для облегчения обработки логического упрощения.

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

Другими словами, планы различны, потому что в прошлом люди полагались на другой текст, производя разные планы, и изменение этого сейчас было бы слишком разрушительным.

Это интересно и необычно, и я не знаю, почему QO признает, что поиск индекса 4 раза будет дешевле, чем сканирование только в одном из этих запросов.

Иногда, когда вы получаете странные планы, лучшее решение — изменить индексы и запросы, чтобы легче было получить хорошие планы.

возможно что-то вроде

CREATE TABLE dbo.tblEvent
(
   EventId                 INT IDENTITY PRIMARY KEY,
   IntegrationEventStateId INT,
   ModifiedDateUtc         DATETIME,
   OtherCol                CHAR(1),
   index IX_tblEvent_IntegrationEventStateId_ModifiedDateUtc(IntegrationEventStateId, ModifiedDateUtc) 
     include (OtherCol) 
     where IntegrationEventStateId in (1,2,4,5)
);

а потом

select *
from dbo.tblEvent e 
where
    e.IntegrationEventStateId in (1,2,4,5)
    and (e.IntegrationEventStateId <> 4 or e.ModifiedDateUtc >= dateadd(minute, -5, getutcdate()))