I. L’auteur▲
|
|
Blog : |
II. Introduction▲
Parmi les pires pratiques 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…
Le fait de redémarrer SQL Server vide tous les caches (il y en a plus d’une centaine) ce qui pourrit les performances et fait perdre toutes les données 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ésout pas le problème, c’est bien pire, elle le masque et fait perdre tous les éléments de diagnostic qui auraient pu permettre de le résoudre.
III. Conséquences d’un arrêt d’une instance SQL Server▲
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 des 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 lents 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 lentes ;
- pour optimiser les pages en mémoire, et comme la RAM est très souvent plus limitée 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 optimal et ne conserve que les pages les plus accédées, il faut beaucoup de temps pour établir cette mesure 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 de faire fonctionner à nouveau 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é.
IV. 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 un gage de bonnes performances. Mais trop d’index tuent l’index. Ainsi est-il judicieux de poser des index qui engendrent 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é).
IV-A. Diagnostiquer les index manquants▲
Microsoft SQL Server est le seul SGBDR qui, depuis 2005 fournit un diagnostic des index manquants qui pourraient 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, voici un exemple d’utilisation de cette collection de DMV :
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
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 performants. 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.
IV-B. Diagnostiquer les index à supprimer▲
Dans le même esprit, il est intéressant de diagnostiquer les index potentiellement à supprimer.
Les index candidats potentiels* à la suppression sont les index redondants (ex : portant sur une première colonne unique augmentée d'autres colonnes ce faisant inutiles) ou non discriminants (ayant un mauvais facteur de filtrage) et qui concernent des tables non vides.
* Potentiels, car attention au cas des colonnes déclarées en "include" qui ne servent pas comme critère de recherche, mais rendent l'index couvrant pour certaines requêtes.
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 :
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
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 les index sont inutilisés depuis combien de temps ? Là est la question. Si votre instance de SQL Server vient de redémarrer alors ce diagnostic conduirait à supprimer de nombreux index qui vont être prochainement utilisés.
V. Depuis combien de temps tourne mon instance SQL Server ?▲
Là est la question essentielle concernant toutes les investigations dans les DMV fournissant 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 commencent 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 entête de tous mes diagnostics :
2.
3.
4.
5.
6.
7.
8.
9.
10.
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. Renouvelez votre demande ultérieurement.', 16, 1, @DT);
END
ELSE
SELECT ...
Les requêtes de cet article :
- Index à supprimer
- Index à créer
VI. Remerciements Developpez.com▲
Nous tenons à remercier escartefigue pour la relecture orthographique de cet article.