SQLpro
[Actualité] Audit global des performances d'un serveur MS SQL Server
par , 16/02/2021 à 19h57 (7075 Affichages)
- La première des choses à voir sur un serveur MS SQL est de voir si toutes les bonnes pratiques ont été appliquées. La plupart du temps cela débloque 50 à 90 % des problèmes.
- La seconde des choses à faire consiste à mener une campagne d'indexation : rajouter les index manquants et supprimer les index inutiles, redondants (doublons) ou inclus.
- La troisième des choses est de voir quels sont les objets lents (pas seulement les requêtes) : requêtes, procédures, déclencheurs et UDF.
Si, et seulement si, ces trois choses-là n'ont pas résorbé le problème, alors on peut commencer à utiliser le profileur SQL et l'analyseur de performances. Si cela ne suffit pas, on peut alors utiliser les événements étendus.
1 - les bonnes pratiques
1.1 - Configuration OS
Si VM, voir si
1.1.1 - les cœurs ne sont pas flottant
1.1.2 - la RAM n'est pas en "balooning"
1.1.3 - le stockage est dédié
Voir les bonnes pratiques VMWare :
https://www.vmware.com/content/dam/d...ices-guide.pdf
Au niveau Windows, vérifier si le mode économie d'énergie est actif. Si oui, le désactiver pour rouler au max de fréquence des CPU.
1.2 - Configuration périmétrique
Vérifiez qu'il n'y a pas d'antivirus.
S'il y en a, isolez tous les répertoires de données des bases de production et système.
Préférez l'antivirus MS qui n'est pas plus mauvais que les autres, mais plus léger et ne s'occupe pas des éléments MS et notamment de SQL Server.
Vérifiez que SQL Server tourne sur une machine dédiée, ce qui signifie :
aucune autre instance MS SQL Server
aucun autre service applicatif d'aucun genre (autre SGBD, application tierce....)
Désactivez le maximum de services Windows inutiles.
1.3 - configuration d'instance SQL Server
Vérifiez que votre instance SQL Server soit à jour des derniers SP pour les versions jusqu'à 2016 et CU pour les versions à partir de 2017
aidez vous de : https://sqlserverbuilds.blogspot.com/
Vérifiez les paramètres suivants (sp_configure) :
affinity I/O mask, affinity mask, affinity64 I/O mask, affinity64 mask à 0
cost threshold for parallelism : à mettre entre 12 et 100 suivant importance des bases (12 petites bases, 24 moyennes bases, 50 grosses bases, 100 VLDB)
max degree of parallelism : à positionner en fonction du nombre de cœurs si pas fait au niveau installation, avec le calcul suivant :
Pour une solution OLTP
Code : Sélectionner tout - Visualiser dans une fenêtre à part
2
3
4
5
6
7
8
9
10Nombre de cœurs MAXDOP ----------------- ------- < 8 2 8 à 12 3 14 à 20 4 22 à 32 5 34 à 48 6 50 à 64 7 66 à 96 8 > 96 10
Pour de l'OLAP, 50 % des cœurs.
On compte les cœurs hyperthreadés (cœurs logiques)
max server memory (MB) : à positionner en fonction de la quantité de RAM, si pas fait au niveau installation, avec le calcul suivant :
optimize for ad hoc workloads à 1
Code : Sélectionner tout - Visualiser dans une fenêtre à part
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24DECLARE @RAM_MACHINE_MB INT = (SELECT physical_memory_kb / 1024 FROM sys.dm_os_sys_info); WITH T AS ( SELECT @RAM_MACHINE_MB AS RAM_MACHINE_MB, CASE WHEN @RAM_MACHINE_MB < 8192 THEN 2048 WHEN @RAM_MACHINE_MB < 16384 THEN 2048 + 1024 * (@RAM_MACHINE_MB - 8192) / 8192 WHEN @RAM_MACHINE_MB < 32768 THEN 3096 + 1024 * (@RAM_MACHINE_MB - 16384) / 16384 WHEN @RAM_MACHINE_MB < 131072 THEN 4096 + 2048 * (@RAM_MACHINE_MB - 32768) / 98304 WHEN @RAM_MACHINE_MB < 1048576 THEN 6144 + 2048 * (@RAM_MACHINE_MB - 131072) / 917504 ELSE 8192 END AS RAM_OS_MB ) SELECT *, RAM_MACHINE_MB - RAM_OS_MB AS RAM_SQL_KB, 'EXEC sp_configure ''max server memory (MB)'', ' + CAST(RAM_MACHINE_MB - RAM_OS_MB AS VARCHAR(32)) + ';' + CHAR(13) + CHAR(10) + 'RECONFIGURE;' FROM T;
Vous pouvez aussi activer :
backup checksum default à 1
backup compression default à 1 si aucune base en chiffrement TDE
traceflag :
Si version antérieure à 2016 activez le (TF 2371)
Si version égale à 2016 sp1 et antérieure à 2019, activez le TF 7412
Si version 2014 SP1 et +, activez le TF 7471
Si version 2014 Sp2 et inférieure à 2016, activez le TF 8079
Vérifiez que vous êtes OK sur le cache en utilisant la requête suivante :
Au minimum à 3600, bien à 15 000, parfait à 30 000 secondes
Code : Sélectionner tout - Visualiser dans une fenêtre à part
2
3
4
Vérifiez, si vous avez plusieurs noeuds NUMA qu'il n'y ait pas un fort déséquilibre (moins de 10 %)
Vérifiez l'état global de votre stockage disques à l'aide de la requête suivante :
Si vous avez des grossissements en pourcentage autres que sur les bases système, remédiez à cela.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17SELECT db.name AS "DATABASE_NAME", mf.name AS LOGICAL_FILE_NAME, mf.type_desc, size / 128.0 AS SIZE_MB, is_percent_growth, volume_mount_point, 100.0 * available_bytes / NULLIF(total_bytes, 0) AS DISK_PERCENT_FREE, io_stall_read_ms / NULLIF(num_of_reads, 0) AS READ_LATENCY_MS, io_stall_write_ms / NULLIF(num_of_writes,0) AS WRITE_LATENCY_MS, SUM(io_stall_read_ms) OVER (PARTITION BY mf.database_id) / NULLIF(SUM(num_of_reads) OVER(PARTITION BY mf.database_id), 0) AS DB_READ_LATENCY_MS, SUM(io_stall_write_ms) OVER (PARTITION BY mf.database_id) / NULLIF(SUM(num_of_writes) OVER(PARTITION BY mf.database_id), 0) AS DB_WRITE_LATENCY_MS, SUM(io_stall_read_ms) OVER (PARTITION BY vs.volume_mount_point) / NULLIF(SUM(num_of_reads) OVER(PARTITION BY vs.volume_mount_point), 0) AS DRIVE_READ_LATENCY_MS, SUM(io_stall_write_ms) OVER (PARTITION BY vs.volume_mount_point) / NULLIF(SUM(num_of_writes) OVER(PARTITION BY vs.volume_mount_point), 0) AS DRIVE_WRITE_LATENCY_MS FROM sys.master_files AS mf JOIN sys.databases AS db ON mf.database_id = db.database_id CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs CROSS APPLY sys.dm_io_virtual_file_stats(mf.database_id, mf.file_id) AS vfs
Si vous avez moins de 10 % de libre sur vos disques, remédiez à cela
Si vos latences disques sont supérieures à :
- 15 ms en écriture
- 8 ms en lecture
Vous avez un problème de stockage. Remédiez-y.
1.4 - Réglages pour tempdb
Vérifiez bien qu'il y ait au moins 1 fichier par paire de cœurs, jusqu'à concurrence de 8 fichiers.
Si version antérieure à 2019, activez les traceflag 1117 et 1118
Dimensionnez correctement vos fichiers de la base de tempdb pour qu'il n'y ait aucune opération de croissance durant l'exploitation ordinaire.
1.5 - Réglages par base
Quelques-uns des réglages au niveau ALTER DATABASE SCOPED CONFIGUATION peuvent être appliqués en fonction de la nature de la base de données. Notamment ceux jouant sur l'optimisation et en particulier l'estimateur de cardinalité.
2 - Campagne d'indexation
Deux méthodes :
2.1 - la méthode brute
Consiste à créer entre 20 et 60 % des index manquants (global au serveur) en prenant ceux ayant le plus de gains (pas ma méthode favorite, car surindex les grosses tables au détriment des petites...). Requête pour se faire :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16SELECT ROW_NUMBER() OVER(ORDER BY migs.avg_total_user_cost * migs.avg_user_impact DESC) AS RN, 100.0 * ROW_NUMBER() OVER(ORDER BY migs.avg_total_user_cost * migs.avg_user_impact DESC) / COUNT(*) OVER() AS PERCENT_CREATED, N'CREATE INDEX X_' + REPLACE(CAST(NEWID() AS NVARCHAR(36)), N'-', N'_') + N'_' + CONVERT(CHAR(8), GETDATE(), 112) + N' ON ' + statement + N' (' + COALESCE(equality_columns + N', ' + inequality_columns, equality_columns, inequality_columns) + N') ' + CASE WHEN included_columns IS NULL THEN N'' ELSE N' INCLUDE(' + included_columns + ') ' END + N';' AS SQL_CREATE_INDEX, migs.avg_total_user_cost * migs.avg_user_impact AS INDICE 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 migs.group_handle = mig.index_group_handle ORDER BY RN
2.2 - la méthode intelligente
Plus intelligent : auditer chaque index à créer en évitant les inclusions et pseudo redondances à l'aide du processus suivant :
http://mssqlserver.fr/aide-au-diagno...ms-sql-server/
2.3 - Supprimer les index inutiles
Utiliez la requête suivante (par base)
2.4 - supprimez les index redondants ou inclus
Code : Sélectionner tout - Visualiser dans une fenêtre à part
2
3
4
5
6
7
8
9
10
11
12
13
14
15SELECT s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME, o.type_desc, i.name AS INDEX_NAME, ius.user_updates 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 database_id = DB_ID() AND ius.user_seeks = 0 AND ius.user_lookups = 0 AND ius.user_scans = 0 AND i.is_hypothetical = 0 AND i.is_primary_key = 0 AND i.is_unique = 0 AND i.is_unique_constraint = 0 ORDER BY ius.user_updates;
Aidez-vous de la requête suivante pour choisir les index à supprimer (par base) :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97WITH TK AS ( -- clés SELECT o.object_id, i.index_id, i.type_desc, i.filter_definition, STUFF((SELECT ', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END FROM sys.index_columns AS ic INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal > 0 AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH('')), 1, 2, '') AS KEY_LIST, STUFF((SELECT ', ~' + CAST(ic.column_id AS VARCHAR(32)) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END + '~' FROM sys.index_columns AS ic WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal > 0 AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH('')), 1, 2, '') AS KEY_ID_LIST FROM sys.indexes AS i JOIN sys.objects AS o ON i.object_id = o.object_id WHERE o.is_ms_shipped = 0 AND index_id > 0 ), TI AS ( -- colonnes incluses SELECT o.object_id, i.index_id, STUFF((SELECT ', ' + c.name FROM sys.index_columns AS ic INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal = 0 AND ic.is_included_column = 1 ORDER BY c.name FOR XML PATH('')), 1, 2, '') AS INC_LIST FROM sys.indexes AS i JOIN sys.objects AS o ON i.object_id = o.object_id WHERE o.is_ms_shipped = 0 AND index_id > 0 AND EXISTS(SELECT * FROM sys.index_columns AS ic WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1) ), TS AS ( -- synthèses SELECT TK.object_id, TK.index_id, TK.type_desc, TK.filter_definition, TK.KEY_LIST, TK.KEY_ID_LIST, TI.INC_LIST, 1 + LEN(KEY_ID_LIST) - LEN(REPLACE(KEY_ID_LIST, ',', '')) AS COLS FROM TK LEFT OUTER JOIN TI ON TK.object_id = TI.object_id AND TI.index_id = TK.index_id ), TX AS ( -- comparaisons SELECT T2.object_id, T2.index_id, T3.index_id AS index_id_anomalie, T2.KEY_LIST AS CLEF_INDEX, T3.KEY_LIST AS CLEF_INDEX_ANORMAL, T2.INC_LIST, T3.INC_LIST AS COLONNES_INCLUSES_ANORMAL, CASE WHEN T2.KEY_ID_LIST = T3.KEY_ID_LIST THEN 'DOUBLONS' WHEN T2.KEY_ID_LIST LIKE T3.KEY_ID_LIST +'%' THEN 'INCLUS' END AS ANOMALIE, ABS(T2.COLS - T3.COLS) AS DISTANCE FROM TS AS T2 INNER JOIN TS AS T3 ON T2.object_id = T3.object_id AND T2.index_id <> T3.index_id AND T2.KEY_ID_LIST LIKE T3.KEY_ID_LIST +'%' ) -- résultat final SELECT TX.*, s.name +'.' + o.name AS NOM_TABLE, i1.name AS NOM_INDEX, i2.name AS NOM_INDEX_ANORMAL , i1.filter_definition AS FILTRE_INDEX , i2.filter_definition AS FILTRE_INDEX_ANORMAL FROM TX INNER JOIN sys.objects AS o ON TX.object_id = o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id INNER JOIN sys.indexes AS i1 ON TX.object_id = i1.object_id AND TX.index_id = i1.index_id INNER JOIN sys.indexes AS i2 ON TX.object_id = i2.object_id AND TX.index_id_anomalie = i2.index_id WHERE o."type" IN ('U', 'V') ORDER BY NOM_TABLE, NOM_INDEX;
2.5 - supprimez les statistiques inutiles
Utilisez la requête suivante (par base)
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
45WITH T0 AS ( SELECT s.object_id, s.name AS STAT_NAME, i.name AS INDEX_NAME, CASE WHEN i.is_primary_key = 1 THEN 'PRIMAY KEY' WHEN i.is_unique_constraint = 1 THEN 'UNIQUE' END AS CONSTRAINT_TYPE, i.has_filter AS HAS_FILTER, (SELECT TOP 1 c.name FROM sys.stats_columns AS sc JOIN sys.columns AS c ON sc.object_id = c.object_id AND sc.column_id = c.column_id WHERE s.object_id = sc.object_id AND s.stats_id = sc.stats_id ORDER BY sc.stats_column_id) AS STAT_COLUMN, STUFF((SELECT ', ' + c.name FROM sys.stats_columns AS sc JOIN sys.columns AS c ON sc.object_id = c.object_id AND sc.column_id = c.column_id WHERE s.object_id = sc.object_id AND s.stats_id = sc.stats_id ORDER BY sc.stats_column_id FOR XML PATH('')), 1, 1, '') AS KEY_COLS FROM sys.stats AS s LEFT OUTER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.name = i.name ) SELECT A.*, B.STAT_NAME AS STAT_NAME2, B.INDEX_NAME AS INDEX_NAME2, B.CONSTRAINT_TYPE AS CONSTRAINT_TYPE2, B.HAS_FILTER AS HAS_FILTER2, B.KEY_COLS AS KEY_COLS2, N'DROP STATISTICS [' + s.name + N'].[' + o.name + N'].[' + B.STAT_NAME + N'];' AS DROP_STAT FROM T0 AS A JOIN T0 AS B ON A.STAT_COLUMN = B.STAT_COLUMN AND A.STAT_NAME > B.STAT_NAME AND A.object_id = B.object_id JOIN sys.objects AS o ON A.object_id = o.object_id JOIN sys.schemas AS s ON o.schema_id = s.schema_id WHERE B.INDEX_NAME IS NULL AND o."type" IN ('V', 'U');
ATTENTION !
Pour tous ces diagnostics, soyez sûr que l'instance n'a pas redémarré depuis au moins 31 jours, à l'aide de la requête :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
2
3
4
5
6
7
8
9DECLARE @SQL_START DATETIME = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info); DECLARE @SQL_START_STR CHAR(21) = CONVERT(CHAR(19), @SQL_START, 121); IF @SQL_START > DATEADD(day, -31, GETDATE()) BEGIN RAISERROR ('The server restarted on %s which is insufficient for a good diagnosis. Try again in a few days', 16, 1, @SQL_START_STR); RETURN; END;
3 - objets lents
Après toutes ces mises en oeuvre et passé un délai de 31 jours, utilisez les requêtes suivantes qui extraient les 25 objets les plus pourris au niveau perf dans chacune des 4 catégories suivantes :
Enfin, s'il vous reste quelque chose à moudre, utilisez le profiler puis les extended events !
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62DECLARE @TOP INT = 25; WITH Q AS ( SELECT TOP(@TOP) 'QUERY' AS CATEGORY, SUBSTRING(st.text, 1 + s.statement_start_offset/2, ((CASE s.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE s.statement_end_offset END - s.statement_start_offset)/2) + 1) AS SQL_STATEMENT, s.execution_count, s.total_worker_time, s.max_elapsed_time, s.min_elapsed_time, CAST(ROUND(1.0 * s.total_worker_time / s.execution_count, 3) AS DECIMAL(32, 3)) AS AVG_EXEC_TIME FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) ORDER BY total_worker_time DESC ), P AS ( SELECT TOP(@TOP) 'PROCEDURE' AS CATEGORY, st.text AS SQL_STATEMENT, s.execution_count, s.total_worker_time, s.max_elapsed_time, s.min_elapsed_time, CAST(ROUND(1.0 * s.total_worker_time / s.execution_count, 3) AS DECIMAL(32, 3)) AS AVG_EXEC_TIME FROM sys.dm_exec_procedure_stats AS s CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) ORDER BY total_worker_time DESC ), T AS ( SELECT TOP(@TOP) 'TRIGGER' AS CATEGORY, st.text AS SQL_STATEMENT, s.execution_count, s.total_worker_time, s.max_elapsed_time, s.min_elapsed_time, CAST(ROUND(1.0 * s.total_worker_time / s.execution_count, 3) AS DECIMAL(32, 3)) AS AVG_EXEC_TIME FROM sys.dm_exec_trigger_stats AS s CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) ORDER BY total_worker_time DESC ), F AS (SELECT TOP(@TOP) 'PROCEDURE' AS CATEGORY, st.text AS SQL_STATEMENT, s.execution_count, s.total_worker_time, s.max_elapsed_time, s.min_elapsed_time, CAST(ROUND(1.0 * s.total_worker_time / s.execution_count, 3) AS DECIMAL(32, 3)) AS AVG_EXEC_TIME FROM sys.dm_exec_function_stats AS s CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(plan_handle) ORDER BY total_worker_time DESC ) SELECT * FROM Q UNION ALL SELECT * FROM P UNION ALL SELECT * FROM T UNION ALL SELECT * FROM F ORDER BY total_worker_time
Consulter le profil
Marquer ce blog comme lu
