Latency on SAN SQL Server get blamed

hi,

i running sql server 2008 r2 on san disk around 20 databases. have 4 different production servers , hosted on same server. latency happening quite , our department straightway blame sql this. monitor sql , can't find evidence of latency. when notice department there latency jump on , run sp_who2 check , of time don't odd running. did notice on occusion there alot high disk i/o reading.

i running performance monitor not sure how capture event. 

is there way in sql server can capture , when happens(email or eventlog). want see if sql causing problem or there other server causing bottleneck.

my favourite sys.dm_io_virtual_filestats. run procedure below interval , use query below analyse data. it's columns avg_read/write_time_ms of interest.

a spinning disk has latency of 9.5 ms typically, on 15 ms warning sign. unfortunately, not unusual sans see lot worse.

create procedure [dbo].[vfsmonitor] as

set nocount on

if object_id('vfsdata') null
begin
  create table vfsdata (
      sample_time               datetime not null,
      database_id               int      not null,
      file_id                   int      not null,
      sample_length_sec         int      null,
      num_of_reads              bigint   null,
      num_of_bytes_read         bigint   null,
      io_stall_read_ms          bigint   null,
      num_of_writes             bigint   null,
      num_of_bytes_written      bigint   null,
      io_stall_write_ms         bigint   null,
      io_stall                  bigint   null,
      delta_size_on_disk        bigint   null,
      tot_num_of_reads          bigint   not null,
      tot_num_of_bytes_read     bigint   not null,
      tot_io_stall_read_ms      bigint   not null,
      tot_num_of_writes         bigint   not null,
      tot_num_of_bytes_written  bigint   not null,
      tot_io_stall_write_ms     bigint   not null,
      tot_io_stall              bigint   not null,
      size_on_disk_bytes        bigint   not null,
      constraint pk_vfsdata primary key (sample_time, database_id, file_id)
  )

  create index database_ix on vfsdata(database_id, file_id)
end

declare @latest datetime,
        @now    datetime

select @latest = max(sample_time) vfsdata
select @now = getdate()

insert dbo.vfsdata
 (sample_time, database_id, file_id, sample_length_sec,
  num_of_reads, num_of_bytes_read,
  io_stall_read_ms, num_of_writes, num_of_bytes_written,
  io_stall_write_ms, io_stall, delta_size_on_disk,
  tot_num_of_reads, tot_num_of_bytes_read,
  tot_io_stall_read_ms, tot_num_of_writes, tot_num_of_bytes_written,
  tot_io_stall_write_ms, tot_io_stall, size_on_disk_bytes)
select @now, fs.database_id, fs.file_id, datediff(ss, v.sample_time, @now),
       fs.num_of_reads         - v.tot_num_of_reads,
       fs.num_of_bytes_read    - v.tot_num_of_bytes_read,
       fs.io_stall_read_ms     - v.tot_io_stall_read_ms,
       fs.num_of_writes        - v.tot_num_of_writes,
       fs.num_of_bytes_written - v.tot_num_of_bytes_written,
       fs.io_stall_write_ms    - v.tot_io_stall_write_ms,
       fs.io_stall             - v.tot_io_stall,
       fs.size_on_disk_bytes   - v.size_on_disk_bytes,
       fs.num_of_reads,
       fs.num_of_bytes_read,
       fs.io_stall_read_ms,
       fs.num_of_writes,
       fs.num_of_bytes_written,
       fs.io_stall_write_ms,
       fs.io_stall,
       fs.size_on_disk_bytes
 from  sys.dm_io_virtual_file_stats(null, null) fs
 left  join vfsdata v on fs.database_id = v.database_id
                        , fs.file_id     = v.file_id
                        , v.sample_time   = @latest

go

with vfs (
   select sample_time, db_name(database_id) db_name, file_id, num_of_reads, num_of_bytes_read,
          convert(decimal(6,1), 1e0*io_stall_read_ms / nullif(num_of_reads, 0)) avg_read_time,
          num_of_writes, num_of_bytes_written, io_stall_write_ms,
          convert(decimal(6,1), 1e0*io_stall_write_ms / nullif(num_of_writes,0)) avg_write_time
   from   vfsdata v
    where  (v.num_of_reads > 10 or v.num_of_writes > 10)
)
select * vfs
avg_read_time > 15 , avg_write_time > 15
order sample_time, db_name, file_id

---

erland sommarskog, sql server mvp, esquel@sommarskog.se

SQL Server  >  SQL Server Database Engine