Capturing Index Usage Stats
Overview
Index usage stats are invaluable for evaluating performance of existing indexes. Every time an index is used by the query-engine, an internal table is updated reflecting that usage. So, every time a scan, seek, lookup, or update operation is completed, a counter is incremented for the affected index. Bravo, SQL Server 2008+! I can tell which indexes are being used, and which aren’t.
Problem is, they aren’t persisted by SQL Server across server reboots. With Windows Update ensuring your Windows servers get rebooted at least monthly for security patches, the stats captured by SQL Server are likely to be of limited utility. If you are uncertain which indexes can be dropped safely, you need a way to determine if any given index is being used. Unused indexes may be getting updated millions of times a day for no reason. It’s not enough to look at the sys.dm_db_index_usage_stats DMV for a couple of days, then drop all the “unused” indexes. It’s really important, for business-critical systems, to ensure you take a longer-term look at index usage first. This ensures you don’t remove an index that is only used rarely, but when it is used is imperative to performance.
Do *you* want to be the one to explain to the CEO why that month-end process is suddenly taking three days instead of three hours?
Solution
I wrote a script that can be scheduled in SQL Server Agent to capture index usage stats. These stats are inserted into a permanent table allowing analysis across multiple months, and even years.
I created a database to hold the index usage stats:
|
1 2 3 4 |
CREATE DATABASE StatsDB; ALTER DATABASE StatsDB SET RECOVERY SIMPLE; BACKUP DATABASE StatsDB TO DISK = N'NUL:'; GO |
The following script was scheduled using a SQL Server Agent job. The job is scheduled to run once every 6 hours. Existing rows are replaced every 6 hours unless the server was just rebooted, in which case a new row is created. This methodology creates the required history to track usage of each index:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 |
USE StatsDB; DECLARE @cmd nvarchar(max); DECLARE @DatabaseName SYSNAME; --this is the name of the database we're capturing index usage stats for: SET @DatabaseName = N'Test'; IF OBJECT_ID(N'dbo.IndexUsageStats', N'U') IS NULL BEGIN CREATE TABLE dbo.IndexUsageStats ( DatabaseID int NOT NULL , ObjectID int NOT NULL , IndexID int NOT NULL , RunDateTime datetime NOT NULL , user_seeks bigint , user_scans bigint , user_lookups bigint , user_updates bigint , system_seeks bigint , system_scans bigint , system_lookups bigint , system_updates bigint , TotalAllocatedMB bigint , PartitionCount bigint , SQLServerStartTime datetime , RowLastUpdated datetime CONSTRAINT IndexUsageStats_RowLastUpdated_DF DEFAULT (GETDATE()) , CONSTRAINT IndexUsageStats_pk PRIMARY KEY CLUSTERED (DatabaseID, ObjectID, IndexID, RunDateTime) WITH ( DATA_COMPRESSION = PAGE , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 100 ) ) ON [PRIMARY]; END SET @cmd = N'DECLARE @SQLServerStartTime datetime = ( SELECT TOP(1) dosi.sqlserver_start_time FROM sys.dm_os_sys_info dosi ); IF OBJECT_ID(N''tempdb..#RowsUpdated'', N''U'') IS NOT NULL DROP TABLE #RowsUpdated; CREATE TABLE #RowsUpdated ( RowNum int NOT NULL PRIMARY KEY CLUSTERED ); IF OBJECT_ID(N''tempdb..#IndexUsageStats'', N''U'') IS NOT NULL DROP TABLE #IndexUsageStats; CREATE TABLE #IndexUsageStats ( RowNum int NOT NULL IDENTITY(1,1) , DatabaseID int NOT NULL , ObjectID int NOT NULL , IndexID int NOT NULL , RunDateTime datetime NOT NULL , user_seeks bigint , user_scans bigint , user_lookups bigint , user_updates bigint , system_seeks bigint , system_scans bigint , system_lookups bigint , system_updates bigint , TotalAllocatedMB bigint , PartitionCount bigint , SQLServerStartTime datetime , PRIMARY KEY CLUSTERED (DatabaseID, ObjectID, IndexID, RunDateTime) WITH ( DATA_COMPRESSION = PAGE , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 100 ) );' SET @cmd = @cmd + N' INSERT INTO #IndexUsageStats ( DatabaseID , ObjectID , IndexID , RunDateTime , user_seeks , user_scans , user_lookups , user_updates , system_seeks , system_scans , system_lookups , system_updates , TotalAllocatedMB , PartitionCount , SQLServerStartTime ) SELECT DatabaseID = us.database_id , ObjectID = o.object_id , IndexID = i.index_id , RunDateTime = GETDATE() , user_seeks = COALESCE(us.user_seeks, 0) , user_scans = COALESCE(us.user_scans, 0) , user_lookups = COALESCE(us.user_lookups, 0) , user_updates = COALESCE(us.user_updates, 0) , system_seeks = COALESCE(us.system_seeks, 0) , system_scans = COALESCE(us.system_scans, 0) , system_lookups = COALESCE(us.system_lookups, 0) , system_updates = COALESCE(us.system_updates, 0) , TotalAllocatedMB = SUM(au.total_pages) * 8192E0 / 1048576 , PartitionCount = COUNT(p.partition_id) , SQLServerStartTime = @SQLServerStartTime FROM ' + QUOTENAME(@DatabaseName) + N'.sys.objects o INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.indexes i ON o.object_id = i.object_id INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas sc ON o.schema_id = sc.schema_id INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.dm_db_index_usage_stats us ON us.object_id = o.object_id AND us.index_id = i.index_id LEFT JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id LEFT JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.allocation_units au ON ( ((au.type = 1 or au.type = 3) AND au.container_id = p.hobt_id) OR (au.type = 2 AND au.container_id = p.partition_id) ) WHERE o.is_ms_shipped = 0 AND us.database_id = ' + CONVERT(nvarchar(5), DB_ID(@DatabaseName)) + N' GROUP BY us.database_id , o.object_id , i.index_id , COALESCE(us.user_seeks, 0) , COALESCE(us.user_scans, 0) , COALESCE(us.user_lookups, 0) , COALESCE(us.user_updates, 0) , COALESCE(us.system_seeks, 0) , COALESCE(us.system_scans, 0) , COALESCE(us.system_lookups, 0) , COALESCE(us.system_updates, 0) ORDER BY o.object_id , i.index_id;' SET @cmd = @cmd + N' UPDATE dbo.IndexUsageStats SET RunDateTime = t.RunDateTime , user_seeks = t.user_seeks , user_scans = t.user_scans , user_lookups = t.user_lookups , user_updates = t.user_updates , system_seeks = t.system_seeks , system_scans = t.system_scans , system_lookups = t.system_lookups , system_updates = t.system_updates , TotalAllocatedMB = t.TotalAllocatedMB , PartitionCount = t.PartitionCount , RowLastUpdated = GETDATE() OUTPUT t.RowNum INTO #RowsUpdated FROM #IndexUsageStats t INNER JOIN dbo.IndexUsageStats us ON t.DatabaseID = us.DatabaseID AND t.ObjectID = us.ObjectID AND t.IndexID = us.IndexID AND t.SQLServerStartTime = us.SQLServerStartTime WHERE us.user_seeks <> t.user_seeks OR us.user_lookups <> t.user_lookups OR us.user_scans <> t.user_scans OR us.user_updates <> t.user_updates OR us.system_lookups <> t.system_lookups OR us.system_scans <> t.system_scans OR us.system_seeks <> t.system_seeks OR us.system_updates <> t.system_updates;' SET @cmd = @cmd + N' DELETE FROM #IndexUsageStats WHERE EXISTS ( SELECT 1 FROM #RowsUpdated ru WHERE ru.RowNum = #IndexUsageStats.RowNum ); INSERT INTO dbo.IndexUsageStats ( DatabaseID , ObjectID , IndexID , RunDateTime , user_seeks , user_scans , user_lookups , user_updates , system_seeks , system_scans , system_lookups , system_updates , TotalAllocatedMB , PartitionCount , SQLServerStartTime ) SELECT DatabaseID , ObjectID , IndexID , RunDateTime , user_seeks , user_scans , user_lookups , user_updates , system_seeks , system_scans , system_lookups , system_updates , TotalAllocatedMB , PartitionCount , SQLServerStartTime FROM #IndexUsageStats iust WHERE NOT EXISTS ( SELECT 1 FROM dbo.IndexUsageStats ius WHERE ius.DatabaseID = iust.DatabaseID AND ius.ObjectID = iust.ObjectID AND ius.IndexID = iust.IndexID AND ius.SQLServerStartTime = iust.SQLServerStartTime ); '; EXEC sys.sp_executesql @cmd; GO |
I use the following query to get the list of index usage stats:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT DatabaseName = 'Test' , ObjectName = o.name , IndexName = i.name , IndexType = i.type_desc , iug.SQLServerStartTime , iug.RowLastUpdated , iug.PartitionCount , iug.TotalAllocatedMB , iug.user_lookups , iug.user_scans , iug.user_seeks , iug.user_updates , iug.system_lookups , iug.system_scans , iug.system_seeks , iug.system_updates FROM dbo.IDX_USG_STATS iug LEFT JOIN [Test].sys.objects o ON iug.ObjectID = o.object_id LEFT JOIN [Test].sys.indexes i ON iug.ObjectID = i.object_id AND iug.IndexID = i.index_id WHERE iug.DatabaseID = DB_ID(N'Test'); GO |
If you have SQL Server 2016+, I’d suggest looking into using SQL Server Query Store since it offers a lot more functionality. However, the script approach I show above is extremely light-weight, and creates only a tiny extra load on the live SQL Server instance. Query Store, not so much.
I hope you found this script useful! If you have any questions, please don’t hesitate to leave a comment below.
Check out our other posts on Performance, as well as our list of Tools for SQL Server.
You mention Query Store, and that can show plans that use indexes but I didn’t think it kept index usage stats. Do you know of a way to get usage stats from Query Store?
I’m away from my computer for a few days, but I’ll take a look once I get back, and update my post. Thanks for the question!