SQL Server kümelenmemiş dizini kullanmıyor

Question

Yaklaşık 470 mln satır içeren tablom var. Tarihe göre veri seçmek istiyorum. Bu tabloda oluşturulmuş iki endeksim var. Biri biri kümelenmiş ve diğeri tarih sütununda kümelenmemiş (tarih INT olarak saklanır). Basit bir seçim ifadem var:

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

Sorun, sorgu planının kümelenmemiş arama + anahtar bakma yerine kümelenmiş dizin taraması kullanmasıdır. Aşağıdaki gibi planlayın:

Sorgu planında oluşturulan tahminler gayet iyi, istatistikler güncel. Bu tarih aralığı, yaklaşık 5 mln satır sunmalıdır. Dizin ipucu verdiğimde, bu seçim birkaç saniye içinde tamamlanıyor - ipucu olmadan bitirmek birkaç dakika sürüyor.

Bu, SQL Server 2019 ve genel olarak konuşursak, db'nin daha büyük tablolarda bile kümelenmemiş + anahtar görünümleri kullanan kümelenmiş dizin taramalarını tercih ettiğini fark ettim.

İpucu kullanmamayı tercih ederim çünkü:

• bazen kümelenmiş dizin taramasının istendiği daha geniş aralıklar seçiyorum
• tablo görünümde kullanılıyor ve görünüme dizin ipucu sağlayamıyorum

Bu durumda db'nin neden NC indeksini kullanmadığına dair herhangi bir açıklama var mı?

Sorgu planlarına bağlantılar:

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

Answer 1

Görünüşe göre SQL Server bu dizini varsayılan olarak kullanmıyor çünkü:

• bu filtrelenmiş bir dizindir ve
• sorgunuz parametreleştirildi

Bu uyarıyı yürütme planı XML'inde görebilirsiniz:

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

SQL Server parametre değerlerinin ne olduğunu bilmez (çünkü değişkenler içindedirler), bu nedenle filtrelenmiş dizini güvenli bir şekilde kullanamaz.

Çözümlerden biri dizin ipuçlarını kullanmaktır (bahsettiğiniz gibi, bu ideal değildir).

Bunu aşmanın bir başka yolu da, burada Jeremiah Peschka tarafından açıklandığı gibi dinamik SQL kullanmaktır:

Filtrelenmiş Dizinler ve Dinamik SQL

Filtrelenmiş dizinin nasıl filtrelendiğini bilmiyorum. Önbellek şişmesini planlamak için değişmez değeri iki değerden yalnızca birine yerleştirmekten kurtulabilirsiniz.

Answer 2

Bu durumda db'nin neden NC indeksini kullanmadığına dair herhangi bir açıklama var mı?

Bu planın tahmini maliyeti daha düşüktür. Kümelenmiş dizin taraması daha sıralı GÇ kullanır ve kümelenmemiş dizin taraması + yer imi araması daha rasgele GÇ kullanır. Yani hangisinin daha hızlı olduğu donanımınıza bağlı olabilir.

Sorgu bekleme istatistiklerine bakın. Kümelenmiş dizin taraması için

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

Kümelenmemiş dizin için

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

Ancak her iki plan da çok pahalıdır, bu yüzden bununla ilgili bir şeyler yapmalısınız. Seçenekler şunları içerir

1. Mevcut kümelenmiş dizini, ilk dizine Tarih eklemek ve ardından kümelenmiş dizini tarihe göre bölümlemek gibi daha kullanışlı bir şeyle değiştirmek.
2. Bu tabloyu Clustered Index yerine Clustered Columnstore olarak saklama
3. Çalışmıyor select *ve seçilen dahil sütunları Tarih dizinine ekle.