Is it really unused index?

Question

Is it really unused index?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
April 8, 2013 at 1:15 am

#291780

Hi,
Can you confirm, Is it really unused index?
UserSeek - 16951
UserScans - 11803
UserLookup- 0
UserUpdates - 62992
rgds
ananda

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Since there's a non-zero number of seeks and scans, no that is not unused (it's been used for seeks and scans)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Thanks for reply.. some of the index result showing as below, it is treated as used index?

Index A

UserSeek - 193121

UserScans - 0

UserLookup- 0

UserUpdates - 49183

Index B

UserSeek - 0

UserScans - 0

UserLookup- 0

UserUpdates - 128920

Seraj Alam-256815 SSC Eights! Points: 804 More actions · Answer 3

Hi,

Index A is indeed in use as Gila suggested earlier.

Index B not being used for query, but it may be important if it is clustered index or Primary Key.

I use below query to evaluate how usefull is my index.

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,

user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],

user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

AND i.index_id = s.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND s.database_id = DB_ID()

AND user_updates > (user_seeks + user_scans + user_lookups)

AND i.index_id > 1

ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Bear in mind that those views are only since the last time SQL was started, not all time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 5

Also, be aware that some indexes exists for maintain constraints - unique indexes. And it could looks like unused...