Index usage overview

--#region drop if exists
if exists (select 1 from INFORMATION_SCHEMA.VIEWS where table_name = 'vw_index_usage' and table_schema='dbo')
drop view [dbo].[vw_index_usage];
go
--#endregion

--#region create view vw_index_usage
create view [dbo].[vw_index_usage]
with encryption
as
with CTE(tablename, indexname, indextype, indexusage, fill_factor, filegroupname, MB, cols, included, has_filter, user_hits, user_seeks, user_scans
, user_lookups, user_updates, system_reads, system_updates, stats_date, compression_method, SQLCmd)
as
(
selectd.name "tablename"
, c.name "indexname"
, c.type_desc "indextype"
, case c.is_disabled
when 1 then '(DISABLED)'
else
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
case when c.name like 'FK[_]%'
then 'Foreign Key'
else
'Performance'
end
end
end
end  "IndexUsage"
, c.fill_factor
, 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 c.object_id = d.object_id and c.index_id = d.index_id and d.is_included_column = 0) "cols"
, (select count(*) from sys.index_columns d where c.object_id = d.object_id and c.index_id = d.index_id and d.is_included_column = 1) "included"
, c.has_filter
, (a.user_seeks + a.user_scans + a.user_lookups) "user_hits"
, a.user_seeks
, a.user_scans
, a.user_lookups
, a.user_updates
, (a.system_seeks + a.system_scans + a.system_lookups) as system_reads
, a.system_updates
, a.last_system_update "stats_date"
, e.data_compression_desc
, case
when is_unique_constraint = 0 and is_unique = 0 and is_primary_key = 0 and c.type_desc = 'NONCLUSTERED'
then 'alter index [' + c.name + '] on [' + object_name(c.object_id) + '] disable;'
end "SQLCmd"
from    sys.indexes as c
leftjoin sys.dm_db_index_usage_stats a
ona.object_id = c.object_id
anda.index_id = c.index_id
anda.database_id = DB_ID()
joinsys.tables d
onc.object_id = d.object_id
joinsys.partitions e
one.object_id = c.object_id
ande.index_id = c.index_id
wherec.type > 0-- exclude HEAPs
union
selectb.name
, b.name
, a.type_Desc
, NULL
, NULL
, FILEGROUP_NAME(a.data_space_id)
, CEILING(c.used/128)
, b.max_column_id_used
, NULL
, NULL
, (d.user_seeks + d.user_scans + d.user_lookups)
, d.user_seeks
, d.user_scans
, d.user_lookups
, d.user_updates
, (d.system_seeks + d.system_scans + d.system_lookups) as system_reads
, d.system_updates
, d.last_system_update "stats_date"
, e.data_compression_desc
, NULL
fromsys.indexes a
joinsys.tables b
on a.object_id = b.object_id
join sysindexes c
on a.object_id = c.id
and a.index_id = c.indid
leftjoin sys.dm_db_index_usage_stats d
ond.object_id = a.object_id
andd.index_id = a.index_id
andd.database_id = DB_ID()
joinsys.partitions e
one.object_id = a.object_id
ande.index_id = a.index_id
whered.index_id = 0
)
selecttablename
, indexname
, indextype
, indexusage
, filegroupname
, fill_factor
, MB
, cols
, included
, has_filter
, 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
, system_updates
, case
when user_hits = 0
then 0
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 "read_ratio"
, case
when user_updates = 0
then 0
else round(cast(user_updates as REAL) / coalesce(nullif(cast(user_seeks + user_scans*.8 + user_lookups*1.2 AS REAL),0),1) , 4)
  end "write_ratio"
, (user_updates - user_hits) / COALESCE(NULLIF(MB,0),1) as "pressure"
, stats_date
, compression_method
, SQLCmd
fromcte
GO
--#endregion