Are My SQL Server Indexes Being Used?

Published On: 2019-08-28By:

Indexes can be incredibly beneficial to your database performance; however, they do come with a cost—indexes both consume storage space and affect insert performance. Therefore, it is important as part of your index maintenance procedures that you periodically check to see if your indexes are being used. Many times, indexes are created in the belief they are needed but in fact they are never used. You can reduce that IO overhead on inserts when you remove unnecessary indexes.

How Do You Find Them?

Glenn Berry (B|T) has a fantastic script as part of his diagnostic scripts (link) that helps identify index utilization. In his diagnostic scripts it is listed as Script # 53.

— Possible Bad NC Indexes (writes > reads)  (Query 53) (Bad NC Indexes)

SELECT d.name,OBJECT_NAME(s.[object_id]) AS [Table Name], 
i.name AS [Index Name], i.index_id,i.is_disabled, i.is_hypothetical, 
i.has_filter, i.fill_factor,user_updates AS [Total Writes], 
user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference],
'USE ' + QUOTENAME(d.name) + '; ALTER INDEX ' + QUOTENAME(i.name) +' ON ' +OBJECT_NAME(s.[object_id]) +' DISABLE '; as statement
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
join sys.databases d on d.database_id=s.database_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
and i.name not like 'PK%' 
and i.name not like 'f%'
ORDER BY [Difference] DESC,
[Total Writes] DESC, 
[Total Reads] AS C OPTION (RECOMPILE);

Things to Consider in the Results

Simply WRITES> READS.

If you are writing to your index and not or infrequently reading (Seeks and Scans) from it, you may want to consider disabling then dropping. It takes a lot of resources to update indexes, store and maintain them, don’t waste resources when you don’t need to. Also, it’s important to note that these numbers are only as good as your last SQL Server service restart. Note the index usage statistics can also be reset if you are running SQL Server 2012 version below SP2+CU11 or SP3+CU3, by executing an ALTER INDEX REBUILD of that index. For SQL Server 2014 until SP2 the ALTER INDEX REBUILD of that index also applies.

You can see in the screen shot that there are  five indexes that are potential candidates for removal. We know this because the Total Writes column is significantly higher than the Total Reads. It’s just that simple. What is great about this script is that it combines user seeks, scans and lookups which makes it easy to see total reads. It also dynamicaly creates a disable script for you to use, if you choose to disable them.

I highly recommend you run this in your environment to see how your indexes are doing. When it comes to performance tuning, fixing wasted resources can make all the difference. But be careful we all have indexes that are only used during budget season or once a year reporting. I highly recommend scripting off all indexes before dropping them.


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link