Coût de l'index SQL Server

J'ai lu que l'un des compromis pour l'ajout d'index de table dans SQL Server est le coût accru des requêtes d'insertion / mise à jour / suppression pour améliorer les performances des requêtes select.

Je peux conceptuellement comprendre ce qui se passe dans le cas d'une insertion car SQL Server doit écrire des inputs dans chaque index correspondant aux nouvelles lignes, mais la mise à jour et la suppression sont un peu plus floues car je ne peux pas vraiment comprendre moteur de database doit faire.

Prenons DELETE comme exemple et supposons que j'ai le schéma suivant (pardonnez le pseudo-SQL)

TABLE Foo col1 int ,col2 int ,col3 int ,col4 int PRIMARY KEY (col1,col2) INDEX IX_1 col3 INCLUDE col4

Maintenant, si je publie la déclaration

 DELETE FROM Foo WHERE col1=12 AND col2 > 34

Je comprends ce que le moteur doit faire pour mettre à jour la table (ou index cluster si vous préférez). L'index est configuré pour faciliter la search de la plage de lignes à supprimer et le faire.

Cependant, à ce stade, il doit également mettre à jour IX_1 et la requête que je lui ai donnée ne donne aucun moyen efficace évident pour le moteur de database de find les lignes à mettre à jour. Est-il obligé de faire une parsing complète de l'index à ce stade? Le moteur lit-il d'abord les lignes de l'index cluster et génère une suppression interne plus intelligente par rapport à l'index?

Cela pourrait m'aider à comprendre ce qui se passe sous le capot, mais je suppose que ma vraie question est la suivante. J'ai une database qui passe beaucoup de time à supprimer et j'essaie de comprendre ce que je peux faire à ce sujet.

Lorsque j'affiche le plan d'exécution pour la suppression, il affiche simplement une input pour "Clustered Index Delete" sur la table Foo qui list dans la section des détails les autres index qui doivent être mis à jour mais je n'ai aucune indication du coût relatif de ces autres indices.

Sont-ils tous égaux dans ce cas? Existe-t-il un moyen d'estimer l'impact de la suppression d'un ou plusieurs de ces indices sans avoir à l'essayer?

Les index non cluster stockent également les keys en cluster .
Il n'a pas besoin de faire un scan complet, car:

votre requête utilisera l'index clusterisé pour localiser les lignes
les lignes contiennent l'autre valeur d'index (c3)
En utilisant l'autre valeur d'index (c3) et les valeurs d'index clusterisées (c1, c2), il peut localiser les inputs correspondantes dans l'autre index.

(Note: J'ai eu de la difficulté à interpréter les docs, mais j'imagine que IX_1 dans votre cas pourrait être défini comme s'il était également sortingé sur c1, c2, puisque ceux-ci sont déjà stockés dans l'index, il serait parfaitement logique de les utiliser pour localiser plus efficacement les loggings pour, par exemple, les mises à jour et les suppressions.)

Tout cela, cependant, a un coût. Pour chaque rangée correspondante:

il doit lire la ligne, pour connaître la valeur de c3
il doit find l'input pour (c3, c1, c2) dans l'index non cluster
il doit également supprimer l'input.

En outre, alors que la requête de plage peut être efficace sur l'index clusterisé dans votre cas (access linéaire, après avoir trouvé une correspondance), la maintenance des autres index entraînera vraisemblablement un access random à chaque index correspondant . L'access random a un coût beaucoup plus élevé que l'énumération des noeuds feuille B + à partir d'une correspondance donnée.
Compte tenu de la requête ci-dessus, plus de time est consacré à la maintenance de l'index non clusterisé – la quantité dépend fortement du nombre d'loggings sélectionnés par le prédicat col1 = 12 AND col2 > 34 .

Je suppose que le coût est conceptuellement le même que si vous n'aviez pas d'index secondaire mais que vous aviez par exemple une table séparée, contenant (c3, c1, c2) les seules colonnes d'une key groupée et que vous avez DELETE rangée en utilisant (c3, c1, c2). De toute évidence, la maintenance d'index est interne à SQL Server et est plus rapide, mais conceptuellement, je suppose que ce qui précède est proche.

Ce qui précède signifierait que les coûts de maintenance des index restraient assez proches les uns des autres, puisque le nombre d'inputs dans chaque index secondaire est le même (le nombre d'loggings) et la suppression ne peut se faire qu'une par une sur chaque index.

Si vous avez besoin des index, en termes de performances, en fonction du nombre d'loggings supprimés, mieux vaut planifier les suppressions, en supprimant les index – qui ne sont pas utilisés lors de la suppression – avant la suppression et en les rajoutant après. Selon le nombre d'loggings affectés, la reconstruction des index peut être plus rapide .