Starting with SQL Server 2005, Microsoft introduced Dynamic Management Views to help DBAs see what's going inside of SQL Server. One of my favorite is the sys.dm_db_index_usage_stats. It collects statistics for different types of index operations and the timestamp at which it was last performed. All the DMVs are great tools; however, they all have the same drawback. They are memory resident, which means the statistics are reset any time the SQL Server service is restarted, the database is attached/detached, or it's restored.
The best way to avoid losing this valuable data is to store it in a persistent table. I typically store data like this within the msdb database, but you can store in any database. Below we have created a table DBA.IndexUsageStats to hold each snapshot of the data.
USE msdb;
GO
CREATE SCHEMA dba AUTHORIZATIONdbo;
GO
CREATE TABLE dba.IndexUsageStats
(
SQLStartTime DATETIMENULL,
CaptureTime DATETIMENULL,
DatabaseName NVARCHAR(128) NULL,
ObjectName NVARCHAR(128) NULL,
index_name NVARCHAR(128) NULL,
index_type_desc NVARCHAR(60) NULL,
index_is_unique BIT NULL,
database_id SMALLINTNULL,
object_id INT NULL,
index_id INT NULL,
user_seeks BIGINTNULL,
user_scans BIGINTNULL,
user_lookups BIGINT NULL,
user_updates BIGINT NULL,
last_user_seek DATETIME NULL,
last_user_scan DATETIME NULL,
last_user_lookup DATETIME NULL,
last_user_update DATETIME NULL,
system_seeks BIGINT NULL,
system_scans BIGINT NULL,
system_lookups BIGINT NULL,
system_updates BIGINT NULL,
last_system_seek DATETIME NULL,
last_system_scan DATETIME NULL,
last_system_lookup DATETIME NULL,
last_system_update DATETIME NULL
);
GO
Next we need to create the collector. This query will pull the current data from the DMV and store it in our table.
DECLARE@CurrentStartTime AS DATETIME,
@PreviousStartTime AS DATETIME;
SELECT@CurrentStartTime = sqlserver_start_time
FROM master.sys.dm_os_sys_info;
USETestDatabase;
INSERT msdb.DBA.IndexUsageStats
SELECT@CurrentStartTime AS SQLStartTime,
CURRENT_TIMESTAMP ASCaptureTime,
Db_name() AS DatabaseName,
Object_name(ius.object_id) AS ObjectName,
i.name ASIndexName,
i.type_desc ASIndexTypeDesc,
i.is_unique ASIndexIsUnique,
ius.*
FROM sys.dm_db_index_usage_statsius
INNER JOIN sys.indexes i
ONius.object_id= i.object_id
ANDius.index_id =i.index_id
WHERE ius.database_id = Db_id()
ORDER BY ObjectName,
ius.index_id;
GO
The script can be scheduled with SQL Agent to run on a regular basis. I prefer a weekly schedule, but any recurring schedule is fine. If you have more than one user database, then you'll need to run this script for each one.
In the script we capture a bit more than just the DMV data. For example:
- SQLStartTime - The time when the SQL Server service started.
- CaptureTime - The time when the script captured a snapshot of the DMV data.
- DatabaseName, OjbectName, IndexName, IndexTypeDesc, IndexIsUnique are pretty self-explanatory.
Once you have let this job execute over a few weeks, you can then revisit the data and start seeing how your indexes are being utilized over a longer period of time. This query will aggregate the index statistics using the captured data.
USE msdb;
SELECT Min(SQLStartTime) AS SQLStartTime,
Max(CaptureTime) ASCaptureTime,
ObjectName,
IndexName,
IndexTypeDesc,
index_id,
Sum(user_seeks) ASuser_seeks,
Sum(user_scans) ASuser_scans,
Sum(user_lookups) AS user_lookups,
Sum(user_updates) AS user_updates
FROM DBA.IndexUsageStats
WHERE DatabaseName ='TestDatabase'
GROUP BY ObjectName,
index_name,
index_type_desc,
index_id
ORDER BY ObjectName,
index_id;
GO
These samples below were taken over a four month period and clearly show how the indexes have been utilized. It even points out some indexes (IX_ItemCodeIDand IX_PurchasedBy) that may be nothing more than overhead and could be candidates for removal. These two indexes have not been used for any seeks or scans, but SQL still needs to update them every time DML code is applied to the Purchases table.
As you can see, the more data you have to draw a conclusion about your index usage, the better you can make tuning recommendations for each of them.
