第七章——DMVs和DMFs（4）——用DMV和DMF監控磁盤IO

前言：

        本文為本系列最後一篇，作為DBA，你必須經常關注磁盤的I/O問題，一旦出現問題，要盡快分析出是什麼問題。SQLServer同樣提供了一些列與I/O相關的DMO來做監控。

        本文介紹如何使用DMO來監控I/O子系統的性能并找到I/O瓶頸。通過本文，可以區分不同資料庫的I/O使用模式。一旦發現有資料庫的I/O很高，可能需要考慮把資料庫遷移到單獨的磁盤，或者深入研究I/O産生的問題。

準備工作：

本文将示範如何監控資料庫檔案的I/O情況，将在SQLServer 2008R2上的AdventureWorks資料庫上做示範。

步驟：

1、  打開SQLServer，連到AdventureWorks。

2、  輸入以下腳本監控SQLServer執行個體上的日志檔案和資料檔案：

SELECT  DB_NAME(VFS.database_id) AS DatabaseName ,
        MF.name AS LogicalFileName ,
        MF.physical_name AS PhysicalFileName ,
        CASE MF.type
          WHEN 0 THEN 'Data File'
          WHEN 1 THEN 'Log File'
        END AS FileType ,
        VFS.num_of_reads AS TotalReadOperations ,
        VFS.num_of_bytes_read AS TotalBytesRead ,
        VFS.num_of_writes AS TotalWriteOperations ,
        VFS.num_of_bytes_written AS TotalWriteOperations ,
        VFS.io_stall_read_ms AS TotalWaitTimeForRead ,
        VFS.io_stall_write_ms AS TotalWaitTimeForWrite ,
        VFS.io_stall AS TotalWaitTimeForIO ,
        VFS.size_on_disk_bytes AS FileSizeInBytes
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS
        INNER JOIN sys.master_files AS MF ON VFS.database_id = MF.database_id
                                             AND VFS.file_id = MF.file_id
ORDER BY VFS.database_id DESC
           

3、  在新視窗輸入以下腳本，清空資料緩存：

USE AdventureWorks
GO
DBCC DROPCLEANBUFFERS
GO 
SELECT  *
FROM    [Sales].[SalesOrderDetail]
GO
           

4、  現在再次執行第二步中的腳本，看看情況。

5、  執行下面的語句，檢視是否有IO挂起操作：

SELECT  DB_NAME(VFS.database_id) AS DatabaseName ,
        MF.name AS LogicalFileName ,
        MF.physical_name AS PhysicalFileName ,
        CASE MF.type
          WHEN 0 THEN 'Data File'
          WHEN 1 THEN 'Log File'
        END AS FileType ,
        PIOR.io_type AS InputOutputOperationType ,
        PIOR.io_pending AS Is_Request_Pending ,
        PIOR.io_handle ,
        PIOR.scheduler_address
FROM    sys.dm_io_pending_io_requests AS PIOR
        INNER JOIN sys.dm_io_virtual_file_stats(DB_ID('AdventureWorks'), NULL)
        AS VFS ON PIOR.io_handle = VFS.file_handle
        INNER JOIN sys.master_files AS MF ON VFS.database_id = MF.database_id
                                             AND VFS.file_id = MF.file_id
GO
           

分析：

        首先要切記不要随便在正式環境使用DBCC DROPCLEANBUFFERS指令，這個将會在一段時間内嚴重影響性能。

        在本例中，對于sys.dm_io_pending_io_requests可能在本機中會沒有資料，因為在單機情況下基本上很少io操作，進而挂起的可能性大大降低，但是在正式環境，多使用者連接配接和操作時，就很有可能發生挂起情況。

        上面提到的DMO對查找I/O子系統問題很有幫助，根據這些資訊，再進一步找到高I/O的資料庫，同時檢查所在磁盤的I/O情況。