finding mostly used indexes on a database

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 343
USE DatabaseName 
GO
SELECT Top 100 object_name(a.object_id) AS table_name,
COALESCE(name, 'table with no clustered index') AS index_name,
type_desc AS index_type,
user_seeks,
user_scans,
user_lookups,
user_updates
,a.object_id
FROM sys.dm_db_index_usage_stats a INNER JOIN
sys.indexes b ON a.index_id = b.index_id AND a.object_id = b.object_id
WHERE database_id = DB_ID('DatabaseName ') AND a.object_id > 1000
ORDER BY user_seeks desc, user_scans desc


This results help you to analyse the index usage on a database and do the necessary action on less used or unused indexes...

Comments or Responses

Login to post response