Explorer les vérifications de maintenance de base de données

  • 7 minutes

L’optimiseur de requête utilise les informations statistiques des index pour tenter de créer un plan d’exécution optimal.

Dans Azure SQL, vous gérez des tâches de maintenance telles que les sauvegardes et les vérifications d’intégrité, et bien que vous puissiez vous reposer sur les mises à jour automatiques pour maintenir vos statistiques à jour, cela n’est parfois pas suffisant.

Le fait d’avoir des statistiques et des index sains garantit que tous les plans seront exécutés avec des performances optimales. La maintenance des index doit être effectuée régulièrement, car les données de vos bases de données changent avec le temps. Vous pouvez modifier votre stratégie de maintenance des index en fonction de la fréquence à laquelle vous apportez des modifications à vos données.

Reconstruire et réorganiser

Une fragmentation d’index a lieu lorsque l’ordre logique au sein des pages d’index ne correspond pas à l’ordre physique. Les pages peuvent ne pas être dans le bon ordre dans les instructions de modification de données de routine, comme UPDATE, DELETE et INSERT. La fragmentation peut entraîner des problèmes de performances en raison des E/S supplémentaires qui sont nécessaires pour localiser les données qui sont référencées par les pointeurs dans les pages d’index.

Lorsque des données sont insérées, mises à jour et supprimées dans l’index, l’ordre logique de l’index ne correspond plus à l’ordre physique des pages (que ce soit à l’intérieur des pages ou entre celles-ci) qui constituent les index. En outre, avec le temps, les modifications des données peuvent entraîner la dissémination ou la fragmentation des données dans la base de données. La fragmentation peut nuire aux performances des requêtes lorsque le moteur de base de données est obligé de lire des pages supplémentaires afin de localiser les données nécessaires.

La réorganisation d’un index est une opération en ligne qui défragmente le niveau feuille de l’index (cluster et non cluster). Ce processus de défragmentation réorganisera physiquement les pages de niveau feuille pour qu’elles correspondent à l’ordre logique des nœuds, de gauche à droite. Pendant ce processus, les pages d’index sont également compactées en fonction de la valeur de facteur de remplissage configurée.

Une reconstruction peut se faire en ligne ou hors ligne, en fonction de la commande exécutée ou de l’édition de SQL Server utilisée. Le processus de reconstruction hors ligne supprime et recrée l’index. Si vous pouvez le faire en ligne, un nouvel index sera créé parallèlement à l’index existant. Une fois que le nouvel index a été créé, l’index existant est supprimé, et le nouveau reçoit le nom de l’ancien index. N’oubliez pas que la version en ligne nécessite plus d’espace, car le nouvel index est créé parallèlement à l’index existant.

Les recommandations habituelles concernant la maintenance des index sont les suivantes :

  • > 5 % mais < 30 % : réorganiser l’index

  • > 30 % : reconstruire l’index

Servez-vous de ces nombres comme de recommandations générales. En fonction de votre charge de travail et de vos données, vous devrez peut-être adopter une méthode plus affirmée. Dans certains cas, vous pourrez reporter la maintenance des index pour les bases de données qui exécutent principalement des requêtes qui recherchent des pages spécifiques.

Les plateformes SQL Server et Azure SQL proposent des DMV qui vous permettent de détecter la fragmentation de vos objets. Les DMV les plus fréquemment utilisées à cet effet sont sys.dm_db_index_physical_stats pour les index b-tree et sys.dm_db_column_store_row_group_physical_stats pour les index columnstore.

Il est également important de noter que les regénérations d’index entraînent la mise à jour des statistiques d’index, ce qui peut améliorer les performances. La réorganisation d’index ne met pas à jour les statistiques.

Microsoft a introduit des opérations de reconstruction d’index reprenables avec SQL Server 2017. L’option des opérations de reconstruction d’index reprenables permet de contrôler plus facilement la durée d’une opération de reconstruction sur une instance donnée. La possibilité de contrôler un degré maximal de parallélisme associé a été ajoutée à SQL Server 2019 dans le but de fournir un contrôle plus précis aux administrateurs de base de données.

