Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Disk IO response time check.

Posted by Simon Cho on 08/04/2011

select convert(varchar(19), getdate(), 121) as date_time
, db_name(df.database_id) as DatabaseName
, f.name as FileLogicalName
, f.filename
, case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end as ‘ms/io’
, case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end as ‘ms/Read io’
, case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end as ‘ms/Write io’
from sys.dm_io_virtual_file_stats(null,null) as df
join sys.sysaltfiles as f
on f.dbid= df.database_id
and f.fileid = df.file_id
–where df.database_id in(DB_ID(‘db_passport’), DB_ID(‘TempDB’))
where case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end >50
or case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end >50
or case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end >50
order by 5 desc

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: