Paramétrer et gérer les index

  • 6 minutes

La méthode la plus courante (et la plus efficace) pour paramétrer des requêtes T-SQL consiste à évaluer et à ajuster votre stratégie d’indexation. Les bases de données qui sont indexées correctement effectuent moins d’E/S pour retourner les résultats de requête, et moins d’E/S signifie moins de pression sur les systèmes d’E/S et de stockage. La réduction des E/S permet même une meilleure utilisation de la mémoire. Gardez à l’esprit le ratio lecture/écriture de vos requêtes.

Une charge de travail d’écriture importante peut indiquer que le coût de l’écriture de lignes dans les autres index n’est pas très avantageux. Elle le serait si la charge de travail effectuait principalement des mises à jour qui doivent également effectuer des opérations de recherche. Les opérations de mise à jour qui effectuent des recherches peuvent tirer parti des autres index ou colonnes qui sont ajoutés à un index existant. Votre objectif doit toujours être de tirer le meilleur parti du plus petit nombre possible d’index dans vos tables.

Voici une approche courante de réglage des performances :

  • Évaluez l’utilisation de l’index existant à l’aide de sys.dm_db_index_operational_stats et de sys.dm_db_index_usage_stats.

  • Éliminez les index inutilisés ou en double (cette opération doit être effectuée avec précaution). Certains index ne peuvent être utilisés qu’au cours d’opérations mensuelles/trimestrielles/annuelles, et peuvent être importants pour ces processus. Vous pouvez également créer des index afin de prendre en charge ces opérations juste avant la planification des opérations, dans le but de réduire la surcharge liée à l’absence d’index inutilisés dans une table.

  • Évaluez les requêtes les plus coûteuses dans le Magasin des requêtes ou dans la capture des événements étendus, puis concevez manuellement des index afin de mieux servir ces requêtes.

  • Créez le ou les index dans un environnement autre qu’un environnement de production, testez l’exécution et les performances des requêtes, puis observez les changements au niveau des performances. Il est important de noter les différences de matériel qui existent entre les environnements de production et les autres, car la quantité de mémoire et le nombre de processeurs peuvent affecter votre plan d’exécution.

  • Après des tests approfondis, implémentez les modifications apportées à votre système de production.

Vérifiez l’ordre des colonnes de vos index : la colonne de début génère les statistiques sur les colonnes, et détermine généralement si l’optimiseur va choisir l’index. Dans l’idéal, la colonne de début sera sélective et utilisée dans la clause WHERE d’un grand nombre de vos requêtes. Utilisez un processus de contrôle des modifications afin de suivre les modifications susceptibles d’affecter les performances de l’application. Avant de supprimer un index, enregistrez le code dans votre contrôle de code source, pour que l’index puisse être recréé rapidement si une requête rarement exécutée nécessite que l’index soit performant.

Enfin, les colonnes utilisées pour les comparaisons d’égalité doivent précéder les colonnes utilisées pour les comparaisons d’inégalité et les colonnes avec une plus grande sélectivité doivent précéder les colonnes avec moins de valeurs distinctes.

Index reprenable

Un index reprenable permet d’interrompre les opérations de maintenance d’index ou de les exécuter dans une fenêtre de temps spécifique, puis de les reprendre ultérieurement. Un bon exemple d’utilisation d’opérations d’index reprenable consiste à réduire l’impact de la maintenance de l’index dans un environnement de production chargé. Vous pouvez ensuite effectuer des opérations de régénération pendant une fenêtre de maintenance spécifique, ce qui vous donne plus de contrôle sur le processus.

En outre, la création d’un index pour une table volumineuse peut nuire aux performances de l’ensemble du système de base de données. La seule façon de résoudre ce problème dans les versions antérieures à SQL Server 2019 consiste à tuer le processus de création d’index. Ensuite, vous devez démarrer le processus à partir du début si le système restaure la session.

Avec un index reprenable, vous pouvez suspendre la génération, puis la redémarrer ultérieurement à l’endroit où elle a été interrompue.

L'exemple suivant montre comment créer un index reprenable :

-- Creates a nonclustered index for the Customer table

CREATE INDEX IX_Customer_PersonID_ModifiedDate 
    ON Sales.Customer (PersonID, StoreID, TerritoryID, AccountNumber, ModifiedDate)
WITH (RESUMABLE=ON, ONLINE=ON)
GO

Dans une fenêtre de requête, interrompez l’opération d’index :

ALTER INDEX IX_Customer_PersonID_ModifiedDate ON Sales.Customer PAUSE
GO

L’instruction ci-dessus utilise la clause PAUSE pour arrêter temporairement la création de l’index en ligne reprenable.

Vous pouvez vérifier l’état d’exécution actuel d’un index en ligne reprenable en interrogeant la vue système sys.index_resumable_operations.

Notes

Un index reprenable est pris en charge uniquement avec des opérations en ligne.