During our Critical Care® sessions with clients, we often see unused indexes (reads=0) with high writes. Sometimes these unused indexes have very high writes. If an index is never being used to help with performance but SQL Server is having to maintain it for INSERT, UPDATE and DELETE operations, then why keep the index around?
Rarely does anyone have enough index usage data to make a decision of whether or not to drop the index. If you are on SQL Server 2012 or higher, index usage data is wiped out when you rebuild the respective index. In earlier versions, index usage data is wiped out when the SQL Server service stops.
What could possibly go wrong?
About 10 years ago, I decided to drop an unused index on a table that had 2 billion rows. The database was around 7TB in size. We were having storage and performance issues. I thought I could help the system out if I were to drop the index.
4 days after I dropped the index, I got a call from our NOC that CPU utilization was pegged at 100% for an hour so they were reaching out to the on-call DBA to check it out. I logged in and saw a query using A LOT of CPU. Its WHERE clause matched the index I had dropped. The execution plan showed it was scanning the table.
It turned out that I only had 2 weeks of uptime, which didn’t include the 1st of the month. The query that was causing the high CPU utilization was a report that ran on the 1st of each month.
Don’t be me. Learn from my mistake. Well, that should be plural. I’ve made lots of mistakes in production, including these two.
Saving the data
As the index usage gets wiped out with service restarts and index rebuilds, you should save the data on a periodic basis, such as daily. I’d also recommend saving the data prior to doing index maintenance.
Utilizing the Output option of sp_BlitzIndex when using @Mode=2, I wrote a script to track this data.
DECLARE @DatabaseName nvarchar(128) = 'StackOverflow'
IF OBJECT_ID('BlitzIndex_Mode2') IS NULL
CREATE TABLE dbo.BlitzIndex_Mode2
ID int IDENTITY(1,1) NOT NULL,ServerName nvarchar(128) NOT NULL,CheckDate datetimeoffset(7) NOT NULL,
DatabaseName nvarchar(128) NULL,SchemaName nvarchar(128) NULL,TableName nvarchar(128) NULL,
IndexName nvarchar(128) NULL,IndexId int NULL,ObjectType nvarchar(15) NULL,IndexDefinition nvarchar(4000) NULL,
KeyColumnNamesWithSort nvarchar(max) NULL,CountKeyColumns int NULL,IncludeColumnNames nvarchar(max) NULL,
CountIncludedColumns int NULL,SecretColumns nvarchar(max) NULL,CountSecretColumns int NULL,
PartitionKeyColumnName nvarchar(max) NULL,FilterDefinition nvarchar(max) NULL,IsIndexedView bit NULL,
IsPrimaryKey bit NULL,IsXML bit NULL,IsSpatial bit NULL,IsNCColumnstore bit NULL,IsCXColumnstore bit NULL,
IsDisabled bit NULL,IsHypothetical bit NULL,IsPadded bit NULL,[FillFactor] int NULL,
IsReferencedByForeignKey bit NULL,LastUserSeek datetime NULL,LastUserScan datetime NULL,
LastUserLookup datetime NULL,LastUserUpdate datetime NULL,TotalReads bigint NULL,UserUpdates bigint NULL,
ReadsPerWrite money NULL,IndexUsageSummary nvarchar(200) NULL,PartitionCount int NULL,TotalRows bigint NULL,
TotalReservedMB numeric(29, 2) NULL,TotalReservedLOBMB numeric(29, 2) NULL,
TotalReservedRowOverflowMB numeric(29, 2) NULL,IndexSizeSummary nvarchar(300) NULL,TotalRowLockCount bigint NULL,
TotalRowLockWaitCount bigint NULL,TotalRowLockWaitInMs bigint NULL,AvgRowLockWaitInMs bigint NULL,
TotalPageLockCount bigint NULL,TotalPageLockWaitCount bigint NULL,TotalPageLockWaitInMs bigint NULL,
AvgPageLockWaitInMs bigint NULL,TotalIndexLockPromotionAttemptCount bigint NULL,
TotalIndexLockPromotionCount bigint NULL,DataCompressionDesc nvarchar(4000) NULL,PageLatchWaitCount bigint NULL,
PageLatchWaitInMs bigint NULL,PageIoLatchWaitCount bigint NULL,PageIoLatchWaitInMs bigint NULL,
CreateDate datetime NULL,ModifyDate datetime NULL,MoreInfo nvarchar(500) NULL,DisplayOrder int NULL,
CONSTRAINT PK_BlitzIndex_Mode2 PRIMARY KEY CLUSTERED (ID ASC)
EXEC master.dbo.sp_BlitzIndex @Mode = 2, @DatabaseName = @DatabaseName, @OutputDatabaseName = 'tempdb', @OutputSchemaName = 'dbo', @OutputTableName = '##BlitzIndex_Mode2'
INSERT INTO BlitzIndex_Mode2
Making a decision
It is important to know your workload when deciding to drop an unused index. Are there any monthly, quarterly or annual reports? Is there any large task that doesn’t run very often? When was the last SQL Server restart? When was the index last rebuilt?
If I had known there was a monthly report and that the index usage data only went back 2 weeks, I would have waited until I had more than 31 days of index usage data.
Once you’ve collected enough index usage data for your system’s workload, consider dropping the indexes that haven’t been used in that time. Check the BlitzIndex_Mode2 table for the last time the index had any writes.
SELECT TOP 1 TableName, IndexName, CheckDate, TotalReads, SUBSTRING(IndexUsageSummary, CHARINDEX('Writes:', IndexUsageSummary) + 7, 20) AS TotalWrites, TotalRows, TotalReservedMB
WHERE TableName = 'TableName' AND IndexName = 'IndexName' AND IndexUsageSummary <> 'Reads: 0 Writes:0'
ORDER BY CheckDate DESC
You could get a lot fancier with the script, but this gets you started.
Brent says: SQL Server Agent has a way to kick off jobs when SQL Server starts. Wouldn’t it be cool to have a way to kick off jobs when the server was about to shut down? I’d totally add this script to catch index details before someone shuts the server down.