IO - Most Heavily Updated files and Indexes Snapshot

This was a query written in anger :)
Pulls IO Stats for a snapshot period, combines file IO statistics with index usage statistics, I have used it successfully to look for unused indexes or indexes that are being written to more than read from. changing the following line

while @counter < 1 begin waitfor delay '00:10:00'

you can configure the snapshot interval.

set xact_abort off
SET ANSI_WARNINGS OFF
set arithabort off
set arithignore ON
set nocount on

if exists (select * from tempdb..sysobjects where name like '%dbio') drop table ##dbio

declare @counter int
set @counter = 0
select db_name(Database_id) as DatabaseName, file_name(file_id) as FileName, num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, io_stall_write_ms
into ##dbio
from sys.dm_io_virtual_file_stats (null, null)
where Database_id = db_id()
while @counter < 1 begin waitfor delay '00:10:00'
insert ##dbio
select db_name(Database_id) as DatabaseName, file_name(file_id) as FileName, num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, io_stall_write_ms from sys.dm_io_virtual_file_stats (null, null) where Database_id = db_id() set @counter = @counter + 1 end

select
##dbio.DatabaseName,
##dbio.FileName,
max(num_of_reads) - min(num_of_reads) 'Number of Read IOs on the File',
max(io_stall_read_ms) - min(io_stall_read_ms) 'Cumulative Time m/s Waiting for reads',
(max(io_stall_read_ms) - min(io_stall_read_ms))/(max(num_of_reads) - min(num_of_reads)) 'Avg m/s Time Waiting for Reads',
max(num_of_writes) - min(num_of_writes) 'Number of Write IOs on the File',
max(io_stall_write_ms) - min(io_stall_write_ms) 'Cumulative Time m/s Waiting for Writes',
(max(io_stall_write_ms) - min(io_stall_write_ms))/(max(num_of_writes) - min(num_of_writes)) 'Avg m/s Time Waiting for Writes',
f.name 'FileGroup Name',
o.name 'Table Name',
si.name 'Index Name', CASE si.indid WHEN 0 THEN 'HEAP' WHEN 1 THEN 'CLUSTERED' ELSE 'NON-CLUSTERED' END 'Index Type',
si.rowcnt 'No.Rows',
si.rowmodctr 'Number of Changed Rows since Last Stats Update',
cast(cast(si.rowmodctr as decimal)/cast(si.rowcnt as decimal)*100 as decimal (2,1))'% Rows Changes since Last Stats Update',
cast (ios.row_lock_wait_in_ms/ios.row_lock_wait_count as decimal (5,5)) 'AVG Wait m/s for Row Lock',
cast(ios.page_lock_wait_in_ms/ios.page_lock_wait_count as decimal (5,5)) 'AVG Wait m/s for Page Lock',
ios.index_lock_promotion_attempt_count 'No. Lock Escalation Attempts',
ios.index_lock_promotion_count 'No. Successful Lock Escalations',
cast(ios.page_io_latch_wait_in_ms/ios.page_io_latch_wait_count as decimal (2,0)) 'Avg Latch I/O Wait Times',
max(ius.user_seeks) 'Number of User Seeks',
max(ius.user_scans) 'Number of User Scans',
max(ius.user_lookups) 'Number of Bookmark Lookups',
max(user_updates) 'Number of Updates',
cast (stats_date(i.object_id, i.index_id) as smalldatetime) 'Last Time Statistics Updated'
from ##dbio
join sys.sysfiles f on file_idex(##dbio.FileName) = f.fileid
join sys.sysfilegroups fg on f.groupid = fg.groupid
join sys.sysindexes si on fg.groupid = si.groupid
join sys.objects o on si.id = o.object_id
join sys.indexes i on o.object_id = i.object_id and i.object_id = si.id and si.indid = i.index_id
join sys.dm_db_index_operational_stats (db_id(), null, null,null) as ios on o.object_id = ios.object_id and i.index_id = ios.index_id
join sys.dm_db_index_usage_stats ius on ius.object_id = o.object_id and ius.index_id = i.index_id
where i.is_hypothetical = 0 and o.type = 'U'
--and si.name not like 'PK_%'
--and ius.user_seeks = 0
--and ius.user_scans = 0
--and ius.user_lookups = 0
group by DatabaseName, f.name, ##dbio.FileName, o.name , si.indid, si.name, si.rowcnt, si.rowmodctr, i.object_id, i.index_id, ios.row_lock_count,ios.row_lock_wait_in_ms,ios.row_lock_wait_count,ios.page_lock_count,page_lock_wait_in_ms,ios.page_lock_wait_count,index_lock_promotion_attempt_count,ios.index_lock_promotion_count,ios.page_io_latch_wait_count,ios.page_io_latch_wait_in_ms,ios.page_io_latch_wait_count
order by max(user_updates) desc

drop table ##dbio

Print 'The values between File Name and File Group Name are relevant to the capture period'
Print 'The values between to the Right of %Rows changed since last update are valid from when the server was started'