SQL_scripts

1
set nocount, xact_abort on
2
set tran isolation level read uncommitted
3

4
declare @schema_table sysname = 'dbo.table'
5

6
drop table if exists #Buffs;
7
create table #Buffs (
8
    db_id int not null
9
    ,allocation_unit_id bigint not null
10
    ,size decimal(12, 3) not null
11
    ,primary key ( db_id, allocation_unit_id )
12
);
13

14
drop table if exists #OpStats;
15
create table #OpStats (
16
	database_id smallint not null
17
	,[object_id] int not null
18
	,index_id int not null
19
	,range_scan_count bigint null
20
	,singleton_lookup_count bigint null
21
	,forwarded_fetch_count bigint null
22
	,lob_fetch_in_pages bigint null
23
	,row_overflow_fetch_in_pages bigint null
24
	,leaf_insert_count bigint null
25
	,leaf_update_count bigint null
26
	,leaf_delete_count bigint null
27
	,leaf_ghost_count bigint null
28
	,nonleaf_insert_count bigint null
29
	,nonleaf_update_count bigint null
30
	,nonleaf_delete_count bigint null
31
	,leaf_allocation_count bigint null
32
	,nonleaf_allocation_count bigint null
33
	,row_lock_count bigint null
34
	,row_lock_wait_count bigint null
35
	,row_lock_wait_in_ms bigint null
36
	,page_lock_count bigint null
37
	,page_lock_wait_count bigint null
38
	,page_lock_wait_in_ms bigint null
39
	,index_lock_promotion_attempt_count bigint null
40
	,index_lock_promotion_count bigint null
41
	,page_latch_wait_count bigint null
42
	,page_latch_wait_in_ms bigint null
43
	,tree_page_latch_wait_count bigint null
44
	,tree_page_latch_wait_in_ms bigint null
45
	,page_io_latch_wait_count bigint null
46
	,page_io_latch_wait_in_ms bigint null
47
	,page_compression_attempt_count bigint null
48
	,page_compression_success_count bigint null
49
	,primary key ( database_id, [object_id], index_id )
50
)
51

52
insert into #Buffs (db_id, allocation_unit_id, size)
53
select
54
	database_id
55
	,allocation_unit_id
56
	,convert(decimal(12, 3), count(*) / 128.0)
57
from sys.dm_os_buffer_descriptors with ( nolock )
58
group by
59
	database_id
60
	,allocation_unit_id
61
option (maxdop 1)
62

63
insert into #OpStats (
64
	database_id
65
	,[object_id]
66
	,index_id
67
	,range_scan_count
68
	,singleton_lookup_count
69
	,forwarded_fetch_count
70
	,lob_fetch_in_pages
71
	,row_overflow_fetch_in_pages
72
	,leaf_insert_count
73
	,leaf_update_count
74
	,leaf_delete_count
75
	,leaf_ghost_count
76
	,nonleaf_insert_count
77
	,nonleaf_update_count
78
	,nonleaf_delete_count
79
	,leaf_allocation_count
80
	,nonleaf_allocation_count
81
	,row_lock_count
82
	,row_lock_wait_count
83
	,row_lock_wait_in_ms
84
	,page_lock_count
85
	,page_lock_wait_count
86
	,page_lock_wait_in_ms
87
	,index_lock_promotion_attempt_count
88
	,index_lock_promotion_count
89
	,page_latch_wait_count
90
	,page_latch_wait_in_ms
91
	,tree_page_latch_wait_count
92
	,tree_page_latch_wait_in_ms
93
	,page_io_latch_wait_count
94
	,page_io_latch_wait_in_ms
95
	,page_compression_attempt_count
96
	,page_compression_success_count
97
)
98
select
99
	os.database_id
100
	,os.[object_id]
101
	,os.index_id
102
	,sum(os.range_scan_count) as range_scan_count
103
	,sum(os.singleton_lookup_count) as singleton_lookup_count
104
	,sum(os.forwarded_fetch_count) as forwarded_fetch_count
105
	,sum(os.lob_fetch_in_pages) as lob_fetch_in_pages
106
	,sum(os.row_overflow_fetch_in_pages) as row_overflow_fetch_in_pages
107
	,sum(os.leaf_insert_count) as leaf_insert_count
108
	,sum(os.leaf_update_count) as leaf_update_count
109
	,sum(os.leaf_delete_count) as leaf_delete_count
110
	,sum(os.leaf_ghost_count) as leaf_ghost_count
111
	,sum(os.nonleaf_insert_count) as nonleaf_insert_count
112
	,sum(os.nonleaf_update_count) as nonleaf_update_count
113
	,sum(os.nonleaf_delete_count) as nonleaf_delete_count
114
	,sum(os.leaf_allocation_count) as leaf_allocation_count
115
	,sum(os.nonleaf_allocation_count) as nonleaf_allocation_count
116
	,sum(os.row_lock_count) as row_lock_count
117
	,sum(os.row_lock_wait_count) as row_lock_wait_count
