August 30, 2011 at 4:36 am
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
August 30, 2011 at 4:43 am
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.
August 30, 2011 at 5:00 am
[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
August 30, 2011 at 6:32 am
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
August 30, 2011 at 6:50 am
you can go through this links
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/d06a001f-0f72-4679-bc2f-66fff7958b86.htm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply