SQL Server n'utilise pas d'index non cluster

Question

J'ai une table qui contient environ 470 mln de lignes. Je souhaite sélectionner des données en fonction de la date. J'ai deux indices créés sur cette table. L'un est groupé l'un et l'autre n'est pas groupé sur la colonne de date (la date est stockée comme INT). J'ai une instruction de sélection simple:

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

Le problème est que le plan de requête utilise l'analyse d'index cluster au lieu de seek + keylookup non cluster. Planifiez comme suit:

Les estimations générées dans le plan de requête sont correctes, les statistiques sont à jour. Cette plage de dates devrait fournir environ 5 millions de lignes. Lorsque je fournis un indice d'index, cette sélection se termine en quelques secondes - sans indice, cela prend quelques minutes.

Il s'agit de SQL Server 2019 et, de manière générale, j'ai remarqué que db préfère les analyses d'index en cluster qui utilisent des recherches de clés non en cluster, même sur des tables plus grandes.

Je préfère ne pas utiliser d'indice car:

• Parfois, je sélectionne des plages plus larges où l'analyse d'index groupé devrait être souhaitable
• table est utilisée dans la vue et je ne peux pas fournir d'indice d'index à la vue

Y a-t-il une explication pour laquelle db n'utilise pas l'index NC dans ce cas?

Liens vers les plans de requête:

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

Answer 1

Il semble que SQL Server n'utilise pas cet index par défaut car:

• c'est un index filtré, et
• votre requête est paramétrée

Vous pouvez voir cet avertissement dans le XML du plan d'exécution:

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

SQL Server ne sait pas quelles sont les valeurs des paramètres (car elles sont dans des variables), il ne peut donc pas utiliser en toute sécurité l'index filtré.

Une solution consiste à utiliser des indices d'index (comme vous l'avez mentionné, ce n'est pas idéal).

Une autre façon de contourner ce problème consiste à utiliser SQL dynamique, comme décrit par Jeremiah Peschka ici:

Index filtrés et SQL dynamique

Je ne sais pas comment l'index filtré est ... filtré. Vous pourrez peut-être vous en sortir en incorporant le littéral sur une seule des deux valeurs, pour limiter le gonflement du cache du plan.

Answer 2

Y a-t-il une explication pour laquelle db n'utilise pas l'index NC dans ce cas?

Le coût estimé de ce plan est inférieur. Le scan d'index clusterisé utilise plus d'E / S séquentielles et l'analyse d'index non cluster + recherche de signets utilise plus d'E / S aléatoires. Donc, lequel est réellement plus rapide peut dépendre de votre matériel.

Regardez les statistiques d'attente de la requête. Pour l'analyse d'index cluster, c'est

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

Pour l'index non clusterisé, c'est

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

Mais les deux plans sont très chers, vous devriez donc faire quelque chose à ce sujet. Les options comprennent

1. Remplacement de l'index clusterisé existant par quelque chose de plus utile, comme l'ajout de Date au premier index, puis le partitionnement de l'index clusterisé par date.
2. Stockage de cette table en tant que magasin de colonnes en cluster au lieu d'un index en cluster
3. Ne fonctionne pas select *et ajoute les colonnes incluses sélectionnées à l'index de date.