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 |
Partager