Audit global des performances d'un serveur MS SQL Server

Un billet blog de Frédéric BROUARD (SQLpro)

1
2
3
4
5
6
7
8
9
10
Nombre 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DECLARE @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;

1
2
3
4
SELECT instance_name AS NUMA_NODE, cntr_value AS PAGE_LIFE_EXPECTANSY
FROM   sys.dm_os_performance_counters
WHERE  object_name LIKE N'%Buffer Node%'
  AND  counter_name = N'Page life expectancy'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 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;

1
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
97
WITH 
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;

1
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
WITH 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');

1
2
3
4
5
6
7
8
9
DECLARE @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;

1
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
DECLARE @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