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; |
Extended events : requêtes les plus longues; vos bonnes pratiques?
Répondre avec citation
Envoyé par Ikebukuro
Partager