sp_helpindexUsage

The suite's diagnostic companion. While SQL Server's built-in sp_helpindex shows you what indexes exist, sp_helpindexUsage shows you how they're actually being used — combining index structure (keys, includes, filters, tree/leaf columns) with live usage statistics (seeks, scans, lookups, writes) and size data, all in one result set.

This is the proc you run before acting on any suite recommendation. Every tool's output includes a commented --EXEC sp_helpindexUsage 'schema.table' line so you can see the full picture for any table before creating, dropping, or modifying an index.

Why sp_helpindex Isn't Enough

SQL Server's built-in sp_helpindex tells you what indexes exist and what columns they contain. That's it. For a DBA making index decisions, the critical questions it can't answer are:

Question	sp_helpindex	sp_helpindexUsage
Is this index actually being used?	—	Seeks, scans, lookups since restart
How much write overhead does it cause?	—	User writes count
Is it worth the storage?	—	Size in KB
Which columns are keys vs includes?	Partial (mixed)	Separate key and include columns
Which columns are at tree vs leaf level?	—	Tree and leaf column breakdown
Is the index filtered?	—	Filter definition
Is the index disabled?	—	Disabled flag
When was it last used?	—	Last seek/scan/lookup/update timestamps
Read vs write ratio?	—	ReadWriteDiff (negative = write-heavy)

Dependency Chain

Three procedures work together, each building on the previous. All are installed in master and marked as system objects via sp_MS_marksystemobject so they execute in the caller's database context — just like sp_help and sp_helpindex.

sp_helpindexUsage
  |
  +--> sp_helpindex2                      Index structure: keys, includes, filters, tree/leaf
  |      |
  |      +--> sp_ExposeColsInIndexLevels  Tree vs leaf column analysis for NC indexes
  |
  +--> sys.sp_MSindexspace                Built-in: index sizes (always available)
  |
  +--> sys.dm_db_index_usage_stats        DMV: seeks, scans, lookups, writes
  |
  +--> DBA_Utilities.dbo.IndexUsageTemp   Only for @Output = 2 (batch collection)

sp_ExposeColsInIndexLevels

The foundation layer. Analyzes each nonclustered index to determine which columns are stored at the tree level (non-leaf pages used for navigation) versus the leaf level (where the actual data pointers live). This distinction matters for understanding index coverage — a column in the tree level can satisfy seeks, while a column only at the leaf level can avoid key lookups but won't help with seeks.

sp_helpindex2

Kimberly Tripp's enhanced replacement for sp_helpindex. Adds critical information the built-in version omits: INCLUDE columns listed separately from key columns, filter definitions for filtered indexes, disabled status, and the tree/leaf column breakdown from sp_ExposeColsInIndexLevels.

sp_helpindexUsage

The top-level wrapper that brings it all together. Takes the structural data from sp_helpindex2, adds index size from sys.sp_MSindexspace, and joins usage statistics from sys.dm_db_index_usage_stats. The result is a single comprehensive view of every index on a table.

Output Columns

Column	Description
`index_id`	SQL Server index ID (1 = clustered, 2+ = nonclustered)
`IndexName`	Index name
`index_keys`	Key columns with sort direction (ASC/DESC)
`included_columns`	INCLUDE columns (separated from keys)
`SizeKB`	Index size in KB
`UserSeeks`	Index seek operations since last restart
`UserScans`	Full index scan operations
`UserLookups`	Key lookup operations (clustered index only)
`UserReads`	Total reads (seeks + scans + lookups)
`UserWrites`	Write operations (every INSERT/UPDATE/DELETE touches the index)
`ReadWriteDiff`	UserReads − UserWrites. Negative = write-heavy (candidate for removal)
`Fill_Factor`	Index fill factor setting
`filter_definition`	WHERE clause for filtered indexes (NULL for standard indexes)
`LastUserSeek`	Timestamp of last seek operation
`LastUserScan`	Timestamp of last scan operation
`LastUserLookup`	Timestamp of last lookup operation
`LastUserUpdate`	Timestamp of last write operation
`is_disabled`	Whether the index is currently disabled
`index_description`	Type + properties (clustered, unique, primary key, nonclustered, etc.)
`columns_in_tree`	Columns stored at non-leaf (tree) level for navigation
`columns_in_leaf`	Columns stored at leaf level (data + pointers)

Interpreting the Results

The usage stats tell you how SQL Server actually uses each index. Here's how to read the key patterns:

Healthy index

High seeks, low scans, moderate writes: The optimizer is using this index for targeted lookups. This is exactly what a nonclustered index is for.

Unused index (removal candidate)

Zero seeks + zero scans + zero lookups, but active writes: Every INSERT, UPDATE, and DELETE on the table has to maintain this index, but no query ever benefits from it. The Unused Index Detection tool flags these automatically.

Scan-heavy index (possible key order issue)

High scans, low seeks: The optimizer is scanning the entire index rather than seeking to specific rows. This often means the leading key column doesn't match the query's WHERE clause, or the index is being used as a "narrow table scan" (cheaper than scanning the clustered index when fewer columns are needed).

Write-heavy index (negative ReadWriteDiff)

ReadWriteDiff is negative: This index costs more to maintain than it saves in reads. Not necessarily bad (a unique constraint must exist regardless of read count), but worth investigating if it's not a unique or primary key.

Key lookup magnet

High lookups on the clustered index: Nonclustered index seeks are frequently followed by key lookups back to the clustered index for missing columns. The Key Lookup Elimination tool identifies which columns to add as INCLUDEs to eliminate these.

Output Modes

Mode	Value	Description
Default	`@Output = 0`	Full detail for one table in the current database: structure, usage stats, sizes, tree/leaf columns
Cross-database	`@Output = 1`	Usage stats for the same table across all user databases on the server. Useful for multi-tenant environments where the same schema exists in every database
Batch insert	`@Output = 2`	Same as mode 0 but inserts into `DBA_Utilities.dbo.IndexUsageTemp` instead of returning a result set. Used for automated collection across databases

Parameters

Parameter	Default	Description
`@TableName`	(required)	Table name. Can include schema prefix (`'dbo.MyTable'`)
`@IndexName`	`''`	Filter to a specific index name. Empty string = all indexes on the table
`@Output`	`0`	Output mode: 0 = full detail, 1 = cross-database, 2 = batch insert to staging table

IndexUsageTemp Staging Table

The DBA_Utilities.dbo.IndexUsageTemp table is used only with @Output = 2 for batch collection scenarios. It mirrors the output columns of mode 0, with an additional DatabaseName column and an InsertDate timestamp. This is useful when:

Collecting index usage data from all databases on a server into one table for analysis
Building historical snapshots of index usage patterns over time
Feeding data into the suite's other analysis tools

The table is optional — it only needs to exist if you use @Output = 2.

Installation

All three procs must be installed in master and marked as system objects. This is what allows them to be called from any database context without a three-part name — the same way sp_help and sp_helpindex work.

Install order matters — each proc depends on the previous one:

sp_ExposeColsInIndexLevels — no dependencies
sp_helpindex2 — depends on #1
sp_helpindexUsage — depends on #2

-- Install in master (run in order)
USE master;
GO

-- 1. Foundation: tree/leaf column analysis
:r sp_exposecolsinindexlevels.sql
EXEC sp_MS_marksystemobject 'sp_ExposeColsInIndexLevels';

-- 2. Enhanced sp_helpindex
:r sp_helpindex2.sql
EXEC sp_MS_marksystemobject 'sp_helpindex2';

-- 3. Usage stats wrapper
:r sp_helpindexUsage.sql
EXEC sp_MS_marksystemobject 'sp_helpindexUsage';

-- 4. Optional: staging table for batch mode
USE DBA_Utilities;
:r IndexUsageTemp.sql

Integration with the Suite

Every recommendation tool in the suite includes sp_helpindexUsage references in its output. The standard workflow is:

Run the suite — get recommendations
Review each table — run the commented sp_helpindexUsage line to see the full index picture
Make an informed decision — the recommendation plus the full index context tells you whether to proceed

For example, the suite might recommend adding [status] as an INCLUDE to an existing index. Running sp_helpindexUsage on that table might reveal that another index already has [status] as a key column — meaning the recommendation might be unnecessary, or the two indexes should be consolidated instead.

When @RunnableUnder is set: The suite comments out the sp_helpindexUsage calls so the output script is executable without stopping. When @RunnableUnder is NULL, the calls are active — you can run each one inline as you review the script.

Usage Examples

-- Full index detail for a table (run from target database)
USE MyDatabase;
EXEC sp_helpindexUsage 'dbo.orders';

-- Filter to a specific index
EXEC sp_helpindexUsage 'dbo.orders', @IndexName = 'IX_orders_customer_id';

-- Cross-database: see how the same table is indexed across all databases
-- Useful in multi-tenant environments
EXEC sp_helpindexUsage 'dbo.orders', @Output = 1;

-- Batch collect into staging table for offline analysis
EXEC sp_helpindexUsage 'dbo.orders', @Output = 2;
SELECT * FROM DBA_Utilities.dbo.IndexUsageTemp ORDER BY InsertDate DESC;

-- Quick check: find indexes with zero reads and high writes (removal candidates)
EXEC sp_helpindexUsage 'dbo.orders';
-- Look for rows where UserReads = 0 and UserWrites > 1000

-- Quick check: find indexes with high scans (possible key order issue)
EXEC sp_helpindexUsage 'dbo.orders';
-- Look for rows where UserScans >> UserSeeks

Attribution

sp_helpindex2 and sp_ExposeColsInIndexLevels are by Kimberly L. Tripp and Paul S. Randal of SQLskills.com. sp_helpindexUsage is a custom wrapper built on top of their work, adding DMV usage stats and size data.