Nucleus Datakonsult sp_indexinfo - enhanced index information procedure


About me and Nucleus

Articles, tips etc.
My blog
Microsoft articles

Courses and training

Microsoft Most Valuable Professional


I often find myself wanting to know lots about the indexes for a table or for all tables in a database. This is why I wrote sp_indexinfo.

Versions etc.
SQL Server 2008 and later. Article last modified: See change log at end of this page.

I can't count how many times I wanted to know more about physical attributes for a table, including index information. So I end up running sp_helpindex, some SELECT over sys.indexes, some reports in SSMS, some other queries against other catalog and dynamic management views, etc. I wrote this procedure for those cases. Here's my version of a procedure to give me information like:
  • What indexes exists for a particular table or for each table in the database
  • Clustered, non-clustered or heap
  • Columns in the index
  • Included columns in the index
  • Unique or nonunique
  • Number rows in the table
  • Space usage
  • How frequently the indexes has been used
  • Any obvious indexes I should add?
For this, I use a number of sources, like sys.indexes, sys.columns, sys.partitions, sys.allocation_units, sys.data_spaces, sys.dm_db_index_usage_stats etc.

I frequently spend lots of time in one particular database, and I then would want to "post-process" the result from a procedure like this. By "post-process" I mean things like sorting and grouping. I suggest that you for those cases take the first SELECT statement and create a view out of it. You can now SELECT from this view with any ORDER BY, GROUP BY, aggregate function etc as you wish. Start with the row starting with WITH and end before the "AND OBJECT_NAME(i.OBJECT_ID) LIKE @tblPat" row. For instance, I created such a view (I named the view index_info) in one of my databases and below are some queries I ran against this view:

SELECT FROM index_info

FROM index_info
WHERE table_name 'tblname'

SELECT table_nameSUM(MBAS size
FROM index_info
GROUP BY table_name

  • You should practice caution when interpreting the results. For instance, a missing index reported doesn't neccesarily mean that you should go and create this index. Make sure you understand the information and where it is comming from (read the source code from the proc). I've only used documented sources - Books Online is your friend.
  • In order for this to be treated as a regular system procedure we need to create it in the master database and also mark it as a system procedure. Marking your own procedures as system procedures is not supported so do this at your own risk!!!
  • If you want filter index specification, you need to uncomment that line of code. This is only available on 2008, so I decided to have this commented so I (still) can have only one version of this proc. Just search the source for 2008 and you will quickly enough find the line of code to uncomment.

@tblPat sysname '%' 
Name of table. Default is all tables in current database.

@missing_ix tinyint 0
Whether or not to include missing indexes information. Default is 0 (no).

Usage examples
USE Adventureworks
EXEC sp_indexinfo
EXEC sp_indexinfo 'Product'
EXEC sp_indexinfo 'Product'0
EXEC sp_indexinfo 'Product%'
EXEC sp_indexinfo DEFAULT0
EXEC pubs..sp_indexinfo

Information returned
Below is brief documentation of what the procedure returns. Note that the procedure only uses documented sources (catalog views, functions etc.). So for deeper documentation I suggest you check the SQL Server Books Online for sys.indexes, sys.partitions etc. (see the source code for the procedure).

First resultset, index information
sch_nameRelational schema name.
tbl_nameTable name.
ix_nameIndex name.
typeType of index:
heap = datapages for heap table (this is not really an index)
cl = clustered index
nc = non-clustered index
col-store = non-clustered columns store index
xml = xml index
uq1 if index is unique, else 0.
cnstrWhether index is created through constraint
no = no
PK = yes, primary key constraint
UQ = yes, unique constraint
key_colsColumns in index key. NULL for type = heap.
incl_colsIncluded columns, if any.
rowsNumber of rows in the index.
pagesNumber of pages used. One page = 8KB.
MBNumber of MB used.
seeksNumber of seeks by user queries since start of SQL Server.
scansNumber of scans by user queries since start of SQL Server.
lookupsNumber of bookmark lookups (only for cl ix, lookup from nc index) by user queries since start of SQL Server.
updatesNumber of updates by user queries since start of SQL Server.
locationWhat filegroup the index is created on.
filterNULL for heap or non-filtered index.
disabled0 if not disabled, 1 if disabled.
depthNumber of levels in index tree.
fill_factorwhat fill factor the index was created with (not current value).
page_lock_disallowed0 if page lock are allowed, 1 if page locks are not allowed.
row_lock_disallowed0 if row lock are allowed, 1 if row locks are not allowed.

Second resultset, missing index information. This is recommended indexes according to the optimizer. When the optimizer work a query, it can notice "obvious" indexes to add. I do not suggest you follow these blindly. They might, however, pick up things you missed. This informationis picked up from the sys.dm_db_missing_index DMV's. See SQL Server Books Online for deeper information.
schema_nameRelational schema name.
table_nameTable name.
ddlCREATE INDEX statements, generated from the DMVs.
user_seeksNumber of seeks caused by user queries that the recommended index in the group could have been used for.
user_scansNumber of scans caused by user queries that the recommended index in the group could have been used for.
avg_user_impactAverage percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

The code:
You find the code here.

Change log
Added missing index information controlled by optional parameter.
Added schema name in first resultset.

Added 'DESC' for descending keys, tip from Alejandro Mesa.
Added COALESCE for included columns so the whole CREATE INDEX isn't NULL for missing index recommendation where no included columns are recommended. Caught by Aaron Bertrand.
Restricted missing index recommendation to current database. Caught by Aaron Bertrand.
Added parenthesis around key columns in the generated CREATE INDEX command, in the missing indexes resultset.

Fixed so that a comma is introduced in the generated CREATE TABLE if both equality as well as inequality columns are recommended. Thanks to Razvan Socol.

Made key_columns and included_columns a bit nicer by removing ending ', '.
Included XML indexes. I have not, however considered adding information for space usage for these "indexes". XML indexes are not regular b-tree indexes so space usage info is found elsewhere. And I still want to keep this in one query (not procedural code). Perhaps some slow day I will look at adding spae usage info for XML indexes, but not today. :-)
Thanks to Dejan Sarka for above two tips.

Added brief documentation of what the procedure returns.

Added filtered index, as suggested by at my blog by Lidong (thanks!).

Added explicit text to indicate if index is disabled. Thanks to Henrik Staun Poulsen.

Added calls to INDEXPROPERTY to check if index is disabled, depth, fillfactor etc. This new code is commented, remove comments as you wish. Thanks to Stephen Morris.

Fixed a bug where row count could be doubled or trippled if LOB data exists and/or row overflow data. Thanks JackMcC, reporing this here.

Changed last query to generate the index name. This is based on schema name, object name, and each column name - truncated to 128 characters.
Re-arranged and shortened column names to make output more compact and more commonly used column moved to the right.
Uncommented some columns that were previously commented.
Added support for columnstore indexes.

Added compression column, as per Stephen Morris' suggestion.