SQL Server nie używa indeksu nieklastrowego

Question

Mam tabelę zawierającą około 470mln wierszy. Chciałbym wybrać dane na podstawie daty. Mam dwa indeksy utworzone w tej tabeli. Jedna jest zgrupowana, a druga nieklastrowana w kolumnie daty (data jest przechowywana jako INT). Mam proste polecenie wyboru:

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

Problem polega na tym, że plan kwerend używa skanowania indeksu klastrowego zamiast nieklastrowego wyszukiwania + keylookup. Zaplanuj w następujący sposób:

Szacunki generowane w planie zapytań są w porządku, statystyki są aktualne. Ten zakres dat powinien dostarczyć około 5 mln wierszy. Kiedy podaję wskazówkę dotyczącą indeksu, wybór trwa kilka sekund - bez podpowiedzi trwa kilka minut.

To jest SQL Server 2019 i mówiąc ogólnie, zauważyłem, że db preferuje skanowanie indeksów w klastrze niż używanie nieklastrowych + keylookupów nawet na większych tabelach.

Wolałbym nie używać podpowiedzi, ponieważ:

• czasami wybieram szersze zakresy, w których skanowanie indeksów klastrowych powinno być pożądane
• tabela jest używana w widoku i nie mogę podać wskazówki dotyczącej indeksu do widoku

Czy jest jakieś wyjaśnienie, dlaczego db nie używa indeksu NC w tym przypadku?

Linki do planów zapytań:

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

Answer 1

Wygląda na to, że SQL Server nie używa tego indeksu domyślnie, ponieważ:

• to jest filtrowany indeks, a
• Twoje zapytanie jest sparametryzowane

Możesz zobaczyć to ostrzeżenie w XML planu wykonania:

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

SQL Server nie wie, jakie są wartości parametrów (ponieważ znajdują się one w zmiennych), więc nie może bezpiecznie używać filtrowanego indeksu.

Jednym z rozwiązań jest użycie wskazówek dotyczących indeksu (jak wspomniałeś, nie jest to idealne rozwiązanie).

Innym sposobem obejścia tego problemu jest użycie dynamicznego SQL, zgodnie z opisem Jeremiah Peschka tutaj:

Filtrowane indeksy i dynamiczny SQL

Nie wiem, jak filtrowany indeks jest ... filtrowany. Możesz być w stanie uciec z osadzeniem literału tylko na jednej z dwóch wartości, aby ograniczyć rozdęcie pamięci podręcznej planu.

Answer 2

Czy jest jakieś wyjaśnienie, dlaczego db nie używa indeksu NC w tym przypadku?

Szacunkowy koszt tego planu jest niższy. Klastrowe skanowanie indeksu wykorzystuje więcej sekwencyjnych operacji we / wy, a nieklastrowe skanowanie indeksów + wyszukiwanie zakładek wykorzystuje więcej losowych operacji we / wy. Więc który z nich jest faktycznie szybszy, może zależeć od twojego sprzętu.

Spójrz na statystyki oczekiwania zapytania. W przypadku skanowania indeksu klastrowego jest to

        <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"/>

W przypadku indeksu nieklastrowego to

        <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"/>

Ale oba plany są bardzo drogie, więc powinieneś coś z tym zrobić. Opcje obejmują

1. Zastąpienie istniejącego indeksu klastrowego czymś bardziej użytecznym, na przykład dodanie daty do pierwszego indeksu, a następnie podzielenie indeksu klastrowego według daty.
2. Przechowywanie tej tabeli jako magazynu kolumn grupowanego zamiast indeksu grupowanego
3. Nie działa select *i dodaj wybrane uwzględnione kolumny do indeksu daty.