缓解 SQL Server has encountered 727 occurrence(s) of I/O requests taking longer than 15 seconds

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[usp_monitor_virtual_file_latency]
as
begin
if OBJECT_ID('master.dbo.tbl_virtual_file_latency','U') is null
begin
create table master.dbo.tbl_virtual_file_latency
(
id int identity(1,1) primary key,
collect_dt datetime not null,
[database_name] [nvarchar](128) NULL,
[file_id] [smallint] NOT NULL,
[type_desc] [nvarchar](60) NULL,
[sample_ms] [int] NOT NULL,
[num_of_reads] [bigint] NOT NULL,
[num_of_bytes_read] [bigint] NOT NULL,
[io_stall_read_ms] [bigint] NOT NULL,
[num_of_writes] [bigint] NOT NULL,
[num_of_bytes_written] [bigint] NOT NULL,
[io_stall_write_ms] [bigint] NOT NULL,
[io_stall] [bigint] NOT NULL,
[size_on_disk_bytes] [bigint] NOT NULL,
[file_handle] [varbinary](8) NOT NULL,
[physical_name] [nvarchar](260) NOT NULL,
[state_desc] [nvarchar](60) NULL
)
create index idx_collect_dt on tbl_virtual_file_latency(collect_dt) with(online=on)
end insert into master.dbo.tbl_virtual_file_latency
SELECT
--virtual file latency
GETDATE(),
db_name(vfs.[database_id]) as database_name
,vfs.[file_id]
,mf.[type_desc]
,vfs.[sample_ms]
,vfs.[num_of_reads]
,vfs.[num_of_bytes_read]
,vfs.[io_stall_read_ms]
,vfs.[num_of_writes]
,vfs.[num_of_bytes_written]
,vfs.[io_stall_write_ms]
,vfs.[io_stall]
,vfs.[size_on_disk_bytes]
,vfs.[file_handle]
,mf.[physical_name]
,mf.[state_desc]FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files(nolock) AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
WHERE DB_NAME(vfs.database_id) not in('master','model','msdb') --delete old data delete from master.dbo.tbl_virtual_file_latency where collect_dt <DATEADD(MONTH,-2,getdate())
end

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[usp_calculate_virtual_file_stats_PerMinutes]
@physical_name nvarchar(1000),
@collect_starttime datetime,
@collect_endtime datetime
as
with snap as
(
select
row_number() over (order by id asc) as row_num,
id
,[collect_dt]
,[database_name]
,[file_id]
,[type_desc]
,[sample_ms]
,[num_of_reads]
,[num_of_bytes_read]
,[io_stall_read_ms]
,[num_of_writes]
,[num_of_bytes_written]
,[io_stall_write_ms]
,[io_stall]
from master.dbo.tbl_virtual_file_latency(nolock) AS [vfs1]
WHERE vfs1.physical_name=@physical_name
and vfs1.collect_dt>=@collect_starttime
and vfs1.collect_dt<=@collect_endtime)
select cur.collect_dt,
read_io_num= case when (cur.num_of_reads-pre.num_of_reads)=0 then 0 else cur.num_of_reads-pre.num_of_reads end ,
write_io_num= case when (cur.num_of_writes-pre.num_of_writes)=0 then 0 else cur.num_of_writes-pre.num_of_writes end ,
read_latency_MS =case when (cur.num_of_reads-pre.num_of_reads)=0 then 0 else cast((cur.io_stall_read_ms-pre.io_stall_read_ms)*1./(cur.num_of_reads-pre.num_of_reads) as decimal(10,1)) end ,
write_latency_MS= case when (cur.num_of_writes-pre.num_of_writes)=0 then 0 else cast((cur.io_stall_write_ms-pre.io_stall_write_ms)*1./(cur.num_of_writes-pre.num_of_writes) as decimal(10,1)) end ,
average_latency_MS=case when (cur.num_of_reads-pre.num_of_reads+cur.num_of_writes-pre.num_of_writes)=0 then 0 else cast((cur.io_stall-pre.io_stall)*1./(cur.num_of_reads-pre.num_of_reads+cur.num_of_writes-pre.num_of_writes) as decimal(10,1)) end,
averageKB_read=case when (cur.num_of_reads-pre.num_of_reads)=0 then 0 else cast((cur.num_of_bytes_read-pre.num_of_bytes_read)*1./(cur.num_of_reads-pre.num_of_reads)/1024 as decimal(10,1))end ,
averageKB_write=case when (cur.num_of_writes-pre.num_of_writes)=0 then 0 else cast((cur.num_of_bytes_written-pre.num_of_bytes_written)*1./(cur.num_of_writes-pre.num_of_writes)/1024 as decimal(10,1)) end,
averageKB_Transfer=case when (cur.num_of_reads-pre.num_of_reads+cur.num_of_writes-pre.num_of_writes)=0 then 0 else cast((cur.num_of_bytes_read-pre.num_of_bytes_read+cur.num_of_bytes_written-pre.num_of_bytes_written)*1./(cur.num_of_reads-pre.num_of_reads+cur.num_of_writes-pre.num_of_writes)/1024 as decimal(10,1)) end
from snap as pre join snap as cur
on pre.row_num=cur.row_num-1

exec usp_calculate_virtual_file_stats_PerMinutes
'E:\TestDB\test.mdf',
'2014-12-14 13:49:01.000',
'2014-12-14 15:49:01.000'

select object_name(object_id) as tblname,*
from sys.partitions (nolock)as sp
where sp.partition_id in (
select au.container_id from
sys.dm_os_buffer_descriptors(nolock) as obd
join sys.allocation_units(nolock) as au
on obd.allocation_unit_id=au.allocation_unit_id
where obd.page_id=18383096)