SQL Server tidak menggunakan indeks nonclustered

Question

Saya memiliki tabel yang berisi sekitar 470mln baris. Saya ingin memilih data berdasarkan tanggal. Saya memiliki dua indeks yang dibuat di tabel ini. Satu dikelompokkan satu dan lainnya tidak berkerumun pada kolom tanggal (tanggal disimpan sebagai INT). Saya memiliki pernyataan pilih sederhana:

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

Masalahnya adalah bahwa paket kueri menggunakan pemindaian indeks berkerumun, bukan pencarian kunci + nonclustered. Rencanakan sebagai berikut:

Perkiraan yang dihasilkan dalam rencana kueri baik-baik saja, statistiknya mutakhir. Rentang tanggal ini harus menghasilkan sekitar 5mln baris. Ketika saya memberikan petunjuk indeks, maka pemilihan ini selesai dalam beberapa detik - tanpa petunjuk dibutuhkan beberapa menit untuk menyelesaikannya.

Ini adalah SQL Server 2019 dan secara umum saya perhatikan bahwa db lebih memilih pemindaian indeks berkerumun yang menggunakan pencarian kunci + non-cluster bahkan pada tabel yang lebih besar.

Saya lebih suka tidak menggunakan petunjuk karena:

• kadang-kadang saya memilih rentang yang lebih luas di mana pemindaian indeks berkerumun harus diinginkan
• tabel digunakan dalam tampilan dan saya tidak dapat memberikan petunjuk indeks ke tampilan

Apakah ada penjelasan mengapa db tidak menggunakan indeks NC dalam kasus ini?

Tautan ke rencana kueri:

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

Answer 1

Sepertinya SQL Server tidak menggunakan indeks tersebut secara default karena:

• itu adalah indeks yang difilter, dan
• kueri Anda diberi parameter

Anda dapat melihat peringatan ini di XML rencana eksekusi:

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

SQL Server tidak mengetahui nilai parameternya (karena berada dalam variabel), jadi SQL Server tidak dapat menggunakan indeks yang difilter dengan aman.

Salah satu solusinya adalah dengan menggunakan petunjuk indeks (seperti yang Anda sebutkan, ini tidak ideal).

Cara lain untuk menyiasatinya adalah dengan menggunakan SQL dinamis, seperti yang dijelaskan oleh Jeremiah Peschka di sini:

Indeks yang Difilter dan SQL Dinamis

Saya tidak tahu bagaimana indeks yang difilter ... difilter. Anda mungkin bisa lolos dengan menyematkan literal hanya pada salah satu dari dua nilai, untuk membatasi pembengkakan cache rencana.

Answer 2

Apakah ada penjelasan mengapa db tidak menggunakan indeks NC dalam kasus ini?

Perkiraan biaya rencana itu lebih rendah. Pemindaian indeks berkerumun menggunakan IO yang lebih berurutan dan pemindaian indeks yang tidak terkluster + pencarian bookmark menggunakan IO yang lebih acak. Jadi mana yang sebenarnya lebih cepat mungkin bergantung pada perangkat keras Anda.

Lihat statistik permintaan menunggu. Untuk pemindaian indeks berkerumun itu

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

Untuk indeks non-cluster

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

Tetapi kedua paket itu sangat mahal, jadi Anda harus melakukan sesuatu tentang itu. Opsi termasuk

1. Mengganti indeks berkerumun yang ada dengan sesuatu yang lebih berguna, seperti menambahkan Tanggal ke indeks pertama dan kemudian mempartisi indeks berkerumun berdasarkan tanggal.
2. Menyimpan tabel ini sebagai Toko Kolom Tergugus dan bukan sebagai Indeks Tergugus
3. Tidak berjalan select *dan menambahkan kolom disertakan yang dipilih ke indeks Tanggal.