Arrêt et re-démarrage d’instance SQL Server… Bonne pratique ou pas ?

Parmi les pires pratique que l’on rencontre encore couramment au sujet de Microsoft SQL Server, il y a le fait que redémarrer une instance régulièrement serait bénéfique pour les bases de données d’une instance SQL Server.  Ceci est faux et c’est bien tout le contraire qui se produit. Explications…

Par Frédéric Brouard (alias SQLpro) – 2022-03-23

Le fait de redémarrer SQL Server vide tous les caches (il y en a plus d’une centaine) ce qui pourri les performances et fait perdre toutes les donnés de diagnostic accumulées en mémoire.

C’est une mauvaise pratique hélas répandue, qui pour beaucoup est une solution de facilité quand on ne sait pas résoudre un problème… Sauf que cette solution ne résous pas le problème, c’est bien pire, elle le masque et fait perdre tous les éléments de diagnostic qui aurait pu permettre de le résoudre.

Pour information, voici les conséquences néfastes d’un arrêt d’une instance SQL Server :

Le cache de plan de SQL Server stocke les plans d’exécution pré-compilés pour les requêtes fréquemment exécutées. Il améliore les performances des requêtes en réduisant le coût de création et de compilation répétée d’un plan d’exécution similaire. Au redémarrage de SQL Server, la zone de cache de plan étant vide, cela nécessite de créer de nouveaux plans de requête ce qui allonge drastiquement les temps de réponse des requêtes.

Le cache des données de SQL Server (mémoire tampon) contient les pages de tables et d’index les plus fréquemment scrutées afin de réduire le nombre les Entrées/Sorties (E/S en français, IO en anglais), c’est-à-dire les accès disque qui sont 1 000 à 10 000 fois plus lent qu’une lecture en mémoire. Ceci a deux conséquences :

  • Au redémarrage de SQL Server le pool de mémoire tampon étant vide (cache des données), tous les accès se font depuis les disques les requêtes deviennent donc lente.
  • Pour optimiser les pages en mémoire, et comme la RAM est très souvent plus limitées que le disque, seules les pages les plus accédées sont mise en mémoire dans la limite de RAM disponible. Pour que ce cache soit optimum et ne conserve que les pages les plus accédées, il faut beaucoup de temps pour établir cette métrique basée sur des statistiques d’accès.

Les vues et fonctions de gestion dynamique de SQL Server (DMV : Data Management View – au nombre de plus de 300) renvoient des informations sur l’état du serveur et sont utilisées pour surveiller l’intégrité d’une instance de serveur, diagnostiquer les problèmes et régler les performances. Les données de ces DMV sont stockées en mémoire, dans l’espace de processus de sqlservr.exe, et perdues en cas d’arrêt de l’instance, obérant toute probabilité de diagnostiquer les causes d’un dysfonctionnement. De nombreuses DMV sont utilisées pour régler les performances (tuning). Lorsque les données statistiques accumulées sur plusieurs semaines sont perdues, cela nécessite à nouveau de faire fonctionner l’instance au moins pendant un mois avant de pouvoir établir de nouveau un diagnostic sérieux d’optimisation.

Récupération forcée au redémarrage : lors d’un arrêt d’instance, certaines transactions étant en cours, le journal des transactions conserve l’état de la base et impose une annulation (ROLLBACK) des transactions non terminées. Cette annulation ne peut se faire qu’au moment du redémarrage (phase de récupération – Recovery) ce qui interdit tout accès à la base pendant toute la durée de la phase. Si cet arrêt survient pendant un moment de forte activité, le temps d’indisponibilité de la base peut devenir gênant pour la production.

Montée en charge des ressources mémoire : le temps que SQL Server récupère la RAM qu’il doit acquérir de Windows pour servir ses différents niveaux de cache (SQL Server comporte plus de 100 niveaux de cache visibles avec la commande DBCC MEMORYSTATUS;) est un temps perdu à la fois pour SQL Server et pour l’OS.

Il est donc extrêmement important de ne jamais redémarrer un serveur SQL, excepté pour des raisons de sécurité, notamment lors du passage d’un CU corrigeant une vulnérabilité.

Un exemple au sujet des DMV… Les index !