118
	,sum(os.row_lock_wait_in_ms) as row_lock_wait_in_ms
119
	,sum(os.page_lock_count) as page_lock_count
120
	,sum(os.page_lock_wait_count) as page_lock_wait_count
121
	,sum(os.page_lock_wait_in_ms) as page_lock_wait_in_ms
122
	,sum(os.index_lock_promotion_attempt_count) as index_lock_promotion_attempt_count
123
	,sum(os.index_lock_promotion_count) as index_lock_promotion_count
124
	,sum(os.page_latch_wait_count) as page_latch_wait_count
125
	,sum(os.page_latch_wait_in_ms) as page_latch_wait_in_ms
126
	,sum(os.tree_page_latch_wait_count) as tree_page_latch_wait_count
127
	,sum(os.tree_page_latch_wait_in_ms) as tree_page_latch_wait_in_ms
128
	,sum(os.page_io_latch_wait_count) as page_io_latch_wait_count
129
	,sum(os.page_io_latch_wait_in_ms) as page_io_latch_wait_in_ms
130
	,sum(os.page_compression_attempt_count) as page_compression_attempt_count
131
	,sum(os.page_compression_success_count) as page_compression_success_count
132
from sys.dm_db_index_operational_stats(null, null, null, 0) as os
133
where os.database_id = db_id()
134
group by
135
	os.database_id
136
    ,os.object_id
137
    ,os.index_id
138
option (maxdop 1)
139

140
;with TableInfo as (
141
	select
142
		t.[object_id]
143
		,i.index_id
144
		,sch.[name] + '.' + t.[name] as [table]
145
		,quotename(i.[name]) as [index]
146
		,i.[type_desc] as [type]
147
		,sum(p.[rows]) as [rows]
148
		,i.is_unique 
149
		,i.fill_factor
150
		,i.is_disabled
151
		,i.filter_definition as [filter]
152
		,t.lock_escalation_desc as [lock_escalation]
153
		,case max(p.data_compression)
154
			when 0 then 'NONE'
155
			when 1 then 'ROW'
156
			when 2 then 'PAGE'
157
			when 3 then 'COLUMNSTORE'
158
			when 4 then 'COLUMNSTORE_ARCHIVE'
159
				end as [max_compression]
160
		,sum(a.total_pages) as [total_pages]
161
		,sum(a.used_pages) as [used_pages]
162
		,sum(a.data_pages) as [data_pages]
163
		,convert(decimal(12,3),sum(a.total_pages) * 8. / 1024.) as [total_space_mb]
164
		,convert(decimal(12,3),sum(a.used_pages) * 8. / 1024.) as [used_space_mb]
165
		,convert(decimal(12,3),sum(a.data_pages) * 8. / 1024.) as [data_space_mb]
166
		,sum(bi.size) as [buffer_pool_space_mb]
167
	from
168
		sys.tables as t
169
		inner join sys.indexes as i
170
			on t.[object_id] = i.[object_id]
171
		inner join sys.partitions as p
172
			on i.[object_id] = p.[object_id]
173
			and i.index_id = p.index_id
174
		inner join sys.allocation_units as a
175
			on p.[partition_id] = a.container_id
176
		inner join sys.schemas as sch
177
			on t.[schema_id] = sch.[schema_id]
178
		left join #Buffs as bi
179
			on bi.[db_id] = db_id()
180
			and a.allocation_unit_id = bi.allocation_unit_id 
181
	where i.[object_id] > 255 
182
	group by
183
		sch.[name]
184
		,t.[name]
185
		,i.[type_desc]
186
		,i.[object_id]
187
		,i.index_id
188
		,i.[name]
189
		,i.is_unique
190
		,i.fill_factor
191
		,t.lock_escalation_desc
192
		,t.[object_id]
193
		,i.index_id
194
		,i.filter_definition
195
		,i.is_disabled
196
)
197
select 
198
	ti.[object_id]
199
	,ti.index_id
200
	,ti.[table]
201
	,ti.[index]
202
	,iif(ic.is_guid = 1, 'yes', 'no') as is_guid
203
	,ti.[type]
204
	,left(idx_def.key_col, len(idx_def.key_col) - 1) as [key_columns]
205
	,left(idx_def.included_col, len(idx_def.included_col) - 1) as [included_columns]
206
	,ti.[filter]
207
	,idx_len.max_key_length
208
	,ti.[rows]
209
	,ti.is_unique
210
	,ti.is_disabled
211
	,ti.[lock_escalation]
212
	,ti.max_compression
213
	,ti.total_pages
214
	,ti.used_pages
215
	,ti.data_pages
216
	,ti.total_space_mb
217
	,ti.used_space_mb
218
	,ti.data_space_mb
219
	,ti.buffer_pool_space_mb
220
	,stats_date(ti.[object_id], ti.index_id) as [stats_date]
221
	,ius.user_seeks
222
	,ius.user_scans
223
	,ius.user_lookups
224
	,ius.user_seeks + ius.user_scans + ius.user_lookups AS [user_reads]
