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
| SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DECLARE @schema_name sysname = 'dbo'
, @table_or_view_name sysname --= 'maTable' -- si NULL, étudie tous les index de toutes les table du schéma
, @index_name sysname = NULL -- si NULL, étudie tous les index de la table
, @separator varchar(2) = ', '
----------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT S.name + '.' + O.name AS schema_table_name
, I.name AS index_name
, PS.row_count
, I.type_desc
, LEFT(KC.key_column_list, LEN(KC.key_column_list) - 2) AS key_column_list
, LEFT(KCI.included_key_column_list, LEN(KCI.included_key_column_list) - 1) AS included_key_column_list
, REPLACE(REPLACE(REPLACE(REPLACE(I.filter_definition, '[', ''), ']', ''), '(', ''), ')', '') AS filter_definition
, PS.used_page_count AS pages_count
, STATS_DATE(O.object_id, I.index_id) AS last_stat_update
, IUS.user_seeks
, IUS.user_scans
, IUS.user_lookups
, (IUS.user_seeks + IUS.user_scans + IUS.user_lookups) AS total_user_searches
, IUS.last_user_seek
, IUS.last_user_scan
, IUS.last_user_lookup
, LS.max_last_search_date
, PS.used_page_count / 8 AS index_size_kb
FROM sys.schemas AS S
INNER JOIN sys.objects AS O
ON S.schema_id = O.schema_id
INNER JOIN sys.indexes AS I
ON O.object_id = I.object_id
INNER JOIN sys.index_columns AS IC
ON IC.object_id = I.object_id
AND IC.index_id = I.index_id
INNER JOIN sys.columns AS C
ON IC.object_id = C.object_id
AND IC.column_id = C.column_id
LEFT JOIN sys.dm_db_index_usage_stats AS IUS
ON IUS.object_id = I.object_id
AND IUS.index_id = I.index_id
AND IUS.database_id = DB_ID()
INNER JOIN sys.dm_db_partition_stats AS PS
ON PS.object_id = I.object_id
AND PS.index_id = I.index_id
CROSS APPLY (
SELECT CS.name + @separator
FROM sys.columns AS CS
INNER JOIN sys.index_columns AS ICS
ON CS.object_id = ICS.object_id
AND CS.column_id = ICS.column_id
WHERE IC.object_id = ICS.object_id
AND IC.index_id = ICS.index_id
AND ICS.is_included_column = 0
ORDER BY ICS.index_column_id
FOR XML PATH ('')
) AS KC (key_column_list)
OUTER APPLY (
SELECT CSI.name + @separator
FROM sys.columns AS CSI
INNER JOIN sys.index_columns AS ICSI
ON CSI.object_id = ICSI.object_id
AND CSI.column_id = ICSI.column_id
WHERE IC.object_id = ICSI.object_id
AND IC.index_id = ICSI.index_id
AND ICSI.is_included_column = 1
ORDER BY ICSI.index_column_id
FOR XML PATH ('')
) AS KCI (included_key_column_list)
OUTER APPLY (
SELECT MAX(M.last_search_date)
FROM (
SELECT LU_USK.last_user_seek
FROM sys.dm_db_index_usage_stats AS LU_USK
WHERE IUS.database_id = LU_USK.database_id
AND IUS.object_id = LU_USK.object_id
AND IUS.index_id = LU_USK.index_id
UNION ALL
SELECT LU_USC.last_user_scan
FROM sys.dm_db_index_usage_stats AS LU_USC
WHERE IUS.database_id = LU_USC.database_id
AND IUS.object_id = LU_USC.object_id
AND IUS.index_id = LU_USC.index_id
UNION ALL
SELECT LU_LK.last_user_lookup
FROM sys.dm_db_index_usage_stats AS LU_LK
WHERE IUS.database_id = LU_LK.database_id
AND IUS.object_id = LU_LK.object_id
AND IUS.index_id = LU_LK.index_id
) AS M (last_search_date)
) AS LS (max_last_search_date)
WHERE (@schema_name IS NULL OR S.name = @schema_name)
AND (@table_or_view_name IS NULL OR O.name = @table_or_view_name)
AND (@index_name IS NULL OR I.name = @index_name)
ORDER BY I.type_desc |