L’indexation est un sujet très sensible concernant les performances. La pose d’index est de loin, l’élément le plus crucial pour obtenir de bonnes performances pour la plupart des requêtes de lecture, mais aussi pour les écritures, notamment pour la modification des données (UPDATE) comme pour la suppression des lignes (DELETE).

Une base bien indexée est gage de bonne performances. Mais trop d’index tue l’index. Ainsi est-il judicieux de poser des index qui ont un gain significatif. Si un index divise le temps de réponse par 10, 100 ou 1000, alors il est nécessaire de l’implanter. À l’inverse un index qui me permettrait de gagner 30% de temps sur une requête n’a pas d’intérêt. Pire, il sera néfaste s’il porte sur une table dotée d’un fort nombre de lignes (cardinalité).

Diagnostiquer les index manquants

Microsoft SQL Server est le seul SGBDR qui, depuis 2005 fournit un diagnostic des index manquant qui pourrait optimiser l’exécution des requêtes.

Voici la liste des DMV qui permettent de diagnostiquer les index manquants :

  • sys.dm_db_missing_index_details
  • sys.dm_db_missing_index_groups
  • sys.dm_db_missing_index_group_stats,
  • sys.dm_db_missing_index_group_stats_query
  • sys.dm_db_missing_index_columns(index_handle)

Et maintenant un exemple d’utilisation de cette collection de DMV :

WITH
IDX_DIAG AS
(
SELECT mid.index_handle, statement,
       COALESCE(equality_columns + N', ' + inequality_columns, 
                equality_columns, inequality_columns) AS INDEX_KEY, 
       COALESCE(included_columns, N'') AS INCLUDE_LIST,
       avg_total_user_cost * avg_user_impact AS GAIN,
       ROW_NUMBER() OVER(ORDER BY avg_total_user_cost * avg_user_impact DESC) AS RN,
       COUNT(*) OVER() AS NB
FROM   sys.dm_db_missing_index_details AS mid
       JOIN sys.dm_db_missing_index_groups AS mig 
          ON mid.index_handle = mig.index_handle
       JOIN sys.dm_db_missing_index_group_stats AS migs 
          ON mig.index_group_handle = migs.group_handle
),
IDX_GAIN AS
(
SELECT DISTINCT statement, INDEX_KEY, INCLUDE_LIST
FROM   IDX_DIAG
WHERE  (100.0 * RN) / NB < 30 --> 30% des index les plus performants
),
IDX_METER AS
(
SELECT id.statement, id.INDEX_KEY, id.INCLUDE_LIST, MAX(GAIN) AS GAIN_MAX
FROM   IDX_DIAG AS id
       JOIN IDX_GAIN as ig
          ON id.statement = ig.statement 
             AND id.INDEX_KEY = ig.INDEX_KEY
             AND id.INCLUDE_LIST = ig.INCLUDE_LIST
GROUP  BY id.statement, id.INDEX_KEY, id.INCLUDE_LIST
)
SELECT *,
       N'CREATE INDEX X_SQLpro__' + REPLACE(CAST(NEWID() AS VARCHAR(38)), N'-', N'_')
       + N'__' + REPLACE(CAST(CAST(GETDATE() AS DATE) AS CHAR(10)), N'-', N'') 
       + N' ON ' + statement 
       + N' (' + INDEX_KEY + N')'
       + CASE INCLUDE_LIST WHEN N'' THEN N''
              ELSE N' INCLUDE(' + INCLUDE_LIST + N')' END + N';' AS SQL_CMD
FROM IDX_METER
ORDER BY GAIN_MAX DESC;

NOTA : requête à scope de niveau instance.

Cette requête fournit les informations et le code SQL pour créer les 30% des index manquants ayant le plus fort gain pour toutes les bases de l’instance MS SQL Server.

Le seul inconvénient de cette approche est que si le serveur a redémarré depuis peu cette liste sera très loin d’être exhaustive et conduira a créer des index probablement peu performant. En effet il n’est pas rare dans une base de données, que certains processus de traitement ne s’activent qu’une fois par semaine, d’autres une fois par mois… Et souvent ce sont les plus lourds, les plus consommateurs, bref ceux qui ont le plus besoin d’index !

Diagnostiquer les index à supprimer

