-
Notifications
You must be signed in to change notification settings - Fork 23
/
IndexUsage.sql
166 lines (158 loc) · 5.74 KB
/
IndexUsage.sql
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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
/*
ServerName DBName ExtraIndexName
STGSQL613 CommunityFirstCUSMC ix_appraisal_ln_loan_id
STGSQL613 CommunityFirstCUSMC IX_ln_loan_id_ald_name
STGSQL613 CommunityFirstCUSMC ix_loan_regulatory_ln_loan_id
PSQLSMC30 FinancialPrtCUSMC ix_appraisal_ln_loan_id
STGSQL614 FinancialPrtCUSMC ix_appraisal_ln_loan_id
PSQLSMC30 FinancialPrtCUSMC IX_ln_loan_id_ald_name
STGSQL614 FinancialPrtCUSMC IX_ln_loan_id_ald_name
PSQLSMC30 FinancialPrtCUSMC ix_loan_regulatory_ln_loan_id
STGSQL614 FinancialPrtCUSMC ix_loan_regulatory_ln_loan_id
STGSQL614 NumericaSMC IX_ln_loan_id_ald_name
STGSQL614 NumericaSMC ix_loan_regulatory_ln_loan_id
QSQL610 PAQALegacySMC IX_InstitutionAssociation_IaInst_IaParentInst
*/
SELECT @@SERVERNAME + DB_NAME() AS Location
, OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME]
, I.[NAME] AS [INDEX NAME]
, A.*
, A.LEAF_INSERT_COUNT
, A.LEAF_UPDATE_COUNT
, A.LEAF_DELETE_COUNT
, A.LEAF_GHOST_COUNT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = A.[OBJECT_ID]
AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
--AND I.[NAME] = 'ix_appraisal_ln_loan_id'
AND I.[NAME] IN ('ix_appraisal_ln_loan_id'
, 'IX_ln_loan_id_ald_name'
, 'ix_loan_regulatory_ln_loan_id'
, 'IX_InstitutionAssociation_IaInst_IaParentInst'
)
SELECT DB_NAME() AS DBName
, OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]
, I.[NAME] AS [INDEX NAME]
, A.LEAF_INSERT_COUNT
, A.LEAF_UPDATE_COUNT
, A.LEAF_DELETE_COUNT
, A.LEAF_GHOST_COUNT
, S.USER_SEEKS
, S.USER_SCANS
, S.USER_LOOKUPS
, S.USER_UPDATES
FROM SYS.INDEXES AS I
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS AS S
ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
INNER JOIN SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
AND I.[NAME] IN ('ix_appraisal_ln_loan_id'
, 'IX_ln_loan_id_ald_name'
, 'ix_loan_regulatory_ln_loan_id'
, 'IX_InstitutionAssociation_IaInst_IaParentInst'
)
---------------------------------------------------------------------
EXEC sp_MSforeachdb '
USE ?
IF (''?'' LIKE ''%SMC'')
BEGIN
SELECT DB_NAME() AS DBName
, OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]
, I.[NAME] AS [INDEX NAME]
, A.LEAF_INSERT_COUNT
, A.LEAF_UPDATE_COUNT
, A.LEAF_DELETE_COUNT
, A.LEAF_GHOST_COUNT
, S.USER_SEEKS
, S.USER_SCANS
, S.USER_LOOKUPS
, S.USER_UPDATES
FROM SYS.INDEXES AS I
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS AS S
ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
INNER JOIN SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],''IsUserTable'') = 1
AND I.[NAME] IN (''ix_appraisal_ln_loan_id''
, ''IX_ln_loan_id_ald_name''
, ''ix_loan_regulatory_ln_loan_id''
, ''IX_InstitutionAssociation_IaInst_IaParentInst''
)
END
'
---------------------------------------------------------------------
/*
Usage statistics for indexes
*/
DECLARE @IndexResults TABLE (
[Server Name] varchar(128)
, DBName varchar(128)
, [OBJECT NAME] SYSNAME
, [INDEX NAME] SYSNAME NULL
, LEAF_INSERT_COUNT int -- Cumulative count of leaf-level inserts.
, LEAF_UPDATE_COUNT int -- Cumulative count of leaf-level updates.
, LEAF_DELETE_COUNT int -- Cumulative count of leaf-level deletes.
, LEAF_GHOST_COUNT int -- Cumulative count of leaf-level rows that are marked as deleted, but not yet removed.
, RANGE_SCAN_COUNT int -- Cumulative count of range and table scans started on the index or heap.
, SINGLETON_LOOKUP_COUNT int -- Cumulative count of single row retrievals from the index or heap.
, USER_SEEKS int -- Number of seeks by user queries.
, USER_SCANS int -- Number of scans by user queries.
, USER_LOOKUPS int -- Number of bookmark lookups by user queries.
, USER_UPDATES int -- Number of updates by user queries.
, SYSTEM_SEEKS int -- Number of seeks by system queries.
, SYSTEM_SCANS int -- Number of scans by system queries.
, SYSTEM_LOOKUPS int -- Number of lookups by system queries.
, SYSTEM_UPDATES int -- Number of updates by system queries.
, LAST_USER_SEEK DATETIME -- Time of last user seek.
, LAST_USER_SCAN DATETIME -- Time of last user scan.
, LAST_USER_LOOKUP DATETIME -- Time of last user lookup.
, LAST_USER_UPDATE DATETIME -- Time of last user update.
)
insert into @IndexResults
EXEC sp_MSforeachdb '
USE ?
IF (''?'' LIKE ''%SMC'')
BEGIN
IF (''?'' NOT IN (''master'', ''msdb'', ''dbamaint'', ''tempdb'', ''distribution'') )
BEGIN
SELECT @@SERVERNAME as [Server Name]
, DB_NAME() AS DBName
, OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]
, I.[NAME] AS [INDEX NAME]
, A.LEAF_INSERT_COUNT
, A.LEAF_UPDATE_COUNT
, A.LEAF_DELETE_COUNT
, A.LEAF_GHOST_COUNT
, A.RANGE_SCAN_COUNT
, A.SINGLETON_LOOKUP_COUNT
, S.USER_SEEKS
, S.USER_SCANS
, S.USER_LOOKUPS
, S.USER_UPDATES
, S.SYSTEM_SEEKS
, S.SYSTEM_SCANS
, S.SYSTEM_LOOKUPS
, S.SYSTEM_UPDATES
, S.LAST_USER_SEEK
, S.LAST_USER_SCAN
, S.LAST_USER_LOOKUP
, S.LAST_USER_UPDATE
FROM SYS.INDEXES AS I
INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS AS S
ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
INNER JOIN SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],''IsUserTable'') = 1
--AND I.[NAME] IN (''ix_appraisal_ln_loan_id''
-- , ''IX_ln_loan_id_ald_name''
-- , ''ix_loan_regulatory_ln_loan_id''
-- , ''IX_InstitutionAssociation_IaInst_IaParentInst''
-- )
END
END
'
select * from @IndexResults
---------------------------------------------------------------------