|
--
-- Example query to extract index usage
-- Results for the hour 10:00 to 11:00 today
-- Highest Reads and Writes with proportion shown
--
select ia1.tablename,ia1.indexname,ia2.reads-ia1.reads as Reads,ia2.writes-ia1.writes as Writes
, convert(int, (ia2.reads-ia1.reads)*1.0/((ia2.reads-ia1.reads)+(ia2.writes-ia1.writes))*100.0) as 'Read%'
, convert(int, (ia2.writes-ia1.writes)*1.0/((ia2.reads-ia1.reads)+(ia2.writes-ia1.writes))*100.0) as 'Write%'
from ServerAdmin.dbo.tbl_indexactivity ia1 join ServerAdmin.dbo.tbl_indexactivity ia2 on
ia1.tablename = ia2.tablename and ia1.indexname=ia2.indexname
where datepart(hh,ia1.thedate) = 10 and convert(char(8),ia1.thedate,112) = convert(char(8),getdate(),112)
and datepart(hh,ia2.thedate) = 11 and convert(char(8),ia2.thedate,112) = convert(char(8),getdate(),112)
and ( (ia2.reads-ia1.reads) + (ia2.writes-ia1.writes) ) >100
order by ((ia2.reads-ia1.reads)+(ia2.writes-ia1.writes)) desc;
go
|