SQL Server - Index usage

Check how are your indexes doing in the database. This script is very useful when you are doing some performance optimizations by creating new indexes. Or trying to find some good adepts for indexes that are slowing SQL Server when used more in insert/update/delete statements rather than for seeks and scans. So you can use it to verify you theory, and prove that index you created is really helpful.

You can also see time when index was used by any operation.

TIP: Extend filtering in where clause to focus as much as possible to set of indexes that are important for you.

	@@SERVERNAME AS [ServerName]
	,DB_NAME() AS [DatabaseName]
	,SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName]
	,[sObj].[name] AS [ObjectName]
		WHEN [sObj].[type] = 'U' THEN 'Table'
		WHEN [sObj].[type] = 'V' THEN 'View'
		END AS [ObjectType]
	,[sIdx].[index_id] AS [IndexID]
	,ISNULL([sIdx].[name], 'N/A') AS [IndexName]
		WHEN [sIdx].[type] = 0 THEN 'Heap'
		WHEN [sIdx].[type] = 1 THEN 'Clustered'
		WHEN [sIdx].[type] = 2 THEN 'Nonclustered'
		WHEN [sIdx].[type] = 3 THEN 'XML'
		WHEN [sIdx].[type] = 4 THEN 'Spatial'
		WHEN [sIdx].[type] = 5 THEN 'Reserved for future use'
		WHEN [sIdx].[type] = 6 THEN 'Nonclustered columnstore index'
	END AS [IndexType]
	,[sdmvIUS].[user_seeks] AS [TotalUserSeeks]
	,[sdmvIUS].[user_scans] AS [TotalUserScans]
	,[sdmvIUS].[user_lookups] AS [TotalUserLookups]
	,[sdmvIUS].[user_updates] AS [TotalUserUpdates]
	,[sdmvIUS].[last_user_seek] AS [LastUserSeek]
	,[sdmvIUS].[last_user_scan] AS [LastUserScan]
	,[sdmvIUS].[last_user_lookup] AS [LastUserLookup]
	,[sdmvIUS].[last_user_update] AS [LastUserUpdate]
	,[sdmfIOPS].[leaf_insert_count] AS [LeafLevelInsertCount]
	,[sdmfIOPS].[leaf_update_count] AS [LeafLevelUpdateCount]
	,[sdmfIOPS].[leaf_delete_count] AS [LeafLevelDeleteCount]
FROM [sys].[indexes] AS [sIdx]
	INNER JOIN [sys].[objects] AS [sObj] ON [sIdx].[object_id] = [sObj].[object_id]
	LEFT JOIN [sys].[dm_db_index_usage_stats] AS [sdmvIUS] ON [sIdx].[object_id] = [sdmvIUS].[object_id] AND [sIdx].[index_id] = [sdmvIUS].[index_id] AND [sdmvIUS].[database_id] = DB_ID()
	LEFT JOIN [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) AS [sdmfIOPS] ON [sIdx].[object_id] = [sdmfIOPS].[object_id] AND [sIdx].[index_id] = [sdmfIOPS].[index_id]
WHERE [sObj].[type] IN ('U', 'V') -- Look in Tables & Views
	AND [sObj].[is_ms_shipped] = 0x0 -- Exclude System Generated Objects
	AND [sIdx].[is_disabled] = 0x0 -- Exclude Disabled Indexes


Script results screen:

Results screen

Script messages after execution:

("n" rows affected)

Script results detailed description:

Column nameData typeDescription
ServerNamenvarcharName of the local server that is running SQL Server.
DatabaseNamenvarchar(128)Name of a specified database
SchemaNamesysnameSchema name associated with a schema ID
ObjectNamesysnameObject name
ObjectTypenvarcharReturning only object types 'Table' and 'View'. But that data constructed in CASE statement from underlying table [sys].[objects] column [type]
IndexIDintD of the index. index_id is unique only within the object. 0 = Heap, 1 = Clustered index, > 1 = Nonclustered index
IndexNamesysnameName of the index. name is unique only within the object. NULL = Heap
IndexTypenvarcharReturning possible index types in its textual specification name. But that data constructed in CASE statement from underlying table [sys].[indexes] column [type]
TotalUserSeeksbigintNumber of seeks by user queries.
TotalUserScansbigintNumber of scans by user queries that did not use 'seek' predicate.
TotalUserLookupsbigintNumber of bookmark lookups by user queries.
TotalUserUpdatesbigintNumber of updates by user queries. This includes Insert, Delete, and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count increments by 1
LastUserSeekdatetimeTime of last user seek
LastUserScandatetimeTime of last user scan.
LastUserLookupdatetimeTime of last user lookup.
LastUserUpdatedatetimeTime of last user update.
LeafLevelInsertCountbigintCumulative count of leaf-level inserts.
LeafLevelUpdateCountbigintCumulative count of leaf-level updates.
LeafLevelDeleteCountbigintCumulative count of leaf-level deletes. leaf_delete_count is only incremented for deleted records that are not marked as ghost first. For deleted records that are ghosted first, leaf_ghost_count is incremented instead.

Leave a Reply