Dans le même esprit, il est intéressant de diagnostiquer les index à supprimer. Ce sont ceux :

  • qui ne sont jamais utilisés
  • qui ne sont pas sémantique (clé primaire, clé alternative ou encore unique)
  • qui portent sur des tables non vide

Ce diagnostic nécessite l’utilisation de la DMV sys.dm_db_index_usage_stats. La requête suivante donne la liste des index inutilisés :

SELECT DB_NAME() AS DATABASE_NAME, s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME,
       i.name AS INDEX_NAME, 
       (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal)
        FROM   sys.index_columns AS ic 
               JOIN sys.columns AS c 
                  ON ic.object_id = c.object_id 
                     AND ic.column_id = c.column_id
        WHERE  ic.object_id = ius.object_id 
               AND ic.index_id = ius.index_id
               AND ic.is_included_column = 0) AS INDEX_KEY,
       (SELECT STRING_AGG(c.name, ', ')
        FROM   sys.index_columns AS ic 
               JOIN sys.columns AS c 
                  ON ic.object_id = c.object_id 
                     AND ic.column_id = c.column_id
        WHERE  ic.object_id = ius.object_id 
               AND ic.index_id = ius.index_id
               AND ic.is_included_column = 1) AS INDEX_INCLUDE,
       filter_definition,
       ius.user_updates,
       N'DROP INDEX [' + i.name + N'] ON [' + s.name + N'].[' + o.name + N'];' AS DROP_INDEX
FROM   sys.dm_db_index_usage_stats AS ius
       JOIN sys.indexes AS i 
          ON ius.object_id = i.object_id
	          AND ius.index_id = i.index_id
       JOIN sys.objects AS o 
          ON ius.object_id = o.object_id
	    JOIN sys.schemas AS s 
          ON o.schema_id = s.schema_id
WHERE  ius.database_id = DB_ID()
-- pas de seek, ni de lookup ni de scan
  AND  ius.user_seeks = 0
  AND  ius.user_lookups = 0
  AND  ius.user_scans = 0
-- pas hypothétique
  AND  i.is_hypothetical = 0
-- pas sémantique
  AND  i.is_primary_key = 0
  AND  i.is_unique = 0
  AND  i.is_unique_constraint = 0
-- et qu'il y ait des lignes dans la table
  AND  EXISTS(SELECT * 
              FROM   sys.dm_db_partition_stats AS ps 
			     WHERE  ius.object_id = ps.object_id 
                     AND row_count > 0);

NOTA : requête à scope de niveau base de données.

Mais inutilisés depuis combien de temps ? Là est la question. Si votre instance de SQL Server vient de redémarrée alors ce diagnostic conduirait à supprimer de nombreux index qui vont être prochainement utilisés.

Depuis combien de temps tourne mon instance SQL Server ?

Là est la question essentielle concernant toutes les investigations dans les DMV founissant un quelconque diagnostic…
Et là encore, un DMV va venir à notre secours : sys.dm_os_sys_info. La colonne « sqlserver_start_time » fournit la date et l’heure du dernier redémarrage de l’instance…
C’est la raison pour laquelle toutes mes requêtes de diagnostic, commence par se poser la question de la profondeur de la rémanence des statistiques collectées par les DMV. Voici la requête préalable que je fais et le script que je place en en entête de tous mes diagnostics :

IF NOT EXISTS (SELECT * 
               FROM   sys.dm_os_sys_info 
               WHERE  sqlserver_start_time < DATEADD(day, -31, GETDATE()))
BEGIN
   DECLARE @DT VARCHAR(16) = (SELECT CONVERT(VARCHAR(16), sqlserver_start_time, 112)
                              FROM sys.dm_os_sys_info)
   RAISERROR('La profondeur d''analyse pour diagnostiquer les index à supprimée est insuffisante, le serveur ayant redémarré le %s, soit moins d''un mois. Renouvellez votre demande ultérieurement.', 16, 1, @DT);
END
ELSE
   SELECT ...

Les requêtes de cet article !


Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Ce contenu a été publié dans DBA, Requêtes de métadonnées, scripts SQL, SQL Server, Tips and tricks, avec comme mot(s)-clé(s) , , , , , . Vous pouvez le mettre en favoris avec ce permalien.