--===============================

--查看文件上IO等待

SELECT * FROM sys.dm_os_wait_stats

WHERE wait_type LIKE '%PAGEIOLATCH%'

--==================================

--查看各数据库文件5分钟内的IO情况

--查看5分钟内文件的IO情况

IF(OBJECT_ID('tempdb.dbo.#TB1') IS NOT NULL)

BEGIN

DROP TABLE #TB1

END

IF(OBJECT_ID('tempdb.dbo.#TB2') IS NOT NULL)

BEGIN

DROP TABLE TB2

END

SELECT

database_id,

file_id,

io_stall_read_ms,

io_stall_write_ms,

num_of_reads,

num_of_writes,

num_of_bytes_read,

num_of_bytes_written

INTO #TB1

FROM sys.dm_io_virtual_file_stats(NULL,NULL)

WAITFOR DELAY '0:05:00'

SELECT

database_id,

file_id,

io_stall_read_ms,

io_stall_write_ms,

num_of_reads,

num_of_writes,

num_of_bytes_read,

num_of_bytes_written

INTO #TB2

FROM sys.dm_io_virtual_file_stats(NULL,NULL)

SELECT

T1.database_id,

DB_NAME(T1.database_id) AS database_name,

T1.file_id,

T2.io_stall_read_ms-T1.io_stall_read_ms AS io_stall_read_ms,

T2.io_stall_write_ms-T1.io_stall_write_ms AS io_stall_write_ms,

T2.num_of_reads-T1.num_of_reads AS num_of_reads,

T2.num_of_writes-T1.num_of_writes AS num_of_writes ,

T2.num_of_bytes_read-T1.num_of_bytes_read AS num_of_bytes_read,

T2.num_of_bytes_written-T1.num_of_bytes_written AS num_of_bytes_written

FROM #TB1 AS T1

INNER JOIN #TB2 AS T2

ON T1.database_id=T2.database_id

AND T1.file_id=T2.file_id

--===============================================================

--查看各数据库文件的使用情况

SELECT

DB.database_id AS DatabaseId,

DB.name AS DatabaseName,

MF.name AS LogicFileName,

MF.type_desc AS TypeDesc,

CAST(VF.num_of_bytes_read/1024.0/1024.0 AS INT) AS num_of_MB_read,

CAST(VF.num_of_bytes_written /1024.0/1024.0 AS INT) AS num_of_MB_written,

VF.num_of_reads,

VF.num_of_writes,

VF.io_stall_read_ms,

VF.io_stall_write_ms,

VF.io_stall,

CAST(VF.size_on_disk_bytes/1024.0/1024.0 AS INT) AS size_on_disk_MB

FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS VF

INNER JOIN sys.databases DB

ON VF.database_id=DB.database_id

INNER JOIN sys.master_files MF

ON DB.database_id=MF.database_id

--=====================================================

--来查看等待响应的IO请求

SELECT * FROM sys.dm_io_pending_io_requests

--==================================================

--查看数据库文件的操作信息如磁盘读写次数、读写数量

SELECT

DB_NAME(S.database_id) AS DBName,

f.name,

S.*

FROM sys.databases D

INNER JOIN sys.sysaltfiles F

ON D.database_id=F.dbid

INNER JOIN sys.dm_io_virtual_file_stats(null,null) S

ON S.database_id=D.database_id

AND S.file_id=F.fileid

--=====================================================

--查看当前挂起的IO请求

SELECT

DB_NAME(S.database_id) AS DBName,

S.file_id,

S.io_stall,

R.io_pending_ms_ticks

FROM sys.dm_io_pending_io_requests R

INNER JOIN sys.dm_io_virtual_file_stats(NULL,NULL) S

ON R.io_handle=S.file_handle