IO--常用的IO问题查询语句

--===============================
--查看文件上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