SQL Swiss Army Knife #12 – Index information galore!

EDIT (06/06/2012): Added script for all databases (may cause performance problems in large servers) and current database only. also fixed issue with partition aligned indexes.

EDIT (19/11/2012): Added Heap information and redesigned duplicate and redundant index search.

EDIT (18/12/2012): Fixed miscellaneous issues and added schema information.

EDIT (20/01/2013): Added index related information.

EDIT (27/02/2013): Fixed issue with partition info; Removed alternate keys from search for Unused and Rarely used indexes.

EDIT (17/03/2013): Added more information to duplicate and redundant indexes output, valuable when deciding which to delete, namely if duplicates are non-clustered.

EDIT (19/03/2013): Fixed issue with potential duplicate index_ids in sys.dm_db_index_operational_stats relating to internal tables.

EDIT (06/05/2013): Changed data collection to minimize blocking potential on VLDBs.

EDIT (20/05/2013): Fixed issue with database names with special characters.

EDIT (04/06/2014): Refined search for duplicate and redundant indexes. 

EDIT (12/11/2014): Added SQL 2014 Hash indexes support; fixed issue with retrieving Heap information; changed scan mode to LIMITED; added search for hard coded references to indexes.

Hello all,
Here is another post on SQL scripts that may help DBAs, following the series “SQL Swiss Army Knife”. I’ve been using and tweaking this script for years now, and with a recent update for SQL 2012, I’ve decided to share it.

So, for the current database in scope, it will report a great deal of useful information about all indexes, including duplicate, redundant, rarely used and completely unused indexes.

After collecting the relevant information on the database(s) in scope, the first report is “All_Index_Info” and “All_Heaps_Info” with information from Clustered and Non-Clustered indexes (including ColumnStore), including name, index type, fragmentation, size, usage statistics on lookups and scans, as well as IO and locking, the ratio of reads vs. writes and other useful information.

If on SQL 2014, the sections “All_XTP_HashIX_Info” and “All_XTP_RangeIX_Info” will report on indexes supporting In-Memory tables.

“Unused_IX_With_Updates”/”Unused_IX_No_Updates” and “Rarely_Used_IX” are next, where unused are either indexes never touched since SQL Server started up, or indexes that were only updated, and never had seeks, scans or lookups. “Duplicate_IX” and “Redundant_IX” follow, where duplicate share the same keys and included columns, and we look for references indexes in sql modules, to find hard references to those indexes. Redundant, on the other hand, are indexes sharing the part of the same key and/or included columns.

We also report on indexes that have keys larger than 900 bytes in section “Large_Index_Key”, indexes with a fill factor lower than 80% in “Low_Fill_Factor” section and finally “NonUnique_CIXs” reporting on non-Unique Clustered Indexes.

Hope you find it useful.

Download scripts here: view_IndexInformation.sql (for all DBs at once) and view_IndexInformation_CurrentDB.sql (for a DB in scope)

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided “as -is”, without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.

Comments (3)

  1. justin says:

    can you please add clip it button on your site so i can clip it?


  2. Derek Wharton says:

    I receive an error using  view_IndexInformation_CurrentDB.sql

    Column 'sys.indexes.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  3. pmasl says:

    Hello Derek, thanks for the input. I had actually fixed this before, but it seems I never uploaded the fixed version.

    All fixed now.