If you are aware with Index well or if you have read all my
previous articles related to Index, you might aware that Index can increase
the speed of SELECT statement but can reduce INSERT/UPDATE/DELETE performance
so it is better to remove Unused index, it will not only give benefit to INSERT/
UPDATE/ DELETE but it will free up some disk space resources too.
So, after this clarification you understand the requirement
of deleting unused Index, right? But how to find which index has never been
used? Well I have written one small snippet of TSQL for the same which I am
going to share with you.
Note: statistics you are going to see with below given
script, would be refreshed and start collecting data again from zero if you
restart you server instance or database. So, first decide your business cycle, let SQL
Server collect data and then run the following script to know exact situation
otherwise it may happen that some query runs regularly so you can see its stats
and few run only once or twice in a month or a quarter and you don’t see its
stats and based on that you drop the index which affect the query when it start
running at its regular time after a month or quarter.
Here is the code which I was talking about, earlier:
--following
query will show you which index is never used
select
ind.Index_id,
obj.Name as TableName,
ind.Name as IndexName,
ind.Type_Desc,
indUsage.user_seeks,
indUsage.user_scans,
indUsage.user_lookups,
indUsage.user_updates,
indUsage.last_user_seek,
indUsage.last_user_scan,
'drop index [' + ind.name + '] ON [' + obj.name + ']' as DropCommand
from
Sys.Indexes as ind JOIN Sys.Objects as obj on ind.object_id=obj.Object_ID
LEFT JOIN sys.dm_db_index_usage_stats
indUsage
ON
ind.object_id =
indUsage.object_id
AND ind.Index_id=indUsage.Index_id
where
ind.type_desc<>'HEAP' and obj.type<>'S'
AND objectproperty(obj.object_id,'isusertable') = 1
AND (isnull(indUsage.user_seeks,0)=0 AND isnull(indUsage.user_scans,0)=0 and isnull(indUsage.user_lookups,0)=0)
order by obj.name,ind.Name
--following
query will show you list of ALL index in database
--along with
data how many times it get seek, scan, lookup or update
select
ind.Index_id,
obj.Name as TableName,
ind.Name as IndexName,
ind.Type_Desc,
indUsage.user_seeks,
indUsage.user_scans,
indUsage.user_lookups,
indUsage.user_updates,
indUsage.last_user_seek,
indUsage.last_user_scan
from
Sys.Indexes as ind JOIN Sys.Objects as obj on ind.object_id=obj.Object_ID
LEFT JOIN sys.dm_db_index_usage_stats
indUsage
ON
ind.object_id =
indUsage.object_id
AND ind.Index_id=indUsage.Index_id
where
ind.type_desc<>'HEAP' and obj.type<>'S'
AND objectproperty(obj.object_id,'isusertable') = 1
order by obj.name,ind.Name
Be sure before droping any index, give it a second thought
before deleting it. This is usually a good practice if you are doing this on
production server.
if you want to refer all other articles related to index, click here.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile”
3 comments:
Syntax error in the second query. Drop the comma after the last column of the Select statement "indUsage.last_user_scan,"
Hello Daniel,
Thanks for your comment, it is corrected now.
This is very handy command to find the indexes which are not in use.
One should aware that this may also include indexes which may be used in future as this give result from last update statistics only.
Post a Comment