Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted June 4, 2018

Indexes That Have Not Been Used but Are Being Updated

By Greg Larsen

We all know indexes are important for improving your query performance, but to store and maintain indexes SQL Server requires disk space and compute resources.  If you have indexes that are not being used, they are costing you valuable resources and causing your update statements to run longer.  Those updates run longer because they have to maintain those unused indexes.  If your application is not using an index, then there is no value in having that index.

Periodically you should review your index usage statistics to determine how your indexes are being used.  You can do that by using the sys.dm_db_index_usage_stats dynamic management view (DMV).  This view will show you if your indexes are being used in a seek, scan, or lookup operation, and whether or not the indexes have been updated.  Remember DMVs only track information since SQL Server started.  Therefore, you need to consider running this DMV after SQL Server has been up for a reasonable time in order to get an accurate picture of how your indexes have been used.

By using the sys.dm_db_index_usage_stats you can identify those indexes that have never been used.  In the following code there are two SELECT statements with a UNION clause between them.  The first SELECT statement identifies those indexes that have never been used but have been update in your databases.  The second SELECT statement identifies those indexes that have never been used and have not been updated.  By “updated” I mean the index has either been maintained due to an INSERT, UPDATE or a DELETE operation.  You should consider running this query periodically to identify those indexes that are providing your application no value but are costing you compute and disk space resources. Once you know what indexes are not being used, then you can determine whether or not you even need those indexes.

-- indexes that have been updated and not used
select SCHEMA_NAME(o.schema_id) as [schema_name], 
       OBJECT_NAME(s.object_id) table_name,
       i.name index_name, 
       'yes' Index_updated_but_not_used
from sys.dm_db_index_usage_stats s
join sys.objects o
on s.object_id = o.object_id
join sys.indexes i
on s.index_id = i.index_id
and s.object_id = i.object_id
(s.user_seeks = 0
  and s.user_scans = 0
  and s.user_lookups = 0) 
and OBJECTPROPERTY(o.object_id,'IsUserTable') = 1
-- indexes that have not been updated or used 
       SCHEMA_NAME(o.schema_id) as [schema_name], 
       OBJECT_NAME(o.object_id) table_name,
       i.name index_name, 
       0  as user_seeks, 
       0 as user_scans, 
       0 as user_lookups,
       0 as user_updates,
       'no' as Index_updated_but_not_used
sys.indexes i
sys.objects o
on i.object_id = o.object_id
  i.index_id NOT IN (
SELECT s.index_id
FROM sys.dm_db_index_usage_stats s
WHERE s.object_id = i.object_id
AND s.index_id = i.index_id
AND s.database_id =  DB_ID(DB_NAME()))
and OBJECTPROPERTY(o.object_id,'IsUserTable') = 1
order by Index_updated_but_not_used desc;

See all articles by Greg Larsen

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM