January 15, 2015 at 4:47 am
Hi, Can you help me to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.
January 15, 2015 at 4:56 am
Type those very words into your favourite search engine. You'll get plenty of results.
John
January 15, 2015 at 6:07 am
This is the DMV you need, sys.dm_db_index_usage_stats. As for querying it, it's really straight forward. You can join to other system views to get the table & index names. The main point to understand is that this data is only since the last time the server was restarted. Also, if a database is disconnected this information gets reset. This means you can't assume that it's perfect. If you restarted your server on December 1st and an index was used for an import query on November 28, it won't be here. I suggest keeping a script of all the indexes you intend to drop in order to be able to quickly recover them in the event they're needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply