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.
SELECT
@@SERVERNAME AS [ServerName]
,DB_NAME() AS [DatabaseName]
,SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName]
,[sObj].[name] AS [ObjectName]
,CASE
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]
,CASE
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
Results
Script results screen:
Script messages after execution:
("n" rows affected)
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| ServerName | nvarchar | Name of the local server that is running SQL Server. |
| DatabaseName | nvarchar(128) | Name of a specified database |
| SchemaName | sysname | Schema name associated with a schema ID |
| ObjectName | sysname | Object name |
| ObjectType | nvarchar | Returning only object types 'Table' and 'View'. But that data constructed in CASE statement from underlying table [sys].[objects] column [type] |
| IndexID | int | D of the index. index_id is unique only within the object. 0 = Heap, 1 = Clustered index, > 1 = Nonclustered index |
| IndexName | sysname | Name of the index. name is unique only within the object. NULL = Heap |
| IndexType | nvarchar | Returning possible index types in its textual specification name. But that data constructed in CASE statement from underlying table [sys].[indexes] column [type] |
| TotalUserSeeks | bigint | Number of seeks by user queries. |
| TotalUserScans | bigint | Number of scans by user queries that did not use 'seek' predicate. |
| TotalUserLookups | bigint | Number of bookmark lookups by user queries. |
| TotalUserUpdates | bigint | Number 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 |
| LastUserSeek | datetime | Time of last user seek |
| LastUserScan | datetime | Time of last user scan. |
| LastUserLookup | datetime | Time of last user lookup. |
| LastUserUpdate | datetime | Time of last user update. |
| LeafLevelInsertCount | bigint | Cumulative count of leaf-level inserts. |
| LeafLevelUpdateCount | bigint | Cumulative count of leaf-level updates. |
| LeafLevelDeleteCount | bigint | Cumulative 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. |