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
0 Comments