CREATE VIEW [dbo].[vw_index_usage] AS WITH CTE(tablename, indexname, indextype, indexusage, filegroupname, MB, cols, included, user_hits, user_seeks, user_scans , user_lookups, user_updates, stats_date, SQLCmd) AS ( SELECT object_name(a.object_id) "tablename" , c.name "indexname" , c.type_desc "indextype" , CASE c.is_unique WHEN 1 THEN CASE is_primary_key WHEN 1 THEN 'Primary Key' ELSE 'Unique' END ELSE CASE c.is_unique_constraint WHEN 1 THEN 'Unique Constraint' ELSE 'Performance' END END "IndexUsage" , FILEGROUP_NAME(c.data_space_id) "FileGroupName" , ( SELECT CEILING(used / 128) FROM sysindexes b WHERE b.name = c.name AND c.index_id = b.indid AND b.[id]= c.[object_id]) "MB" , ( SELECT COUNT(*) FROM sys.index_columns d WHERE a.object_id = d.object_id AND a.index_id = d.index_id AND d.is_included_column = 0) "cols" , ( SELECT COUNT(*) FROM sys.index_columns d WHERE a.object_id = d.object_id AND a.index_id = d.index_id AND d.is_included_column = 1) "included" , (a.user_seeks + a.user_scans + a.user_lookups) "user_hits" , a.user_seeks , a.user_scans , a.user_lookups , a.user_updates , a.last_user_update "stats_date" , CASE WHEN is_unique_constraint = 0 AND is_unique = 0 AND is_primary_key = 0 THEN 'alter index [' + c.name + '] on [' + object_name(a.object_id) + '] disable;' END "SQLCmd" FROM sys.dm_db_index_usage_stats a JOIN sys.indexes AS c ON a.object_id = c.object_id AND a.index_id = c.index_id WHERE a.object_id > 1000 --exclude system tables and c.type <> 0 -- exclude HEAPs and c.is_disabled = 0 -- only active indexes and a.database_id = DB_ID() -- for current database only ) SELECT tablename , indexname , indextype , indexusage , filegroupname , MB , cols , included , round(CAST(user_seeks AS REAL) / COALESCE(NULLIF(user_hits, 0), 1) * 100,0) AS "perc_seeks" , round(CAST(user_scans AS REAL) / COALESCE(NULLIF(user_hits, 0), 1) * 100, 0) AS "perc_scans" , round(CAST(user_lookups AS REAL) / COALESCE(NULLIF(user_hits,0),1) * 100,0) AS "perc_lookups" , user_hits , user_updates , CASE WHEN user_hits = 0 THEN - user_updates ELSE round(CAST(user_seeks + user_scans*.8 + user_lookups*1.2 AS REAL) / CAST(COALESCE(NULLIF(user_updates, 0), 1) AS REAL),4) END "ratio" ,(user_updates - user_hits) / COALESCE(NULLIF(MB,0),1) AS "pressure" , stats_date , SQLCmd FROM cte GO
