SQL Server не использует некластеризованный индекс

Question

У меня есть таблица, которая содержит около 470 миллионов строк. Я хотел бы выбрать данные по дате. У меня есть два индекса, созданных на этой таблице. Один из них кластеризован, а другой не кластеризован по столбцу даты (дата сохраняется как INT). У меня есть простой оператор выбора:

select *
from big_table
where [date] BETWEEN 20200820 AND 20200828

Проблема в том, что план запроса использует сканирование кластерного индекса вместо некластеризованного поиска + поиска по ключу. Планируйте следующее:

Оценки, созданные в плане запроса, в порядке, статистика актуальна. В этом диапазоне дат должно быть отображено около 5 млн строк. Когда я даю подсказку по индексу, этот выбор завершается за пару секунд - без подсказки на завершение уходит пара минут.

Это SQL Server 2019, и, вообще говоря, я заметил, что db предпочитает сканирование кластерного индекса, которое использует некластеризованные + поисковые запросы даже для больших таблиц.

Я бы предпочел не использовать подсказку, потому что:

• иногда я выбираю более широкие диапазоны, в которых желательно сканирование кластерного индекса
• таблица используется в представлении, и я не могу предоставить указание индекса для представления

Есть ли какое-нибудь объяснение, почему db не использует индекс NC в этом случае?

Ссылки на планы запросов:

• https://www.brentozar.com/pastetheplan/?id=SkaQFqLmD
• https://www.brentozar.com/pastetheplan/?id=Hkh7q587P

Answer 1

Похоже, что SQL Server не использует этот индекс по умолчанию, потому что:

• это отфильтрованный индекс, и
• ваш запрос параметризован

Вы можете увидеть это предупреждение в XML-плане плана выполнения:

<UnmatchedIndexes>
  <Parameterization>
    <Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" />
  </Parameterization>
</UnmatchedIndexes>
<Warnings UnmatchedIndexes="1" />

SQL Server не знает, каковы значения параметров (поскольку они находятся в переменных), поэтому он не может безопасно использовать отфильтрованный индекс.

Одно из решений - использовать подсказки индекса (как вы упомянули, это не идеально).

Другой способ обойти это - использовать динамический SQL, как описано здесь Джереми Пешкой:

Отфильтрованные индексы и динамический SQL

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

Answer 2

Есть ли какое-нибудь объяснение, почему db не использует индекс NC в этом случае?

Ориентировочная стоимость этого плана ниже. Сканирование кластерного индекса использует больше последовательных операций ввода-вывода, а сканирование некластеризованного индекса + поиск по закладкам использует больше случайных операций ввода-вывода. Итак, какой из них на самом деле быстрее, может зависеть от вашего оборудования.

Посмотрите статистику ожидания запроса. Для сканирования кластерного индекса это

        <WaitStats>
          <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="3188040" WaitCount="31753"/>
          <Wait WaitType="CXPACKET" WaitTimeMs="566095" WaitCount="6329619"/>
          <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="21354" WaitCount="29774"/>
          <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="11994" WaitCount="8679127"/>
          <Wait WaitType="SLEEP_BPOOL_STEAL" WaitTimeMs="7435" WaitCount="439"/>
          <Wait WaitType="LATCH_EX" WaitTimeMs="206" WaitCount="35"/>
          <Wait WaitType="SESSION_WAIT_STATS_CHILDREN" WaitTimeMs="8" WaitCount="6"/>
          <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="5" WaitCount="2"/>
        </WaitStats>
        <QueryTimeStats ElapsedTime="247180" CpuTime="232769"/>

Для некластеризованного индекса это

        <WaitStats>
          <Wait WaitType="CXPACKET" WaitTimeMs="451425" WaitCount="4834017"/>
          <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="43202" WaitCount="41863"/>
          <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="11453" WaitCount="11288"/>
          <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="2823" WaitCount="4051831"/>
          <Wait WaitType="LCK_M_S" WaitTimeMs="1366" WaitCount="1"/>
          <Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="152" WaitCount="37550"/>
          <Wait WaitType="PAGEIOLATCH_UP" WaitTimeMs="49" WaitCount="4"/>
          <Wait WaitType="LATCH_EX" WaitTimeMs="11" WaitCount="14"/>
          <Wait WaitType="LATCH_SH" WaitTimeMs="1" WaitCount="3"/>
        </WaitStats>
        <QueryTimeStats ElapsedTime="67529" CpuTime="119828"/>

Но оба плана очень дороги, так что вам нужно что-то с этим делать. Варианты включают

1. Замена существующего кластерного индекса чем-то более полезным, например добавлением Date к первому индексу, а затем секционированием кластерного индекса по дате.
2. Сохранение этой таблицы как кластерного хранилища столбцов вместо кластерного индекса
3. Не работает select *и добавить выбранные включенные столбцы в индекс даты.