SQL Server

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

Frédéric BROUARD

Pour réagir au contenu de cet article, un espace de dialogue vous est proposé sur le forum. Commentez

- Titre : SQL Server

- Auteur : Frédéric BROUARD

- Parution : 30 mai 2022

- Public visé :

- Licence : Copyright ® 2020 Frédéric BROUARD. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. L’auteur

- II. Introduction

- III. Conséquences d’un arrêt d’une instance SQL Server

- IV. Un exemple au sujet des DMV… Les index !

- IV-A. Diagnostiquer les index manquants

- IV-B. Diagnostiquer les index à supprimer

- V. Depuis combien de temps tourne mon instance SQL Server ?

- VI. Remerciements Developpez.com

I - L’auteur

Blog :
https://blog.developpez.com/sqlpro
Le site :
https://sqlpro.developpez.com

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 :

1. WITH

2. IDX_DIAG AS

3. (

4. SELECT mid.index_handle, statement,

5. COALESCE(equality_columns + N', ' + inequality_columns,

6. equality_columns, inequality_columns) AS INDEX_KEY,

7. COALESCE(included_columns, N'') AS INCLUDE_LIST,

8. avg_total_user_cost * avg_user_impact AS GAIN,

9. ROW_NUMBER() OVER(ORDER BY avg_total_user_cost * avg_user_impact DESC) AS RN,

10. COUNT(*) OVER() AS NB

11. FROM   sys.dm_db_missing_index_details AS mid

12. JOIN sys.dm_db_missing_index_groups AS mig

13. ON mid.index_handle = mig.index_handle

14. JOIN sys.dm_db_missing_index_group_stats AS migs

15. ON mig.index_group_handle = migs.group_handle

16. ),

17. IDX_GAIN AS

18. (

19. SELECT DISTINCT statement, INDEX_KEY, INCLUDE_LIST

20. FROM   IDX_DIAG

21. WHERE  (100.0 * RN) / NB < 30 --> 30% des index les plus performants

22. ),

23. IDX_METER AS

24. (

25. SELECT id.statement, id.INDEX_KEY, id.INCLUDE_LIST, MAX(GAIN) AS GAIN_MAX

26. FROM   IDX_DIAG AS id

27. JOIN IDX_GAIN as ig

28. ON id.statement = ig.statement

29. AND id.INDEX_KEY = ig.INDEX_KEY

30. AND id.INCLUDE_LIST = ig.INCLUDE_LIST

31. GROUP  BY id.statement, id.INDEX_KEY, id.INCLUDE_LIST

32. )

33. SELECT *,

34. N'CREATE INDEX X_SQLpro__' + REPLACE(CAST(NEWID() AS VARCHAR(38)), N'-', N'_')

35. + N'__' + REPLACE(CAST(CAST(GETDATE() AS DATE) AS CHAR(10)), N'-', N'')

36. + N' ON ' + statement

37. + N' (' + INDEX_KEY + N')'

38. + CASE INCLUDE_LIST WHEN N'' THEN N''

39. ELSE N' INCLUDE(' + INCLUDE_LIST + N')' END + N';' AS SQL_CMD

40. FROM IDX_METER

41. 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 :

1. SELECT DB_NAME() AS DATABASE_NAME, s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME,

2. i.name AS INDEX_NAME,

3. (SELECT STRING_AGG(c.name, ', ')

4. WITHIN GROUP (ORDER BY ic.key_ordinal)

5. FROM sys.index_columns AS ic

6. JOIN sys.columns AS c

7. ON ic.object_id = c.object_id

8. AND ic.column_id = c.column_id

9. WHERE  ic.object_id = ius.object_id

10. AND ic.index_id = ius.index_id

11. AND ic.is_included_column = 0) AS INDEX_KEY,

12. (SELECT STRING_AGG(c.name, ', ')

13. FROM sys.index_columns AS ic

14. JOIN sys.columns AS c

15. ON ic.object_id = c.object_id

16. AND ic.column_id = c.column_id

17. WHERE  ic.object_id = ius.object_id

18. AND ic.index_id = ius.index_id

19. AND ic.is_included_column = 1) AS INDEX_INCLUDE,

20. filter_definition,

21. ius.user_updates,

22. N'DROP INDEX [' + i.name + N'] ON [' + s.name + N'].[' + o.name + N'];' AS DROP_INDEX

23. FROM   sys.dm_db_index_usage_stats AS ius

24. JOIN sys.indexes AS i

25. ON ius.object_id = i.object_id

26. AND ius.index_id = i.index_id

27. JOIN sys.objects AS o

28. ON ius.object_id = o.object_id

29. JOIN sys.schemas AS s

30. ON o.schema_id = s.schema_id

31. WHERE  ius.database_id = DB_ID()

32. -- pas de seek, ni de lookup ni de scan

33. AND  ius.user_seeks = 0

34. AND  ius.user_lookups = 0

35. AND  ius.user_scans = 0

36. -- pas hypothétique

37. AND  i.is_hypothetical = 0

38. -- pas sémantique

39. AND  i.is_primary_key = 0

40. AND  i.is_unique = 0

41. AND  i.is_unique_constraint = 0

42. -- et qu'il y ait des lignes dans la table

43. AND  EXISTS(SELECT *

44. FROM   sys.dm_db_partition_stats AS ps

45. WHERE  ius.object_id = ps.object_id

46. 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 :

1. IF NOT EXISTS (SELECT *

2. FROM   sys.dm_os_sys_info

3. WHERE  sqlserver_start_time < DATEADD(day, -31, GETDATE()))

4. BEGIN

5. DECLARE @DT VARCHAR(16) = (SELECT CONVERT(VARCHAR(16), sqlserver_start_time, 112)

6. FROM sys.dm_os_sys_info)

7. 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);

8. END

9. ELSE

10. SELECT ...

Les requêtes de cet article :

- Index à supprimer

1. /******************************************************************************

2. * INDEX À SUPPRIMER - SQLpro - Frédéric BROUARD - 2022-03-23 - SQL SPOT SARL  *

3. ******************************************************************************/

4. IF NOT EXISTS (SELECT *

5. FROM   sys.dm_os_sys_info

6. WHERE  sqlserver_start_time < DATEADD(day, -31, GETDATE()))

7. BEGIN

8. DECLARE @DT VARCHAR(16) = (SELECT CONVERT(VARCHAR(16), sqlserver_start_time, 112)

9. FROM sys.dm_os_sys_info)

10. 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);

11. END

12. ELSE

13. SELECT DB_NAME() AS DATABASE_NAME, s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME,

14. i.name AS INDEX_NAME,

15. (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal)

16. FROM   sys.index_columns AS ic

17. JOIN sys.columns AS c

18. ON ic.object_id = c.object_id

19. AND ic.column_id = c.column_id

20. WHERE  ic.object_id = ius.object_id

21. AND ic.index_id = ius.index_id

22. AND ic.is_included_column = 0) AS INDEX_KEY,

23. (SELECT STRING_AGG(c.name, ', ')

24. FROM   sys.index_columns AS ic

25. JOIN sys.columns AS c

26. ON ic.object_id = c.object_id

27. AND ic.column_id = c.column_id

28. WHERE  ic.object_id = ius.object_id

29. AND ic.index_id = ius.index_id

30. AND ic.is_included_column = 1) AS INDEX_INCLUDE,

31. filter_definition,

32. ius.user_updates,

33. N'DROP INDEX [' + i.name + N'] ON [' + s.name + N'].[' + o.name + N'];' AS DROP_INDEX

34. FROM   sys.dm_db_index_usage_stats AS ius

35. JOIN sys.indexes AS i

36. ON ius.object_id = i.object_id

37. AND ius.index_id = i.index_id

38. JOIN sys.objects AS o

39. ON ius.object_id = o.object_id

40. JOIN sys.schemas AS s

41. ON o.schema_id = s.schema_id

42. WHERE  ius.database_id = DB_ID()

43. -- pas de seek, ni de lookup ni de scan

44. AND  ius.user_seeks = 0

45. AND  ius.user_lookups = 0

46. AND  ius.user_scans = 0

47. -- pas hypothétique

48. AND  i.is_hypothetical = 0

49. -- pas sémantique

50. AND  i.is_primary_key = 0

51. AND  i.is_unique = 0

52. AND  i.is_unique_constraint = 0

53. -- et qu'il y ait des lignes dans la table

54. AND  EXISTS(SELECT *

55. FROM   sys.dm_db_partition_stats AS ps

56. WHERE  ius.object_id = ps.object_id

57. AND row_count > 0);

58. GO

- Index à créer

1. /******************************************************************************

2. * INDEX À CRÉER  - SQLpro -  Frédéric BROUARD  - 2022-03-23 -  SQL SPOT SARL  *

3. ******************************************************************************/

4. IF NOT EXISTS (SELECT *

5. FROM   sys.dm_os_sys_info

6. WHERE  sqlserver_start_time < DATEADD(day, -31, GETDATE()))

7. BEGIN

8. DECLARE @DT VARCHAR(16) = (SELECT CONVERT(VARCHAR(16), sqlserver_start_time, 112)

9. FROM sys.dm_os_sys_info)

10. 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);

11. END

12. ELSE

13. WITH

14. IDX_DIAG AS

15. (

16. SELECT mid.index_handle, statement,

17. COALESCE(equality_columns + N', ' + inequality_columns,

18. equality_columns, inequality_columns) AS INDEX_KEY,

19. COALESCE(included_columns, N'') AS INCLUDE_LIST,

20. avg_total_user_cost * avg_user_impact AS GAIN,

21. ROW_NUMBER() OVER(ORDER BY avg_total_user_cost * avg_user_impact DESC) AS RN,

22. COUNT(*) OVER() AS NB

23. FROM   sys.dm_db_missing_index_details AS mid

24. JOIN sys.dm_db_missing_index_groups AS mig

25. ON mid.index_handle = mig.index_handle

26. JOIN sys.dm_db_missing_index_group_stats AS migs

27. ON mig.index_group_handle = migs.group_handle

28. ),

29. IDX_GAIN AS

30. (

31. SELECT DISTINCT statement, INDEX_KEY, INCLUDE_LIST

32. FROM   IDX_DIAG

33. WHERE  (100.0 * RN) / NB < 30 --> 30% des index les plus performants

34. ),

35. IDX_METER AS

36. (

37. SELECT id.statement, id.INDEX_KEY, id.INCLUDE_LIST, MAX(GAIN) AS GAIN_MAX

38. FROM   IDX_DIAG AS id

39. JOIN IDX_GAIN as ig

40. ON id.statement = ig.statement

41. AND id.INDEX_KEY = ig.INDEX_KEY

42. AND id.INCLUDE_LIST = ig.INCLUDE_LIST

43. GROUP  BY id.statement, id.INDEX_KEY, id.INCLUDE_LIST

44. )

45. SELECT *,

46. N'CREATE INDEX X_SQLpro__' + REPLACE(CAST(NEWID() AS VARCHAR(38)), N'-', N'_')

47. + N'__' + REPLACE(CAST(CAST(GETDATE() AS DATE) AS CHAR(10)), N'-', N'')

48. + N' ON ' + statement

49. + N' (' + INDEX_KEY + N')'

50. + CASE INCLUDE_LIST WHEN N'' THEN N''

51. ELSE N' INCLUDE(' + INCLUDE_LIST + N')' END + N';' AS SQL_CMD

52. FROM IDX_METER

53. ORDER BY GAIN_MAX DESC;

VI - Remerciements Developpez.com

Nous tenons à remercier escartefigue pour la relecture orthographique de cet article.