SQL 伺服器：盡可能降低磁碟 I/O
微調查詢和編製索引是降低實體和邏輯磁碟 I/O 的有效方法。
摘自"SQL 伺服器 DMV 起動包、"由紅門書 （2010 年） 出版。
Glenn Berry, Louis Davidson 和 Tim Ford
有持續性的需要，儘量減少邏輯和物理 I/O。 O 相關的資料庫管理物件 （目的地） 的集合可以説明調查，具體來說，您的系統上進行的資料寫入並從磁片中讀取時的物理 I/O。
在此類別中的旅遊景點管理組織提供從磁片子系統的磁片 I/O 顯式圖片。 他們向我們展示，例如，如何將 I/O 分佈在各種檔在磁片上放置其中 I/O 是成為性能瓶頸，從而導致 I/O 檔位，依此類推。 您可以使用此資訊來優化磁片子系統的體系結構。 此外可以收集資料並使用它來支援更多存儲容量對營業單位領導的請求。
當然，一些物理 I/O 是不可避免的。 SQL Server 必須將應用程式資料寫入磁片。 它也必須寫入到事務日誌中為每個插入、 更新和刪除、 甚至為大容量操作。 然而，你只是需要更多磁片電源的結論之前，記住有很多您可以在查詢優化和索引，儘量減少不必要的邏輯和物理 I/O。
您應該考慮從 DMOs 派生的 I/O 資訊涵蓋在這裡 （所有哪些開始與"sys.dm_io_"），以及從其他動態管理檢視 (Dmv) 引用 I/O 性能以某種方式的資料包括：
- sys.dm_exec_query_stats — — 一個給定的查詢已過去它已經執行的時間成本的 I/O
- sys.dm_exec_connections — — 在該連接發生的 I/O
- sys.dm_exec_sessions — — 已採取的 I/O 放置在該屆會議期間
- sys.dm_os_workers — — 一個給定的工作執行緒掛起 I/O
這一節中的查詢的所有與 SQL Server 2005 中，2008 年和 2008 R2，和所有需要查看伺服器狀態的許可權。
調查 I/O 檔通過磁碟瓶頸
我們將在這裡使用的 DMV 是 sys.dm_io_virtual_file_stats，SQL Server 連線叢書描述為："返回的資料和日誌檔 I/O 統計資訊。 此動態管理檢視取代 fn_virtualfilestats 函數"。
此 DMV 接受兩個參數：database_id 和 file_id。 您可以為任一指定 Null。 在這種情況下，它將返回資訊的所有資料庫上的所有檔。
請注意此 DMV 累積。 換言之，資料列中的值遞增不斷從點時，伺服器上次重新開機。 這意味著您需要採取基準測量，跟實際測量。 然後減去兩個，以便您可以看到那裡積累 I/O。
此腳本，您可以看到的讀取的數量，並寫上每個資料庫的 SQL Server 實例上運行的每個資料和日誌檔。 它被按平均 I/O 檔時間以毫秒為單位）：
-- Calculates average stalls per read, per write, and per total input/output -- for each database file. SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;
此查詢將顯示您等待磁片 I/O 的時間最長的檔。 它可以説明您決定在哪裡可以找到基於您可用磁片資源的單個檔。 此外可以使用它來説明說服別人像 SAN 工程師，SQL Server 看到磁碟瓶頸的某些檔。
調查通過掛起 I/O 磁碟瓶頸
這為調查磁片 I/O 瓶頸採用稍有不同的方法。 使用 sys.dm_io_pending_io_requests DMV，SQL Server 連線叢書描述為："在 SQL Server 中返回的每個掛起的 I/O 請求的行"。
DMV 中的資料執行腳本的時候您的系統上提供 I/O 請求掛起的"時間點"的快照：
-- Look at pending I/O requests by file SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs. num_of_reads , fs. num_of_writesFROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_idORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;
因為此資料表示活動的時間點快照，您需要多次運行此查詢以查看是否相同的檔 （和相同的磁碟機號） 顯示一貫在清單的頂部。 如果發生這種情況，則該特定的檔或磁碟機號的 I/O 瓶頸的證據。 您可以使用此説明說服您的 SAN 系統工程師正在遇到 I/O 問題的特定 LUN。
最後兩個列在查詢中的返回的讀取和寫入檔的累計數目自啟動 SQL Server （或因為在創建該檔 — — 時間較短者為准）。 試圖決定哪些 RAID 級別使用的特定磁碟機號時，此資訊很有用。 例如，帶有更多寫活動的檔將通常更好地對執行 RAID 10 LUN 比他們會在 RAID 5 LUN 上。
瞭解每個檔的相對讀/寫比率可以説明您將您適當的 LUN 上的資料庫檔案。 這一點，反過來，將説明您優化您的查詢，以提高效率。
.png)
.png)
.png)
Glenn Berry 在丹佛，科羅拉多州 NewsGator 技術工作作為資料庫架構師 他是 SQL 伺服器 MVP，並且具有微軟認證，包括 MCITP、 MCDBA、 MCSE、 MCSD、 MCAD 和 MCTS，這證明了他喜歡參加測試的整個集合。
Louis Davidson 已在 IT 業 16 年作為公司的資料庫開發人員和架構師。 他六年來一直 SQL 伺服器 Microsoft MVP，寫了四本書的資料庫設計。 目前他是資料架構師和有時 DBA 支援辦事處在弗吉尼亞州佛吉尼亞海灘和位於田納西州的納什維爾的基督教廣播網路
Timothy Ford 我s SQL 伺服器 MVP 和與 SQL Server 工作 10 年以上。他是主 DBA 和 SQL Server 平臺的頻譜保健的主題事項專家。他曾是自 2007 年以來的各種 Web 網站有關技術寫作並維護自己在博客上的 thesqlagentman.com，以及遠端辦公和專業發展主題作為覆蓋 SQL。**
瞭解更多有關"SQL 伺服器 DMV 起動包"在 red-gate.com/our-company/about/book-store。