Check unused index in SQL Sever 2008

Checking for unused indices across your database should also be a part of housekeeping. Usually i would schedule checking for unused indices on quarterly basis. Just to minimize surprises on a database suddenly timing out. The DMV for checking on usage and statistics is sys.dm_db_index_usage_stats. Here’s a script that i use most of the time and is on my quarterly maintenance plan then i just save the result to a table. 


SELECT object_name(i.object_id) as tableName, as indexName, s.object_id, s.user_seeks, s.user_scans
FROM sys.indexes i
JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id
AND i.index_id = s.index_id AND s.database_id = DB_ID()
WHERE objectproperty(i.object_id,’IsUserTable’) = 1
AND i.index_id > 0
AND i.is_Primary_Key = 0
AND i.is_unique_constraint = 0
AND i.is_unique = 0

The column for user_lookups and user_updates is also available in the DMV. You may add these columns for your additional checking. From the results, you can use your best judgement on which index is not used at all and you can proceed to drop. To drop an index from a table execute as below

DROP INDEX [indexnamehere] ON [tablenamehere]

After that

From above result, check If the column user_seeks is high, this means index needs tuning up. Ideally, you would want to maintain around 5-10 indices per table. When you do drop an index it is also ideal to monitor performance of the database to see if there was any negative effect via the SQL Profiler. Just to make sure.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali -

Meels on Wheels

Meels for breakfast, lunch and dinner.

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment