Optimisation SQL Server (Stop au arrêt serveur)

Pour améliorer le fonctionnement de SQL Server, bon nombre de consultants/éditeurs de logiciel préconisent un redémarre de l’instance afin que celle-ci reparte à « neuf » et que les performances soient bien meilleures. J’ai eu le cas, il n’y a pas très longtemps de la part d’un intégrateur SAGE qui me proposait en désespoir de cause de redémarrer le serveur toutes les semaines pour régler des problèmes de performances. Comme prévu cela n’a rien réglé.

Je suis tombé sur cet excellent article https://sqlpro.developpez.com/tutoriel/sql-server/arret-redemarrage-instances-bonne-pratique/ de Frédéric BROUARD. Il insiste sur le fait de ne pas stopper une instance SQL Server sans raison, car au fur et à mesure de l’utilisation l’instance met en oeuvre des mécanismes d’optimisation (cache, calcul de plan d’exécution). En cas de redémarrage tout cela est perdu.

Il fournit un petit script SQL permettant de trouver les meilleurs index à créer pour améliorer les performances d’exécution. Ces index sont basés sur les statistiques d’utilisation, il est donc indispensable que l’instance soit en fonctionnement depuis un certain temps et que l’utilisation qui en a été faîte soit représentative d’une utilisation normale. Voici son script reproduit tel quel :

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;

Une autre source d’information pour l’optimisation est de travailler sur les requêtes les plus consommatrices de CPU, et qui prennent le plus de temps à s’exécuter.

SELECT TOP 100
  total_worker_time/execution_count AS AvgCPUTime,
Execution_count,
total_elapsed_time/execution_count as AVGRunTime,
total_elapsed_time,
(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS Query_Text 
FROM sys.dm_exec_query_stats
ORDER BY AvgCPUTime DESC
Tagged in:

0 Comments

Leave a Reply

Votre adresse e-mail ne sera pas publiée.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.