Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Disk IO Response time In SQL

Posted by Simon Cho on 08/14/2012

https://sqlserverperformance.wordpress.com/tag/dmv-queries/

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('msdb'), 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: