(This script is part of my SQL Server Metrics Pack project that is now available on GitHub. If you like the code below and find it useful, I encourage you to check out the full project.)
Maintaining indexes can be an important part of a DBA’s job. Researching an index to determine how useful it is, or if it needs to be removed, is something I do regularly. Thankfully, SQL Server does a great job of keeping up with an index’s usage information behind the scenes, and you can find it by querying the built in sys.dm_db_index_usage_stats DMV. This DMV provides important information about your indexes, such as the number of seeks, scans, lookups, and updates the index has received. With this information a DBA can determine whether an index is used heavily, or not at all.
But guess what happens to all of that index info when SQL Server is restarted?
That’s right, it gets cleared out. Every time SQL Server is restarted the index usage stats get reset to zero.
But I don’t wanna lose my index usage stats!
Me either, that’s why I came up with the quick-and-dirty solution below. It stores the index usage stats for all indexes in a given database to a table, which will allow you to keep these stats. With each restart a new record is inserted for each index, allowing you to be able to aggregate these stats across restarts, while also being able to see the usage for the previously recorded periods.
Before we get started, I want to point out that there’s a bug in SQL Server 2012 and early versions of 2014 where index stats may get wiped out after an index rebuild. The below code does not account for that, so if you are affected by this bug you may not get accurate results. See Kendra Little’s Index Usage Stats Bug Fixed post for more info.
First, we need a table for storing our index stats:
|
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 |
CREATE TABLE [dbo].[IndexStats]( [ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [DatabaseName] [sysname] NOT NULL, [SchemaName] [nvarchar](128) NULL, [ObjectName] [sysname] NOT NULL, [IndexName] [sysname] NULL, [IndexID] [int] NOT NULL, [UserSeek] [bigint] NOT NULL, [UserScans] [bigint] NOT NULL, [UserLookups] [bigint] NOT NULL, [UserUpdates] [bigint] NOT NULL, [TableRows] [bigint] NULL, [IndexSizeMB] [bigint] NULL, [DropStatement] [nvarchar](2000) NULL, [Count] [int] NULL, [SQLServerStartTime] [datetime] NULL, [HoursOnline] [int] NULL, [DateInitiallyChecked] [datetime] NOT NULL, [DateLastChecked] [datetime] NOT NULL, [LastUserSeek] [datetime] NULL, [LastUserScan] [datetime] NULL, [LastUserLookup] [datetime] NULL, [LastUserUpdate] [datetime] NULL, [isUnique] [bit] NOT NULL, [FillFactor] [tinyint] NOT NULL, [isDisabled] [bit] NOT NULL ) |
Now, we need a way to populate our table. I chose to use a stored procedure for this so that you can easily specify the database you want to store index stats for. It uses a simple query that gathers stats from several DMVs, and also generates a DROP statement for each index. (Why not, right?) It will then do a MERGE with our new IndexStats table, basing the MERGE on the generated DROP statement and current SQL Server start date/time. If there is a match, it updates the stats, if not it creates a new record. I toyed with the idea of using a CHECKSUM or HASHBYTES to base the MERGE on, but so far this has worked pretty well so I’m not in a hurry to change it. Also, this procedure only checks nonclustered indexes, and excludes clustered indexes, primary keys and unique indexes. That’s just my personal preference, you should be able to easily change that by editing the filters in the query’s WHERE clause.
|
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 |
CREATE PROCEDURE [dbo].[loadIndexStats] @DBName sysname AS /************************************************************************** Author: Eric Cobb - https://sqlnuggets.com/blog/persist-and-aggregate-index-stats-across-server-restarts Purpose: This stored procedure will be used to collect metrics from multiple DMVs over time to determine which indexes in the specified database are useful. Due to the DMVs being refreshed with each restart of SQL Server, data is persisted in a database. This procedure excludes clustered indexes, primary keys and unique indexes. ***************************************************************************/ SET NOCOUNT ON --The following is dynamic so that this stored procedure can reside in any database. --Capture server uptime DECLARE @sql varchar(max) DECLARE @crlf NCHAR(2) SET @crlf = NCHAR(13)+NCHAR(10) SET @sql = ' USE '+ @DBName +' DECLARE @sqlserver_start_time datetime, @HoursOnline int SELECT @sqlserver_start_time = sqlserver_start_time, @HoursOnline = datediff(hh,sqlserver_start_time, getdate()) from sys.dm_os_sys_info' + @crlf --create the MERGE statement SET @sql = @sql + ' MERGE INTO dbo.IndexStats AS Target USING ( SELECT DB_NAME() AS DatabaseName ,SCHEMA_NAME(s.schema_id) AS SchemaName ,OBJECT_NAME(i.OBJECT_ID) AS ObjectName ,i.name AS IndexName ,i.index_id AS IndexID ,COALESCE(ius.user_seeks,-1) AS UserSeek ,COALESCE(ius.user_scans,-1) AS UserScans ,COALESCE(ius.user_lookups,-1) AS UserLookups ,COALESCE(ius.user_updates,-1) AS UserUpdates ,p.TableRows ,CASE WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages) ELSE 0 END * 8 / 1024 AS indexsizeMB , ''DROP INDEX '' + QUOTENAME(i.name) + '' ON '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) AS DropStatement ,1 AS [Count] ,@sqlserver_start_time AS SQLServerStartTime ,@HoursOnline AS hoursOnline ,GetDate() AS DateChecked ,ius.last_user_seek AS LastUserSeek ,ius.last_user_scan AS LastUserScan ,ius.last_user_lookup AS LastUserLookup ,ius.last_user_update AS LastUserUpdate ,i.is_unique AS isUnique ,i.fill_factor AS [FillFactor] ,i.is_disabled AS isDisabled FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats ius ON ius.index_id = i.index_id AND ius.OBJECT_ID = i.OBJECT_ID INNER JOIN (SELECT sch.name, sch.schema_id, o.OBJECT_ID, o.create_date FROM sys.schemas sch INNER JOIN sys.objects o ON o.schema_id = sch.schema_id) s ON s.OBJECT_ID = i.OBJECT_ID LEFT JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p ON p.index_id = i.index_id AND i.OBJECT_ID = p.OBJECT_ID LEFT JOIN (SELECT OBJECT_ID, index_id, SUM(used_page_count) AS usedpages, SUM(CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END) AS pages FROM sys.dm_db_partition_stats GROUP BY object_id, index_id) AS ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id WHERE OBJECTPROPERTY(i.OBJECT_ID,''IsUserTable'') = 1 AND (ius.database_id = DB_ID() OR ius.database_id IS NULL) AND i.type_desc = ''nonclustered'' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 ) AS Source ([DatabaseName],[SchemaName], [ObjectName], [IndexName], [IndexId], [UserSeek], [UserScans], [UserLookups], [UserUpdates], [TableRows], [IndexSizeMB], [DropStatement], [Count], [SQLServerStartTime], [hoursOnline], [DateChecked], [LastUserSeek], [LastUserScan], [LastUserLookup], [LastUserUpdate], [isUnique], [FillFactor], [isDisabled]) ON ( Target.DropStatement = Source.DropStatement AND Target.SQLServerStartTime = Source.SQLServerStartTime) WHEN NOT MATCHED THEN INSERT ( [DatabaseName], [SchemaName] , [ObjectName] , [IndexName] , [IndexID] , [UserSeek] , [UserScans] , [UserLookups] , [UserUpdates] , [TableRows] , [indexsizeMB] , [DropStatement] , [Count], [SQLServerStartTime] , [HoursOnline] , [DateInitiallyChecked], [DateLastChecked], [LastUserSeek], [LastUserScan], [LastUserLookup], [LastUserUpdate], [isUnique], [FillFactor], [isDisabled] ) VALUES ([DatabaseName], [SchemaName], [ObjectName], [IndexName], [IndexId], [UserSeek], [UserScans], [UserLookups], [UserUpdates], [TableRows], [IndexSizeMB], [DropStatement], [Count], [SQLServerStartTime], [hoursOnline], [DateChecked], [DateChecked],[LastUserSeek],[LastUserScan],[LastUserLookup],[LastUserUpdate],[isUnique],[FillFactor],[isDisabled]) WHEN MATCHED THEN UPDATE SET target.[UserSeek] = source.UserSeek, target.[UserScans] = source.UserScans, target.[UserLookups] = source.UserLookups, target.[UserUpdates] = source.UserUpdates, target.[TableRows] = source.TableRows, target.[IndexSizeMB] = source.IndexSizeMB, target.[Count] = target.[Count] + 1, target.[DateLastChecked] = GetDate(), target.HoursOnline = @HoursOnline, target.[LastUserSeek] = source.[LastUserSeek], target.[LastUserScan] = source.[LastUserScan], target.[LastUserLookup] = source.[LastUserLookup], target.[LastUserUpdate] = source.[LastUserUpdate], target.[isUnique] = source.[isUnique], target.[FillFactor] = source.[FillFactor], target.[isDisabled] = source.[isDisabled] ; ' --run the generated T-SQL EXEC(@sql) GO |
Once you have the procedure created, all you have to do is call it and pass in the database you want to track index stats for.
|
1 2 3 |
EXEC [dbo].[loadIndexStats] YourDBName |
I recommend putting this procedure and the above table somewhere neutral, I have a “DBA” database on each server where I put things like this. That way you can use it to track multiple databases and all of the information is stored in one place.
So What Do You Think?
This is still a work in progress, so I would be very interested in hearing if anyone uses this and what your experience with it is. As with anything, I’m sure other people will run into situations that I haven’t encountered or designed for. I’m also working on a view that will aggregate the data that is gathered, I just haven’t gotten it fine tuned yet. Look for it in a future post.
Pingback: Random Blogging Challenge Results - Grant Fritchey
Pingback: SQL Scripts: How To Find Index Usage - SQL Nuggets
Pingback: SQL Scripts: How To Find Missing Indexes - SQL Nuggets
Thanks for this script, however when I set up the table and store procedure and execute I got the error message: procedure loadindexstats has no parameters and arguments were supplied
It sound like you’re not specifying the database parameter in your stored procedure call.
You should take a look at my Index Metrics Pack on github, it has more updated functionality:
https://github.com/ericcobb/SQL-Server-Metrics-Pack