Query to find out Un-used Indexes in SQL Server

Hi Folks,

You all knows that to improve the seraching mechanism in SQL Server indexes plays a Vital role but sometimes to improve the performance its necessary to delete OR offline the existing indexes. The Indexes that are not frequently used OR a table that is having more writes compare than Reads then you should delete those indexes OR offline them , Becuase whenever you are performing a write statement then SQL Server also updates the index pages which requires resources to do that and its time consuming, result in bad Performance. Below is the query that will guide you to find out un-used indexes.


sch.name + ‘.’ + t.name AS [Table Name],
i.name AS [Index Name],
ISNULL(user_updates,0) AS [Total Writes],
ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads],
s.last_user_scan ,
ISNULL(user_updates,0) – ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference],
p.reserved_page_count * 8.0 / 1024 as SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK) ON s.object_id = i.object_id  AND i.index_id = s.index_id  AND s.database_id=db_id()  AND objectproperty(s.object_id,’IsUserTable’) = 1
INNER JOIN sys.tables AS t WITH (NOLOCK) ON i.object_id = t.object_id
INNER JOIN sys.schemas AS sch WITH (NOLOCK) ON t.schema_id = sch.schema_id
LEFT OUTER JOIN sys.dm_db_partition_stats AS p WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id
WHERE (1=1)
–AND ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) –shows all indexes including those that have not been used
–AND ISNULL(user_updates,0) – ISNULL((user_seeks + user_scans + user_lookups),0)>0 –only shows those indexes which have been used
–AND i.index_id > 1 — Only non-first indexes (I.E. non-primary key)
–AND i.is_primary_key<>1 — Only those that are not defined as a Primary Key)
–AND i.is_unique_constraint<>1 — Only those that are not classed as “UniqueConstraints”.
ORDER BY [Table Name], [index name]


Hope it will be usefull for you !!!



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: