Several problems of unused Index script

The script that used to download online has not used index such as SQL Server–2008–unused index script–download, but now it seems that this script has some problems.

The script is as follows:

--Unusedindex Script

--Original author:pinal Dave (C) 2011

Selecttop 25

O.name as ObjectName

, I.nameas IndexName

, I.index_idas IndexID

, Dm_ius.user_seeksas Userseek

, Dm_ius.user_scansas Userscans

, Dm_ius.user_lookupsas userlookups

, Dm_ius.user_updatesas userupdates

, P.tablerows

, ' DROP INDEX ' +quotename (i.name)

+ ' on ' +quotename (s.name) + '. ' +quotename (object_name) (Dm_ius. object_id)) As ' drop statement '

Fromsys.dm_db_index_usage_statsdm_ius

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

Innerjoinsys.indexes ION i.index_id= Dm_ius.index_idand Dm_ius. Object_id= i.object_id

Innerjoinsys.objects oon Dm_ius. Object_id= o.object_id

Innerjoinsys.schemas SON o.schema_id= s.schema_id

Innerjoin (Selectsum (p.rows) tablerows, p.index_id, p.object_id

Fromsys.partitions pgroupby p.index_id, p.object_id) p

On p.index_id= Dm_ius.index_idand Dm_ius. Object_id= p.object_id

Whereobjectproperty (Dm_ius. object_id, ' isusertable ') = 1

and dm_ius.database_id=db_id ()

and i.type_desc= ' nonclustered '

and i.is_primary_key= 0

and i.is_unique_constraint= 0

by (dm_ius.user_seeks+ dm_ius.user_scans+ dm_ius.user_lookups) ASC

Go

This script obtains the index usage by querying sys.dm_db_index_usage_stats. However, if the index is not used, it will not appear in the sys.dm_db_index_usage_stats. Only the index is used for the first time will insert a record in the Sys.dm_db_index_usage_stats, and then the information that the index is accessed will be updated to this table. This part of the data is ignored if you use the Innser join connection.

Of course, the index is created without a single use of the situation will be very small.