script for Unused index

Question

script for Unused index

SQL Galaxy

One Orange Chip

Points: 29472
More actions
August 30, 2011 at 4:36 am

#256479

Hi,
SELECT o.name, indexname=i.name, i.index_id, reads=user_seeks + user_scans + user_lookups,
writes = user_updates, rows = (SELECT SUM(p.rows) FROM sys.partitions p
WHERE p.index_id = s.index_id AND s.object_id = p.object_id),
CASE WHEN s.user_updates < 1 THEN 100
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates END
AS reads_per_write, 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.'
+ QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o on s.object_id = o.object_id INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'AND i.is_primary_key = 0 AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id
AND s.object_id = p.object_id) > 10000 ORDER BY reads
I got from http://www.databasejournal.com/features/mssql/sql-server-index-t-sql-statements.html
This script mention one column name reads_per_write , actually what exact mention this column?. it always display value 100. Pl suggestion.
Thanks

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

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

if you look at the query its clear that the reason you get the value 100 is because the user_updates column is always less than 1

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications.

Jayanth Kurup[/url]

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

[The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications.[/quote]

Thanks for reply..

user_updates counter indicates the level of maintenance due to DML operation, ok. But as you mention this which index are used only lightly by your application.

I am getting confused, it means read_per_write colum display value 100 and lightly using those index, Not ever used those index

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

hi,

I have attached unused index three report for using three different script. all are showing different values, Could anyone suggestion me which is the best script and go for it.

Thanks

deepak.a SSCertifiable Points: 5330 More actions · Answer 4

you can go through this links

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/d06a001f-0f72-4679-bc2f-66fff7958b86.htm