-
Notifications
You must be signed in to change notification settings - Fork 681
/
dbo.sp_IndexAnalysis-v11.sql
916 lines (867 loc) · 42.2 KB
/
dbo.sp_IndexAnalysis-v11.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
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
USE master
GO
IF OBJECT_ID('dbo.sp_IndexAnalysis') IS NOT NULL
DROP PROCEDURE dbo.sp_IndexAnalysis
GO
/*********************************************************************************************
Index Analysis Script v11.06
(C) 2013, Jason Strate
Feedback:
mailto:[email protected]
http://www.jasonstrate.com
License:
This query is free to download and use for personal, educational, and internal
corporate purposes, provided that this header is preserved. Redistribution or sale
of this query, in whole or in part, is prohibited without the author's express
written consent.
Note:
Recommendations in the Index Action column are not black and white recommendations.
They are more light grey ideas of what may be appropriate. Always use your experience
with the database in place of a blanket recommendation.
The information in the DMVs is gathered from when the SQL Server service last started, the
database was brought onling, or when the index metadata was updated (such as during reindex
operations, which ever event is more recent.
The index statistics accumulated in sys.dm_db_index_operational_stats and
sys.dm_db_index_usage_stats are reset when the index is rebuilt.
The index statistics for a table that are accumulated in the DMVs
sys.dm_db_missing_index_* are reset whenever an index is created on the table.
The index name provided in the name column for indexes that do not exist is not a
recommended name for the index. It’s just an informative placeholder.
Parameters
@TableName(NVARCHAR(256)): Optional parameter to add
@IncludeMissingIndexes(BIT): Identifies whether to include missing indexes in the output
@IncludeMissingFKIndexes(BIT): Identifies whether to include missing foreign key indexes
in the output
@Output(VARCHAR(20)): Determines the output results from the stored procedure. The
available values are
* DETAILED: All results from the index analysis
* DUPLICATE: Index results for deplicate indexes
* OVERLAPPING: Index results for overlapping indexes
Columns:
index_action: Analysis recommendation on action to take on the index
CREATE: Recommend adding the index to the table.
DROP-DUP: Recommend dropping the index since it is a duplicate
DROP-COUNT: Count of indexes on tables may indicate that the index may be a candidate
for removal.
DROP-USAGE: Usage of the index suggests it may be a candidate for removal
BLEND: Review the missing index details to see if the missing index details can be
added to an existing index.
REALIGN: Bookmark lookups on the index exceed the number of seeks on the table.
Recommend investigating whether to move the clustered index to another index or
add included columns to the indexes that are part of the bookmark lookups.
index_pros: list of reasons that indicate the benefits provided by the index
FK: The index schema maps to a foreign key
UQ: Index is a unique constraint
$, $$, $$$, $$$+: Indicates the ratio of read to write uses in execution plans. The
higher the ratio the more dollar signs; this should correlate to greater benefit
provided by the index.
index_cons: list of reasons that indicate some negative aspects associate with the index
SCN: Flag indicating that the ratio of seeks to scans on the index less than 1,000.
DP: Index schema is a duplicate of another index.
OV: Index schema overlaps another index.
$, $$, $$$, $$$+: Indicates the ratio of write to read uses in execution plans. The
higher the ratio the more dollar signs; this should correlate to more cost
incurred by the index.
DSB: Index is disabled. These should be enabled or removed.
filegroup: file group that the index is located.
schema_id: Schema ID
schema_name: Name of the schema.
object_id: Object ID
table_name: Name of the table name
index_id: Index ID
index_name: Name of the index.
is_unique: Flag indicating whether an index has a unique index.
has_unique: Flag indicating whether the table has a unique index.
type_desc: Type of index; either clustered or non-clustered.
partition_number: Partition number.
fill_factor: Percentage of free space left on pages the index was created or rebuilt.
is_padded: Boolean value indicating whether fill factor is applied to nonleaf levels
reserved_page_count: Total number of pages reserved for the index.
size_in_mb: The amount of space in MB the index utilizes on disk.
buffered_page_count: Total number of pages in the buffer for the index.
buffer_mb: The amount of space in MB in the buffer for the index.
pct_in_buffer: The percentage of an index that is current in the SQL Server buffer.
table_buffer_mb: The amount of space in MB in the SQL Server buffer that is being
utilized by the table.
row_count: Number of rows in the index.
missing_index_impact: Calculation of impact of a potential index. This is based on the seeks and
scans that the index could have utilized multiplied by average improvement the index
would have provided. This is included only for missing indexes.
existing_ranking: Ranking of the existing indexes ordered by user_total descending across
the indexes for the table.
user_total: Total number of seek, scan, and lookup operations for the index.
user_total_pct: Percentage of total number of seek, scan, and lookup operations for this
index compared to all seek, scan, and lookup operations for existing indexes for the
table.
estimated_user_total_pct: Percentage of total number of seek, scan, and lookup operations
for this index compared to all seek, scan, and lookup operations for existing and
potential indexes for the table. This number is naturally skewed because a seek for
potential Index A resulted in another operation on an existing index and both of
these operations would be counted.
user_seeks: Number of seek operations on the index.
user_scans: Number of scan operations on the index.
user_lookups: Number of lookup operations on the index.
user_updates: Number of update operations on the index.
read_to_update_ratio: Ratio of user_seeks, user_scans, and user_lookups to user_updates.
read_to_update: Division of user_seeks, user_scans, and user_lookups by user_updates.
update_to_read: Division of user_updates to user_seeks, user_scans by user_lookups.
row_lock_count: Cumulative number of row locks requested.
row_lock_wait_count: Cumulative number of times the Database Engine waited on a row lock.
row_lock_wait_in_ms: Total number of milliseconds the Database Engine waited on a row
lock.
row_block_pct: Percentage of row locks that encounter waits on a row lock.
avg_row_lock_waits_ms: Average number of milliseconds the Database Engine waited on a row
lock.
page_latch_wait_count: Cumulative number of times the page latch waits occurred
avg_page_latch_wait_ms: Average number of milliseconds the Database Engine waited on a
page latch wait.
page_io_latch_wait_count: Cumulative number of times the page IO latch waits occurred
avg_page_io_latch_wait_ms: Average number of milliseconds the Database Engine waited on a
page IO latch wait.
tree_page_latch_wait_count: Cumulative number of times the tree page latch waits occurred
avg_tree_page_latch_wait_ms: Average number of milliseconds the Database Engine waited on
a tree page latch wait.
tree_page_io_latch_wait_count: Cumulative number of times the tree page IO latch waits
occurred
avg_tree_page_io_latch_wait_ms: Average number of milliseconds the Database Engine waited
on a tree page IO latch wait.
read_operations: Cumulative count of range_scan_count and singleton_lookup_count
operations
leaf_writes: Cumulative count of leaf_insert_count, leaf_update_count, leaf_delete_count
and leaf_ghost_count operations
leaf_page_allocations: Cumulative count of leaf-level page allocations in the index or
heap. For an index, a page allocation corresponds to a page split.
leaf_page_merges: Cumulative count of page merges at the leaf level.
nonleaf_writes: Cumulative count of leaf_insert_count, leaf_update_count and
leaf_delete_count operations
nonleaf_page_allocations: Cumulative count of page allocations caused by page splits
above the leaf level.
nonleaf_page_merges: Cumulative count of page merges above the leaf level.
indexed_columns: Columns that are part of the index, missing index or foreign key.
included_columns: Columns that are included in the index or missing index.
indexed_columns_ids: Column IDs that are part of the index, missing index or foreign
key
included_column_ids: Column IDs that are included in the index or missing index.
duplicate_indexes: List of Indexes that exist on the table that are identical to the
index on this row.
overlapping_indexes: List of Indexes that exist on the table that overlap the index on
this row.
related_foreign_keys: List of foreign keys that are related to the index either as an
exact match or covering index.
related_foreign_keys_xml: XML document listing foreign keys that are related to the index
either as an exact match or covering index.
Revision History
Date Version DESCRIPTION
----------------------------------------------------------------------------------------------
2012-12-10 11.01 Rewrote information collection to reduce dynamic SQL
* Fixed issue with Object_ID
* Added unique check and filter definition to duplication and
overlapping indexes
* Fixed errors with documentation section.
* Added parameters @IncludeMissingIndexes, @IncludeMissingFKIndexes,
and @Output
* Removed DDL create and drop columns for indexes, too much risk in
automatic index building
2012-12-28 11.02 * Fixed new issues WITH Object_ID
* change missing foreign key logic to include basic details but not
suggest missing indexes with @IncludeMissingFKIndexes flag
* Fixed issues with missing foreign keys that was not displaying them
* Improved default sort for DETAILED output
* Updated case for case-sensitive collations
2013-01-08 11.03 * Fixed issue with overlapping indexes and duplicate indexes related
filtered indexes
* Improved error handling to solve issue with truncation MESSAGE
2013-03-07 11.04 * Fixed duplicate index logic to match that from
http://www.jasonstrate.com/2013/03/thats-actually-a-duplicate-index/
* Added check for disabled indexes to checks
* Expanded drop reasons with explanations and descriptions for
dropping because of duplication, usage, and count of indexes
* Increased size of foreign key name to accommodate larger names
* Fixed issue causing DROP recommmendation on missing indexes
2013-03-20 11.05 * Fixed data type on missing_index_impact (Credit: Dick Baker)
2013-05-22 11.06 * Fixed bug causing mismatch with foreign key analysis
* Adjusted overlapping index logic which was broke with new duplicate
index logic
*********************************************************************************************/
CREATE PROCEDURE dbo.sp_IndexAnalysis
(
@TableName NVARCHAR(256) = NULL
,@IncludeMissingIndexes BIT = 1
,@IncludeMissingFKIndexes BIT = 1
,@Output VARCHAR(20) = 'DETAILED'
)
WITH RECOMPILE
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @ERROR_MESSAGE NVARCHAR(2048)
,@ERROR_SEVERITY INT
,@ERROR_STATE INT
DECLARE @SQL NVARCHAR(max)
,@DB_ID INT
,@ObjectID INT
,@DatabaseName NVARCHAR(max)
BEGIN TRY
IF @Output NOT IN ('DETAILED','DUPLICATE','OVERLAPPING')
RAISERROR('The value "%s" provided for the @Output parameter is not valid',16,1,@Output)
SELECT @DB_ID = DB_ID()
,@ObjectID = OBJECT_ID(QUOTENAME(DB_NAME(DB_ID()))+'.'+COALESCE(QUOTENAME(PARSENAME(@TableName,2)),'')+'.'+QUOTENAME(PARSENAME(@TableName,1)))
,@DatabaseName = QUOTENAME(DB_NAME(DB_ID()))
-- Obtain memory buffer information on database objects
IF OBJECT_ID('tempdb..#MemoryBuffer') IS NOT NULL
DROP TABLE #MemoryBuffer
CREATE TABLE #MemoryBuffer (
database_id INT
,object_id INT
,index_id INT
,partition_number INT
,buffered_page_count INT
,buffered_mb DECIMAL(12, 2)
)
SET @SQL = 'WITH AllocationUnits
AS (
SELECT p.object_id
,p.index_id
,p.partition_number
,au.allocation_unit_id
FROM '+@DatabaseName+'.sys.allocation_units AS au
INNER JOIN '+@DatabaseName+'.sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT p.object_id
,p.index_id
,p.partition_number
,au.allocation_unit_id
FROM '+@DatabaseName+'.sys.allocation_units AS au
INNER JOIN '+@DatabaseName+'.sys.partitions AS p ON au.container_id = p.partition_id AND au.type = 2
)
SELECT DB_ID()
,au.object_id
,au.index_id
,au.partition_number
,COUNT(*)AS buffered_page_count
,CONVERT(DECIMAL(12,2), CAST(COUNT(*) as bigint)*CAST(8 as float)/1024) as buffer_mb
FROM '+@DatabaseName+'.sys.dm_os_buffer_descriptors AS bd
INNER JOIN AllocationUnits au ON bd.allocation_unit_id = au.allocation_unit_id
WHERE bd.database_id = db_id()
GROUP BY au.object_id, au.index_id, au.partition_number'
BEGIN TRY
INSERT INTO #MemoryBuffer
EXEC sys.sp_executesql @SQL
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Populate #MemoryBuffer (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
-- Obtain index meta data information
IF OBJECT_ID('tempdb..#IndexMeta') IS NOT NULL
DROP TABLE #IndexMeta
CREATE TABLE #IndexMeta (
database_id SMALLINT
,filegroup_name NVARCHAR(128)
,schema_id INT
,schema_name NVARCHAR(128)
,object_id INT
,table_name NVARCHAR(128)
,index_id INT
,index_name NVARCHAR(128)
,is_primary_key BIT
,is_unique BIT
,is_disabled BIT
,type_desc NVARCHAR(128)
,partition_number INT
,fill_factor TINYINT
,is_padded BIT
,reserved_page_count BIGINT
,size_in_mb DECIMAL(12, 2)
,row_count BIGINT
,filter_definition NVARCHAR(MAX)
,indexed_columns NVARCHAR(MAX)
,included_columns NVARCHAR(MAX)
,key_columns NVARCHAR(MAX)
,data_columns NVARCHAR(MAX)
,indexed_columns_ids NVARCHAR(1024)
,included_column_ids NVARCHAR(1024)
)
SET @SQL = N'SELECT
database_id = DB_ID()
, filegroup = ds.name
, s.schema_id
, schema_name = s.name
, object_id = t.object_id
, table_name = t.name
, i.index_id
, index_name = COALESCE(i.name, ''N/A'')
, ps.partition_number
, i.is_primary_key
, i.is_unique
, i.is_disabled
, type_desc = CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' END + i.type_desc
, i.fill_factor
, i.is_padded
, ps.reserved_page_count
, size_in_mb = CAST(reserved_page_count * CAST(8 as float) / 1024 as DECIMAL(12,2))
, row_count
, i.filter_definition
, indexed_columns = STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY key_ordinal ASC
FOR XML PATH('''')), 1, 2, '''')
, included_columns = STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 1
ORDER BY key_ordinal ASC
FOR XML PATH('''')), 1, 2, '''')
, key_columns = STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY key_ordinal ASC
FOR XML PATH(''''))
+ COALESCE((SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id
AND c.column_id = ic_key.column_id
AND i.index_id = ic_key.index_id
AND ic_key.is_included_column = 0
WHERE i.object_id = ic.object_id
AND ic.index_id = 1
AND ic.is_included_column = 0
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')),''''), 1, 2, '''')
, data_columns = CASE WHEN i.index_id IN (0,1) THEN ''ALL-COLUMNS'' ELSE STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id AND c.column_id = ic_key.column_id AND ic_key.index_id = 1
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND ic.is_included_column = 1
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')), 1, 2, '''') END
, indexed_column_ids = (SELECT QUOTENAME(CAST(ic.column_id AS VARCHAR(10))
+ CASE WHEN ic.is_descending_key = 0 THEN ''+'' ELSE ''-'' END, ''('')
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY key_ordinal ASC
FOR XML PATH(''''))
+ ''|'' + COALESCE((SELECT QUOTENAME(CAST(ic.column_id AS VARCHAR(10))
+ CASE WHEN ic.is_descending_key = 0 THEN ''+'' ELSE ''-'' END, ''('')
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id
AND c.column_id = ic_key.column_id
AND i.index_id = ic_key.index_id
AND ic_key.is_included_column = 0
WHERE i.object_id = ic.object_id
AND ic.index_id = 1
AND ic.is_included_column = 0
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')),'''')
+ CASE WHEN i.is_unique = 1 THEN ''U'' ELSE '''' END
, included_column_ids = CASE WHEN i.index_id IN (0,1) THEN ''ALL-COLUMNS'' ELSE
COALESCE((SELECT QUOTENAME(ic.column_id,''('')
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id AND c.column_id = ic_key.column_id AND ic_key.index_id = 1
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND ic.is_included_column = 1
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')), SPACE(0)) END
FROM '+@DatabaseName+'.sys.tables t
INNER JOIN '+@DatabaseName+'.sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN '+@DatabaseName+'.sys.indexes i ON t.object_id = i.object_id
INNER JOIN '+@DatabaseName+'.sys.data_spaces ds ON i.data_space_id = ds.data_space_id
INNER JOIN '+@DatabaseName+'.sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id'
IF @ObjectID IS NOT NULL
SET @SQL = @SQL + CHAR(13) + 'WHERE t.object_id = @ObjectID '
BEGIN TRY
INSERT INTO #IndexMeta (
database_id
,filegroup_name
,schema_id
,schema_name
,object_id
,table_name
,index_id
,index_name
,partition_number
,is_primary_key
,is_unique
,is_disabled
,type_desc
,fill_factor
,is_padded
,reserved_page_count
,size_in_mb
,row_count
,filter_definition
,indexed_columns
,included_columns
,key_columns
,data_columns
,indexed_columns_ids
,included_column_ids)
EXEC sys.sp_executesql @SQL, N'@DB_ID INT, @ObjectID INT', @DB_ID = @DB_ID, @ObjectID = @ObjectID
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Populate #IndexMeta (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
IF OBJECT_ID('tempdb..#IndexStatistics') IS NOT NULL
DROP TABLE #IndexStatistics
SELECT IDENTITY(INT,1,1) AS row_id
,CAST('' AS VARCHAR(10)) AS index_action
,CAST('' AS VARCHAR(25)) AS index_pros
,CAST('' AS VARCHAR(25)) AS index_cons
,im.database_id
,im.filegroup_name
,im.schema_id
,im.schema_name
,im.object_id
,im.table_name
,im.index_id
,im.index_name
,im.is_primary_key
,im.is_unique
,im.is_disabled
,CAST(0 AS BIT) AS has_unique
,im.type_desc
,im.partition_number
,im.fill_factor
,im.is_padded
,im.reserved_page_count
,im.size_in_mb
,mb.buffered_page_count
,mb.buffered_mb
,CAST(0 AS INT) AS table_buffered_mb
,CAST(100.*mb.buffered_page_count/NULLIF(im.reserved_page_count,0) AS DECIMAL(12,2)) AS buffered_percent
,im.row_count
,ROW_NUMBER() OVER (PARTITION BY im.object_id ORDER BY im.is_primary_key desc,ius.user_seeks + ius.user_scans + ius.user_lookups DESC) AS index_rank
, ius.user_seeks + ius.user_scans + ius.user_lookups AS user_total
, COALESCE(CAST(100 * (ius.user_seeks + ius.user_scans + ius.user_lookups)
/(NULLIF(SUM(ius.user_seeks + ius.user_scans + ius.user_lookups)
OVER(PARTITION BY im.object_id), 0) * 1.) as DECIMAL(6,2)),0) AS user_total_pct
,CAST(0 AS DECIMAL(6,2)) AS estimated_user_total_pct
,CAST(0 AS FLOAT) AS missing_index_impact -- Dick Baker 201303 (INT range not big enough and is f.p. anyway)
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
,ius.user_updates
,(1.*(ius.user_seeks + ius.user_scans + ius.user_lookups))/NULLIF(ius.user_updates,0) AS read_to_update_ratio
,CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups >= ius.user_updates
THEN CEILING(1.*(ius.user_seeks + ius.user_scans + ius.user_lookups)/COALESCE(NULLIF(ius.user_seeks,0),1))
ELSE 0 END AS read_to_update
,CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups <= ius.user_updates
THEN CEILING(1.*(ius.user_updates)/COALESCE(NULLIF(ius.user_seeks + ius.user_scans + ius.user_lookups,0),1))
ELSE 0 END AS update_to_read
,ios.row_lock_count
,ios.row_lock_wait_count
,ios.row_lock_wait_in_ms
,CAST(100.0 * ios.row_lock_wait_count/NULLIF(ios.row_lock_count,0) AS DECIMAL(12,2)) AS row_block_pct
,CAST(1. * ios.row_lock_wait_in_ms /NULLIF(ios.row_lock_wait_count,0) AS DECIMAL(12,2)) AS avg_row_lock_waits_ms
,ios.page_latch_wait_count
,CAST(1. * page_latch_wait_in_ms / NULLIF(ios.page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_page_latch_wait_ms
,ios.page_io_latch_wait_count
,CAST(1. * ios.page_io_latch_wait_in_ms / NULLIF(ios.page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_page_io_latch_wait_ms
,ios.tree_page_latch_wait_count AS tree_page_latch_wait_count
,CAST(1. * tree_page_latch_wait_in_ms / NULLIF(ios.tree_page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_tree_page_latch_wait_ms
,ios.tree_page_io_latch_wait_count
,CAST(1. * ios.tree_page_io_latch_wait_in_ms / NULLIF(ios.tree_page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_tree_page_io_latch_wait_ms
,range_scan_count + singleton_lookup_count AS read_operations
,ios.leaf_insert_count + ios.leaf_update_count + ios.leaf_delete_count + ios.leaf_ghost_count AS leaf_writes
,leaf_allocation_count As leaf_page_allocations
,ios.leaf_page_merge_count AS leaf_page_merges
,ios.nonleaf_insert_count + ios.nonleaf_update_count + ios.nonleaf_delete_count AS nonleaf_writes
,ios.nonleaf_allocation_count AS nonleaf_page_allocations
,ios.nonleaf_page_merge_count AS nonleaf_page_merges
,im.indexed_columns
,im.included_columns
,im.filter_definition
,key_columns
,data_columns
,im.indexed_columns_ids
,im.included_column_ids
,CAST('' AS VARCHAR(MAX)) AS duplicate_indexes
,CAST('' AS SMALLINT) AS first_dup_index_id
,CAST('' AS VARCHAR(MAX)) AS overlapping_indexes
,CAST('' AS VARCHAR(MAX)) AS related_foreign_keys
,CAST('' AS XML) AS related_foreign_keys_xml
INTO #IndexStatistics
FROM #IndexMeta im
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON im.object_id = ius.object_id AND im.index_id = ius.index_id AND im.database_id = ius.database_id
LEFT OUTER JOIN sys.dm_db_index_operational_stats(@DB_ID, NULL, NULL, NULL) ios ON im.object_id = ios.object_id AND im.index_id = ios.index_id AND im.partition_number = ios.partition_number
LEFT OUTER JOIN #MemoryBuffer mb ON im.object_id = mb.object_id AND im.index_id = mb.index_id AND im.partition_number = mb.partition_number
IF @IncludeMissingIndexes = 1
BEGIN
INSERT INTO #IndexStatistics
(filegroup_name, schema_id, schema_name, object_id, table_name, index_name, type_desc, missing_index_impact, index_rank, user_total, user_seeks, user_scans, user_lookups, indexed_columns, included_columns)
SELECT
'' AS filegroup_name
,SCHEMA_ID(OBJECT_SCHEMA_NAME(mid.object_id)) AS schema_id
,OBJECT_SCHEMA_NAME(mid.object_id) AS schema_name
,mid.object_id
,OBJECT_NAME(mid.object_id) AS table_name
,'--MISSING INDEX--' AS index_name
,'NONCLUSTERED' AS type_desc
,(migs.user_seeks + migs.user_scans) * migs.avg_user_impact as impact
,0 AS index_rank
,migs.user_seeks + migs.user_scans as user_total
,migs.user_seeks
,migs.user_scans
,0 as user_lookups
,COALESCE(equality_columns + CASE WHEN inequality_columns IS NOT NULL THEN ', ' ELSE SPACE(0) END, SPACE(0)) + COALESCE(inequality_columns, SPACE(0)) as indexed_columns
,included_columns
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = @DB_ID
END
-- Collect foreign key information.
IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
DROP TABLE #ForeignKeys
CREATE TABLE #ForeignKeys
(
foreign_key_name NVARCHAR(256)
,object_id INT
,fk_columns NVARCHAR(max)
,fk_columns_ids NVARCHAR(1024)
)
SET @SQL = N'SELECT fk.name + ''|PARENT'' AS foreign_key_name
,fkc.parent_object_id AS object_id
,STUFF((SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
INNER JOIN '+@DatabaseName+'.sys.columns c ON ifkc.parent_object_id = c.object_id AND ifkc.parent_column_id = c.column_id
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')), 1, 2, '''') AS fk_columns
,(SELECT QUOTENAME(CAST(ifkc.parent_column_id AS VARCHAR(10))+''+'',''('')
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')) AS fk_columns_compare
FROM '+@DatabaseName+'.sys.foreign_keys fk
INNER JOIN '+@DatabaseName+'.sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_column_id = 1
AND (fkc.parent_object_id = @ObjectID OR @ObjectID IS NULL)
UNION ALL
SELECT fk.name + ''|REFERENCED'' as foreign_key_name
,fkc.referenced_object_id AS object_id
,STUFF((SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
INNER JOIN '+@DatabaseName+'.sys.columns c ON ifkc.referenced_object_id = c.object_id AND ifkc.referenced_column_id = c.column_id
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')), 1, 2, '''') AS fk_columns
,(SELECT QUOTENAME(CAST(ifkc.referenced_column_id AS VARCHAR(10))+''+'',''('')
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')) AS fk_columns_compare
FROM '+@DatabaseName+'.sys.foreign_keys fk
INNER JOIN '+@DatabaseName+'.sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_column_id = 1
AND (fkc.referenced_object_id = @ObjectID OR @ObjectID IS NULL)'
BEGIN TRY
INSERT INTO #ForeignKeys
(foreign_key_name, object_id, fk_columns, fk_columns_ids)
EXEC sp_executesql @SQL, N'@DB_ID INT, @ObjectID INT', @DB_ID = @DB_ID, @ObjectID = @ObjectID
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Populate #ForeignKeys (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
-- Determine duplicate, overlapping, and foreign key index information
UPDATE i
SET duplicate_indexes = STUFF((SELECT ', ' + index_name AS [data()]
FROM #IndexStatistics iibl
WHERE i.object_id = iibl.object_id
AND i.is_primary_key = iibl.is_primary_key
AND i.is_unique = iibl.is_unique
AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
AND i.index_id <> iibl.index_id
AND REPLACE(i.indexed_columns_ids,'|','') = REPLACE(iibl.indexed_columns_ids,'|','')
AND i.included_column_ids = iibl.included_column_ids
FOR XML PATH('')), 1, 2, '')
,first_dup_index_id = (SELECT MIN(index_id)
FROM #IndexStatistics iibl
WHERE i.object_id = iibl.object_id
AND i.is_primary_key = iibl.is_primary_key
AND i.is_unique = iibl.is_unique
AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
AND i.index_id > iibl.index_id
AND REPLACE(i.indexed_columns_ids,'|','') = REPLACE(iibl.indexed_columns_ids,'|','')
AND i.included_column_ids = iibl.included_column_ids)
,overlapping_indexes = STUFF((SELECT ', ' + index_name AS [data()]
FROM #IndexStatistics iibl
WHERE i.object_id = iibl.object_id
AND i.is_primary_key = iibl.is_primary_key
AND i.is_unique = iibl.is_unique
AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
AND i.index_id <> iibl.index_id
AND LEFT(i.indexed_columns_ids, CHARINDEX('|',iibl.indexed_columns_ids,1)-1)
LIKE LEFT(iibl.indexed_columns_ids, CHARINDEX('|',i.indexed_columns_ids,1)-1) + '%'
AND Replace(i.indexed_columns_ids,'|','') <> Replace(iibl.indexed_columns_ids,'|','')
FOR XML PATH('')), 1, 2, '')
,related_foreign_keys = STUFF((SELECT ', ' + foreign_key_name AS [data()]
FROM #ForeignKeys ifk
WHERE ifk.object_id = i.object_id
AND i.indexed_columns_ids LIKE ifk.fk_columns_ids + '%'
FOR XML PATH('')), 1, 2, '')
,related_foreign_keys_xml = CAST((SELECT foreign_key_name
FROM #ForeignKeys fk
WHERE fk.object_id = i.object_id
AND i.indexed_columns_ids LIKE fk.fk_columns_ids + '%'
FOR XML AUTO) as xml)
FROM #IndexStatistics i
IF @IncludeMissingFKIndexes = 1
BEGIN
INSERT INTO #IndexStatistics
(filegroup_name, schema_id, schema_name, object_id, table_name, index_name, type_desc, index_rank, indexed_columns, related_foreign_keys)
SELECT '' AS filegroup_name
,SCHEMA_ID(OBJECT_SCHEMA_NAME(fk.object_id)) AS schema_id
,OBJECT_SCHEMA_NAME(fk.object_id) AS schema_name
,fk.object_id
,OBJECT_NAME(fk.object_id) AS table_name
,'--MISSING FOREIGN KEY--' AS index_name
,'NONCLUSTERED' AS type_desc
,9999
,fk.fk_columns
,fk.foreign_key_name
FROM #ForeignKeys fk
LEFT OUTER JOIN #IndexStatistics i ON fk.object_id = i.object_id AND i.indexed_columns_ids LIKE fk.fk_columns_ids + '%'
WHERE i.index_name IS NULL
END
-- Determine whether tables have unique indexes
UPDATE i
SET has_unique = 1
FROM #IndexStatistics i
WHERE EXISTS (SELECT * FROM #IndexStatistics ii WHERE i.object_id = ii.object_id AND ii.is_unique = 1)
-- Calculate estimated user total for each index.
;WITH Aggregation
AS (
SELECT row_id
,CAST(100. * (user_seeks + user_scans + user_lookups)
/(NULLIF(SUM(user_seeks + user_scans + user_lookups)
OVER(PARTITION BY schema_name, table_name), 0) * 1.) as DECIMAL(12,2)) AS estimated_user_total_pct
,SUM(buffered_mb) OVER(PARTITION BY schema_name, table_name) as table_buffered_mb
FROM #IndexStatistics
)
UPDATE ibl
SET estimated_user_total_pct = COALESCE(a.estimated_user_total_pct, 0)
,table_buffered_mb = a.table_buffered_mb
FROM #IndexStatistics ibl
INNER JOIN Aggregation a ON ibl.row_id = a.row_id
-- Update Index Action information
;WITH IndexAction
AS (
SELECT row_id
,CASE WHEN user_lookups > user_seeks AND type_desc IN ('CLUSTERED', 'HEAP', 'UNIQUE CLUSTERED') THEN 'REALIGN'
WHEN is_disabled = 1 THEN 'ENABLE'
WHEN duplicate_indexes IS NOT NULL AND first_dup_index_id IS NOT NULL AND index_id IS NOT NULL THEN 'DROP-DUP'
WHEN type_desc = '--MISSING FOREIGN KEY--' THEN 'CREATE'
WHEN type_desc = 'XML' THEN '---'
WHEN is_unique = 1 THEN '---'
WHEN related_foreign_keys IS NOT NULL THEN '---'
WHEN type_desc = '--NONCLUSTERED--' AND ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total desc) <= 10 AND estimated_user_total_pct > 1 THEN 'CREATE'
WHEN type_desc = '--NONCLUSTERED--' THEN 'BLEND'
WHEN ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total desc, index_rank) > 10 AND index_id IS NOT NULL THEN 'DROP-COUNT'
WHEN index_id NOT IN (0,1) AND duplicate_indexes IS NULL AND user_total = 0 AND index_id IS NOT NULL
THEN 'DROP-USAGE'
ELSE '---' END AS index_action
FROM #IndexStatistics
)
UPDATE ibl
SET index_action = ia.index_action
FROM #IndexStatistics ibl INNER JOIN IndexAction ia
ON ibl.row_id = ia.row_id
-- Update Pro/Con statuses
UPDATE #IndexStatistics
SET index_pros = COALESCE(STUFF(CASE WHEN related_foreign_keys IS NOT NULL THEN ', FK' ELSE '' END
+ CASE WHEN is_unique = 1 THEN ', UQ' ELSE '' END
+ COALESCE(', ' + CASE WHEN read_to_update BETWEEN 1 AND 9 THEN '$'
WHEN read_to_update BETWEEN 10 AND 99 THEN '$$'
WHEN read_to_update BETWEEN 100 AND 999 THEN '$$$'
WHEN read_to_update > 999 THEN '$$$+' END, '')
,1,2,''),'')
,index_cons = COALESCE(STUFF(CASE WHEN user_seeks / NULLIF(user_scans,0) < 1000 THEN ', SCN' ELSE '' END
+ CASE WHEN duplicate_indexes IS NOT NULL THEN ', DP' ELSE '' END
+ CASE WHEN overlapping_indexes IS NOT NULL THEN ', OV' ELSE '' END
+ COALESCE(', ' + CASE WHEN update_to_read BETWEEN 1 AND 9 THEN '$'
WHEN update_to_read BETWEEN 10 AND 99 THEN '$$'
WHEN update_to_read BETWEEN 100 AND 999 THEN '$$$'
WHEN update_to_read > 999 THEN '$$$+' END, '')
+ CASE WHEN is_disabled = 1 THEN ', DSB' ELSE '' END
,1,2,''),'')
IF @Output = 'DETAILED'
BEGIN
SELECT
index_action
, index_pros
, index_cons
, QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
, index_name
, type_desc
, indexed_columns
, included_columns
, filter_definition
, is_primary_key
, is_unique
, is_disabled
, has_unique
, partition_number
, fill_factor
, is_padded
, size_in_mb
, buffered_mb
, table_buffered_mb
, buffered_percent
, row_count
, user_total_pct
, estimated_user_total_pct
, missing_index_impact
, user_total
, user_seeks
, user_scans
, user_lookups
, user_updates
, read_to_update_ratio
, read_to_update
, update_to_read
, row_lock_count
, row_lock_wait_count
, row_lock_wait_in_ms
, row_block_pct
, avg_row_lock_waits_ms
, page_latch_wait_count
, avg_page_latch_wait_ms
, page_io_latch_wait_count
, avg_page_io_latch_wait_ms
, tree_page_latch_wait_count
, avg_tree_page_latch_wait_ms
, tree_page_io_latch_wait_count
, avg_tree_page_io_latch_wait_ms
, read_operations
, leaf_writes
, leaf_page_allocations
, leaf_page_merges
, nonleaf_writes
, nonleaf_page_allocations
, nonleaf_page_merges
, duplicate_indexes
, overlapping_indexes
, related_foreign_keys
, related_foreign_keys_xml
, key_columns
, data_columns
FROM #IndexStatistics
WHERE (estimated_user_total_pct > 0.01 AND index_id IS NULL)
OR related_foreign_keys IS NOT NULL
OR index_id IS NOT NULL
ORDER BY table_buffered_mb DESC, object_id, COALESCE(user_total,-1) DESC, COALESCE(user_updates,-1) DESC, COALESCE(index_id,999)
END
ELSE IF @Output = 'DUPLICATE'
BEGIN
SELECT
DENSE_RANK() OVER (ORDER BY key_columns, data_columns) AS duplicate_group
, index_action
, index_pros
, index_cons
, QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
, index_name
, type_desc
, indexed_columns
, included_columns
, is_primary_key
, is_unique
, duplicate_indexes
, size_in_mb
, buffered_mb
, table_buffered_mb
, buffered_percent
, row_count
, user_total_pct
, user_total
, user_seeks
, user_scans
, user_lookups
, user_updates
, read_operations
FROM #IndexStatistics
WHERE duplicate_indexes IS NOT NULL
ORDER BY table_buffered_mb DESC, object_id, RANK() OVER (ORDER BY key_columns, data_columns)
END
ELSE IF @Output = 'OVERLAPPING'
BEGIN
SELECT
index_action
, index_pros
, index_cons
, QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
, overlapping_indexes
, index_name
, type_desc
, indexed_columns
, included_columns
, is_primary_key
, is_unique
, size_in_mb
, buffered_mb
, table_buffered_mb
, buffered_percent
, row_count
, user_total_pct
, user_total
, user_seeks
, user_scans
, user_lookups
, user_updates
, read_operations
FROM #IndexStatistics
WHERE overlapping_indexes IS NOT NULL
ORDER BY table_buffered_mb DESC, object_id, user_total DESC
END
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Procedure Error (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
GO
USE AdventureWorks2012
GO
EXEC dbo.sp_IndexAnalysis @Output = 'DETAILED'
,@TableName = '[Production].[ProductDescription]'
,@IncludeMissingIndexes = 1
,@IncludeMissingFKIndexes = 1
EXEC dbo.sp_IndexAnalysis @Output = 'OVERLAPPING'
,@TableName = '[Production].[ProductDescription]'
,@IncludeMissingIndexes = 1
,@IncludeMissingFKIndexes = 1