225
	,ius.user_updates 
226
	,ius.last_user_seek
227
	,ius.last_user_scan
228
	,ius.last_user_lookup
229
	,ius.last_user_update
230
	,ius.profit
231
	,ios.range_scan_count
232
	,ios.singleton_lookup_count
233
	,ios.forwarded_fetch_count
234
	,ios.lob_fetch_in_pages
235
	,ios.row_overflow_fetch_in_pages
236
	,ios.leaf_insert_count
237
	,ios.leaf_update_count
238
	,ios.leaf_delete_count
239
	,ios.leaf_ghost_count
240
	,ios.nonleaf_insert_count
241
	,ios.nonleaf_update_count
242
	,ios.nonleaf_delete_count
243
	,ios.leaf_allocation_count
244
	,ios.nonleaf_allocation_count
245
	,ios.row_lock_count
246
	,ios.row_lock_wait_count
247
	,ios.row_lock_wait_in_ms
248
	,ios.page_lock_count
249
	,ios.page_lock_wait_count
250
	,ios.page_lock_wait_in_ms
251
	,ios.index_lock_promotion_attempt_count
252
	,ios.index_lock_promotion_count
253
	,ios.page_latch_wait_count
254
	,ios.page_latch_wait_in_ms
255
	,ios.tree_page_latch_wait_count
256
	,ios.tree_page_latch_wait_in_ms
257
	,ios.page_io_latch_wait_count
258
	,ios.page_io_latch_wait_in_ms
259
	,ios.page_compression_attempt_count
260
	,ios.page_compression_success_count
261
from
262
	TableInfo as ti
263
	left join #OpStats as ios
264
		on ti.[object_id] = ios.[object_id]
265
		and ti.index_id = ios.index_id
266
		and ios.database_id = db_id()
267
	outer apply (
268
		select 
269
			ius.user_seeks
270
			,ius.user_scans
271
			,ius.user_lookups
272
			,ius.user_updates 
273
			,ius.last_user_seek
274
			,ius.last_user_scan
275
			,ius.last_user_lookup
276
			,ius.last_user_update
277
			,10000 * ius.user_seeks / (ius.user_updates + 1) as profit
278
		from sys.dm_db_index_usage_stats as ius
279
		where
280
			ius.database_id = db_id()
281
			and ius.[object_id] = ti.[object_id]
282
			and ius.index_id = ti.index_id
283
		) as ius
284
	--outer apply (
285
	--			select sum(ps.[used_page_count]) * 8 / 1024 as IDX_Size_MB
286
	--			from sys.dm_db_partition_stats as ps
287
	--			where
288
	--				ps.[object_id] = ti.[object_id]
289
	--				and ps.index_id = ti.index_id
290
	--) as isz
291
	outer apply (
292
		select top (1) 1 as is_guid
293
		from
294
			sys.index_columns as ic
295
			inner join sys.columns as c
296
				on ic.[object_id] = c.[object_id]
297
				and	ic.column_id = c.column_id
298
		where
299
			ic.[object_id] = ti.[object_id]
300
			and ic.index_id = ti.index_id
301
			and c.system_type_id = 36
302
	) as ic
303
	outer apply (
304
		select
305
			(	select 
306
					quotename(col.[name]) as [text()]
307
					,iif(icol_meta.is_descending_key = 1, ' DESC','') as [text()]
308
					,',' as [text()]
309
				from
310
					sys.index_columns as icol_meta
311
					inner join sys.columns as col
312
						on icol_meta.[object_id] = col.[object_id]
313
						and icol_meta.column_id = col.column_id
314
				where
315
					icol_meta.[object_id] = ti.[object_id]
316
					and icol_meta.index_id = ti.index_id
317
					and icol_meta.is_included_column = 0
318
				order by icol_meta.key_ordinal
319
				for xml path('')
320
			) as key_col
321
			,(	select
322
					quotename(col.[name]) as [text()]
323
					,',' as [text()]
324
				from
325
					sys.index_columns as icol_meta
326
					inner join sys.columns as col
327
						on icol_meta.[object_id] = col.[object_id]
328
						and icol_meta.column_id = col.column_id
329
				where
330
					icol_meta.[object_id] = ti.[object_id]
331
					and icol_meta.index_id = ti.index_id
332
					and icol_meta.is_included_column = 1
333
				order by col.[name]
334
				for xml path('')
335
			) as included_col
336
	) as idx_def
337
	outer apply (
338
		select sum(c.max_length) as max_key_length
339
		from
340
			sys.index_columns as ic
341
			inner join sys.columns as c
342
				on ic.[object_id] = c.[object_id]
343
				and ic.column_id = c.column_id
344
		where
345
			ic.[object_id] = ti.[object_id]
346
			and ic.index_id = ti.index_id
347
			and ic.is_included_column = 0
348
	) as idx_len
349
where ti.[table] = replace(replace(@schema_table, '[', ''), ']', '')
350
option (recompile, maxdop 1)
351

352