一. 概述
sql server作為關系型資料庫，需要進行資料存儲， 那在運作中就會不斷的與硬碟進行讀寫互動。如果讀寫不能正确快速的完成，就會出現性能問題以及資料庫損壞問題。下面講講引起I/O的産生，以及分析優化。
二.sql server 主要磁盤讀寫的行為
2.1 從資料檔案(.mdf)裡, 讀入新資料頁到記憶體。前頁講述記憶體時我們知道，如果想要的資料不在記憶體中時，就會從硬碟的資料檔案裡以頁面為最小機關，讀取到記憶體中，還包括預讀的資料。 當記憶體中存在，就不會去磁盤讀取資料。足夠的記憶體可以最小化磁盤I/O,因為磁盤的速度遠慢于記憶體。
2.2 預寫日志系統(WAL),向日志檔案(.ldf)寫入增删改的日志記錄。 用來維護資料事務的ACID。
2.3 Checkpoint 檢查點發生時，将髒頁資料寫入到資料檔案 ，在sp_configure的recovery interval 控制着sql server多長時間進行一次Checkpoint， 如果經常做Checkpoint，那每次産生的硬碟寫就不會太多，對硬碟沖擊不會太大。如果隔長時間一次Checkpoint，不做Checkpoint時性能可能會比較快，但累積了大量的修改，可能要産生大量的寫，這時性能會受影響。在絕大多資料情況下，預設設定是比較好的，沒必要去修改。
2.4 記憶體不足時，Lazy Write發生，會将緩沖區中修改過的資料頁面同步到硬碟的資料檔案中。由于記憶體的空間不足觸發了Lazy Write， 主動将記憶體中很久沒有使用過的資料頁和執行計劃清空。Lazy Write一般不被經常調用。
2.5 CheckDB, 索引維護，全文索引，統計資訊，備份資料，高可用同步日志等。
三. 磁盤讀寫的相關分析
3.1 sys.dm_io_virtual_file_stats 擷取資料檔案和日志檔案的I/O 統計資訊。該函數從sql server 2008開始,替換動态管理視圖fn_virtualfilestats函數。 哪些檔案經常要做讀num_of_reads，哪些經常要做寫num_of_writes,哪些讀寫經常要等待io_stall_*。為了擷取有意義的資料，需要在短時間内對這些資料進行快照，然後将它們同基線資料相比較。
SELECT DB_NAME(database_id) AS 'Database Name',
file_id,
io_stall_read_ms / num_of_reads AS 'Avg Read Transfer/ms',
io_stall_write_ms / num_of_writes AS 'Avg Write Transfer/ms'
FROM sys.dm_io_virtual_file_stats(null, null)
WHERE num_of_reads > 0 AND num_of_writes > 0 io_stall_read_ms：使用者等待檔案,發出讀取所用的總時間(毫秒)。
io_stall_write： 使用者等待在該檔案中完成寫入所用的總時間毫秒。
3.2 windows 性能計數器： Avg. Disk Sec/Read 這個計數器是指每秒從磁盤讀取資料的平均值
< 10 ms - 非常好
10 ~ 20 ms 之間- 還可以
20 ~50 ms 之間- 慢，需要關注
> 50 ms –嚴重的 I/O 瓶頸
3.4 I/O 實體記憶體讀取次數最多的前50條
SELECT TOP 50
qs.total_physical_reads,qs.execution_count,
qs.total_physical_reads/qs.execution_count AS [avg I/O],
qs. creation_time,
qs.max_elapsed_time,
qs.min_elapsed_time,
SUBSTRING(qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset=-1
THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text,
qt.dbid,dbname=DB_NAME(qt.dbid),
qt.objectid,
qs.sql_handle,
qs.plan_handle
from sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_physical_reads DESC 3.5 使用sp_spaceused檢視表的磁盤空間
exec sp_spaceused 'table_xx' 
reserved:保留的空間總量
data:資料使用的空間總量
index_size:索引使用空間
Unused: 未用的空間量
3.6 監測I/0運作狀态 STATISTICS IO ON;
四 磁盤讀寫瓶頸的症狀
4.1 errorlog裡報告錯誤 833
4.2 sys.dm_os_wait_stats 視圖裡有大量等待狀态PAGEIOLATCH_* 或 WriteLog。當資料在緩沖區裡沒有找到，連接配接的等待狀态就是PAGEIOLACTH_EX(寫) PAGEIOLATCH_SH(讀),然後發起異步操作，将頁面讀入緩沖區中。像 waiting_tasks_count和wait_time_ms比較高的時候，經常要等待I/O,除在反映在資料檔案上以外，還有writelog的日志檔案上。想要獲得有意義資料，需要做基線資料,檢視感興趣的時間間隔。
select wait_type,
waiting_tasks_count,
wait_time_ms ,
max_wait_time_ms,
signal_wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
order by wait_type wait_type:等待類型
waiting_tasks_count:該等待類型的等待數
wait_time_ms:該等待類型的總等待時間(包括一個程序懸挂狀态(Suspend)和可運作狀态(Runnable)花費的總時間)
max_wait_time_ms:該等待類型的最長等待時間
signal_wait_time_ms:正在等待的線程從收到信号通知到其開始運作之間的時差(一個程序可運作狀态Runnable花費的總時間)
i/o等待時間==wait_time_ms - signal_wait_time_ms
五 優化磁盤I/O
5.1 資料檔案裡頁面碎片整理。 當表發生增删改操作時索引都會産生碎片（索引葉級的頁拆分），碎片是指索引上的頁不再具有實體連續性時，就會産生碎片。比如你查詢10條資料，碎片少時，可能隻掃描2個頁，但碎片多時可能要掃描更多頁(後面講索引時在細說)。
5.2 表格上的索引。比如：建議每個表都包含聚集索引，這是因為資料存儲分為堆和B-Tree, 按B-Tree空間占用率更高。 充分使用索引減少對I/0的需求。
5.3 資料檔案，日志檔案，TempDB檔案建議存放不同實體磁盤，日志檔案放寫入速度比較快的磁盤上，例如 RAID 10的分區
5.4 檔案空間管理，設定資料庫增長時要按固定大小增長，而不能按比例，這樣避免一次增長太多或太少所帶來的不必要麻煩。建議對比較小的資料庫設定一次增長50MB到100MB。下圖顯示如果按5%來增長近10G， 如果有一個應用程式在嘗試插入一行，但是沒有空間可用。那麼資料庫可能會開始增長一個近10G, 檔案的增長可能會耗用太長的時間，以至于用戶端程式插入查詢失敗。
5.5 避免自動收縮檔案,如果設定了此功能，sql server會每隔半小時檢查檔案的使用，如果空閑空間>25%，會自動運作dbcc shrinkfile 動作。自動收縮線程的會話ID SPID總是6(以後可能有變) 如下顯示自動收縮為False。
5.6 如果資料庫的恢複模式是：完整。 就需要定期做日志備份，避免日志檔案無限的增長，用于磁盤空間。