Statistiques

Lorsque vous réglez les performances dans Azure SQL, il est essentiel de comprendre l’importance des statistiques.

Les statistiques sont stockées dans la base de données utilisateur comme des objets blob. Ces objets blob contiennent des informations statistiques sur la distribution des valeurs de données dans une ou plusieurs colonnes d’une table ou d’une vue indexée.

Les statistiques contiennent des informations sur la distribution des valeurs de données dans une colonne. L’optimiseur de requête utilise des statistiques de colonnes et d’index pour déterminer la cardinalité, qui correspond au nombre de lignes qu’une requête est supposée retourner.

Des estimations de cardinalité sont ensuite utilisées par l’optimiseur de requête pour générer le plan d’exécution. Les estimations de cardinalité permettent également à l’optimiseur de déterminer le type d’opération (par exemple, une recherche ou une analyse d’index) à utiliser pour récupérer les données demandées.

Pour afficher la liste des statistiques définies par l’utilisateur avec la dernière date mise à jour, exécutez la requête ci-dessous :

SELECT sp.stats_id, 
       name, 
       last_updated, 
       rows, 
       rows_sampled
FROM sys.stats
     CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1

Create statistics

Lorsque l’option AUTO_CREATE_STATISTICS est définie sur ON, l’optimiseur de requête crée des statistiques sur la colonne indexée par défaut. L'optimiseur de requête crée également des statistiques pour les colonnes uniques des prédicats de requête.

Ces méthodes fournissent des plans de requêtes de haute qualité pour la plupart des requêtes. Parfois, vous devrez peut-être créer davantage de statistiques à l’aide de l’instruction CREATE STATISTICS pour améliorer des plans de requêtes spécifiques.

Il est recommandé de conserver l’option AUTO_CREATE_STATISTICS activée, car elle permet à l’optimiseur de requête de créer automatiquement des statistiques pour les colonnes de prédicat de requête.

Chaque fois que vous rencontrez les situations suivantes, envisagez de créer des statistiques :

  • L'Assistant Paramétrage du Moteur de base de données suggère de créer des statistiques
  • Le prédicat de requête contient plusieurs colonnes qui ne figurent pas déjà dans le même index
  • La requête effectue une sélection dans un sous-ensemble de données
  • Il manque des statistiques pour la requête

Automatisation des tâches de maintenance

Azure SQL fournit des outils natifs pour effectuer des tâches de maintenance de base de données à des fins d’automatisation. Différents outils sont disponibles en fonction de la plateforme sur laquelle la base de données s’exécute.

SQL Server sur une machine virtuelle Azure

Vous pouvez accéder à des services de planification tels que SQL Agent ou le Planificateur de tâches Windows. Ces outils d’automatisation peuvent permettre de réduire au maximum la fragmentation au sein des index. Avec les bases de données les plus volumineuses, vous devez trouver un équilibre entre reconstruction et réorganisation des index afin de garantir des performances optimales. La flexibilité fournie par SQL Agent ou par le Planificateur de tâches vous permet d’exécuter des tâches personnalisées.

Azure SQL Database

En raison de la nature d’Azure SQL Database, vous n’avez pas accès à SQL Server Agent ni au Planificateur de tâches Windows. Sans ces services, la maintenance d’index doit être créée en utilisant d’autres méthodes. Il existe trois façons de gérer les opérations de maintenance pour SQL Database :

  • Runbooks Azure Automation

  • Les travaux SQL Agent dans SQL Server sur une machine virtuelle Azure (appel distant)

  • Les travaux élastiques Azure SQL

Azure SQL Managed Instance

Comme avec SQL Server sur une machine virtuelle Azure, vous pouvez planifier des travaux sur SQL Managed Instance par le biais de SQL Server Agent. L’utilisation de SQL Server Agent offre la flexibilité nécessaire à l’exécution de code conçu dans le but de réduire la fragmentation au sein des index de la base de données.