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.
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 | 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 ( ServerName,CheckDate,DatabaseName,SchemaName,TableName,IndexName,IndexId,ObjectType,IndexDefinition,KeyColumnNamesWithSort,CountKeyColumns ,IncludeColumnNames,CountIncludedColumns,SecretColumns,CountSecretColumns,PartitionKeyColumnName,FilterDefinition,IsIndexedView,IsPrimaryKey ,IsXML,IsSpatial,IsNCColumnstore,IsCXColumnstore,IsDisabled,IsHypothetical,IsPadded,[FillFactor],IsReferencedByForeignKey,LastUserSeek,LastUserScan ,LastUserLookup,LastUserUpdate,TotalReads,UserUpdates,ReadsPerWrite,IndexUsageSummary,PartitionCount,TotalRows,TotalReservedMB,TotalReservedLOBMB ,TotalReservedRowOverflowMB,IndexSizeSummary,TotalRowLockCount,TotalRowLockWaitCount,TotalRowLockWaitInMs,AvgRowLockWaitInMs,TotalPageLockCount ,TotalPageLockWaitCount,TotalPageLockWaitInMs,AvgPageLockWaitInMs,TotalIndexLockPromotionAttemptCount,TotalIndexLockPromotionCount,DataCompressionDesc ,PageLatchWaitCount,PageLatchWaitInMs,PageIoLatchWaitCount,PageIoLatchWaitInMs,CreateDate,ModifyDate,MoreInfo,DisplayOrder ) SELECT server_name, run_datetime,database_name,schema_name,table_name,index_name,index_id,object_type,index_definition,key_column_names_with_sort_order,count_key_columns ,include_column_names,count_included_columns,secret_columns,count_secret_columns,partition_key_column_name,filter_definition,is_indexed_view,is_primary_key ,is_XML,is_spatial,is_NC_columnstore,is_CX_columnstore,is_disabled,is_hypothetical,is_padded,fill_factor,is_referenced_by_foreign_key,last_user_seek,last_user_scan ,last_user_lookup,last_user_update,total_reads,user_updates,reads_per_write,index_usage_summary,partition_count,total_rows,total_reserved_MB,total_reserved_LOB_MB ,total_reserved_row_overflow_MB,index_size_summary,total_row_lock_count,total_row_lock_wait_count,total_row_lock_wait_in_ms,avg_row_lock_wait_in_ms,total_page_lock_count ,total_page_lock_wait_count,total_page_lock_wait_in_ms,avg_page_lock_wait_in_ms,total_index_lock_promotion_attempt_count,total_index_lock_promotion_count ,data_compression_desc,page_latch_wait_count,page_latch_wait_in_ms,page_io_latch_wait_count,page_io_latch_wait_in_ms,create_date,modify_date,more_info,display_order FROM ##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.
1 2 3 4 | SELECT TOP 1 TableName, IndexName, CheckDate, TotalReads, SUBSTRING(IndexUsageSummary, CHARINDEX('Writes:', IndexUsageSummary) + 7, 20) AS TotalWrites, TotalRows, TotalReservedMB FROM BlitzIndex_Mode2 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.
9 Comments. Leave new
I’m not sure if you guys are in my office, listening to my current issues or if we’re just all in the same boat 🙂 I’ve been having some discussions on tracking exactly this, because we have some tables with huge unused indexes, and I’ve been told to get 12 months worth of data before deleting it. Stealing this, and claiming credit in 5… 4…
I’m in your head! Maybe you’re John Malkovich.
[…] Tara Kizer has a warning for people eager to drop “unused” indexes: […]
i’ve been dropping unused indexes for years and when i did my searches i always searched by user_seeks=0 and made sure the server had been up for at least a month and the table the index was on had been created longer than 90 days or so ago. We had replicated databases with different indexes and a replication resync would recreate the table and deleting an index on those could be trouble
indexes with low usage, i’d delete after 180 days or so
FWIW Microsoft fixed the wiping out index usage details when doing rebuild. It doesn’t happen in 2016 or 2017. And in 2012, it is fixed in CU3 of sp3. In 2014 in CU 8 of sp1 and cu 14 of RTM. (I didn’t verify those CU/SP levels myself for 2012 and 2014, from documentation. 🙂 )
Well spotted saved a lot of work!
“…on a table that had 2 billion rows. The database was around 7TB in size. We were having storage and performance issues.”
So … how long did it take to put the index back on a 7TB database, a table with 2 billion rows and existing storage/performance issues? And did you work there after the index was re-created? 🙂
We didn’t end up adding that index back to the system. Instead, we moved the report to another server where there was an automated restore from prod. Didn’t make sense for that report to run on production if it was only monthly, and it didn’t need realtime data.
Some years ago, a problem occured when I dropped a unique non-clustered index which was not used.