如何检查SQL Server I/O瓶颈

SELECT database_id,file_id,io_stall_read_ms,io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL)

--查询I/O等待锁的统计信息

SELECT
wait_type,waiting_tasks_count,wait_time_ms,signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%'
ORDER BY wait_type

--查找引发I/O最多的查询或批处理

SELECT TOP 10
total_logical_reads/execution_count AS avg_logical_reads
,total_logical_writes/execution_count AS avg_logical_writes
,total_physical_reads/execution_count AS avg_phys_reads
,execution_count,
(SELECT SUBSTRING(text,statement_start_offset/2+1,
        (CASE WHEN statement_end_offset=-1
        THEN LEN(CONVERT(NVARCHAR(max),text))*2
        ELSE statement_end_offset
        END -statement_start_offset)/2)
    FROM sys.dm_exec_sql_text(sql_handle)
    ) AS query_text
,plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads+total_logical_writes) DESC

---如何查询丢失的索引
CREATE TABLE t_sample(c1 INT ,c2 INT ,c3 INT ,c4 INT ,c5 CHAR(5000))

DECLARE @i INT
SELECT @i =0
WHILE (@i<1000)
BEGIN
INSERT INTO t_sample VALUES(@i,@i+1000,@i+2000,@i+3000,'hello')
SET @i=@i+1
END

CREATE CLUSTERED INDEX t_ample_ci ON t_sample(c1)
CREATE NONCLUSTERED INDEX t_sample_nci_c4 ON t_sample(c4)

DECLARE @i INT
SELECT @i=0;
WHILE(@i<100)
BEGIN
SELECT SUM(c1+c2+c3) FROM t_sample
WHERE c1 BETWEEN @i AND @i+50
SELECT SUM(c2) FROM t_sample
WHERE c2 BETWEEN @i+1000 AND @i+1100
SELECT SUM(c3) FROM t_sample
WHERE c3 BETWEEN @i+2000 AND @i+2400
SET @i=@i+1
END


SELECT
t1.object_id,t2.user_seeks,t2.user_scans,
t1.equality_columns,t1.inequality_columns
FROM sys.dm_db_missing_index_details AS t1,
sys.dm_db_missing_index_group_stats AS  t2,
sys.dm_db_missing_index_groups AS t3
WHERE database_id=DB_ID()
AND object_id=object_id('t_sample')
AND t1.index_handle=t3.index_handle
AND t2.group_handle=t3.index_group_handle