共計 2634 個字符，預計需要花費 7 分鐘才能閱讀完成。

今天就跟大家聊聊有關如何進行 sql server 性能調優 I/ O 開銷的深入解析，可能很多人都不太了解，為了讓大家更加了解，丸趣 TV 小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

一. 概述

IO 內存是 sql server 最重要的資源，數據從磁盤加載到內存，再從內存中緩存，輸出到應用端，在 sql server 內存初探中有介紹。在明白了 sqlserver 內存原理后，就能更好的分析 I / O 開銷，從而提升數據庫的整體性能。在生產環境下數據庫的 sqlserver 服務啟動后一個星期，就可以通過 dmv 來分析優化。在 I / O 分析這塊可以從物理 I / O 和內存 I / O 二方面來分析，重點分析應在內存 I / O 上，可能從多個維度來分析，比如從 sql server 服務啟動以來 歷史 I / O 開銷總量分析，自執行計劃編譯以來執行次數總量分析，平均 I / 0 次數分析等。

sys.dm_exec_query_stats：返回緩存的查詢計劃，緩存計劃中的每個查詢語句在該視圖中對應一行。當 sql server 工作負載過重時，該 dmv 也有可以統計不正確。如果 sql server 服務重啟緩存的數據將會清掉。這個 dmv 包括了太多的信息像內存掃描數，內存空間數，cpu 耗時等，具體查看 msdn 文檔。

sys.dm_exec_sql_text：返回的 SQL 文本批處理，它是由指定 sql_handle，其中的 text 列是查詢的文本。

1.1 按照物理讀的頁面數排序 前 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

如下圖所示：

total_physical_reads: 計劃自編譯后在執行期間所執行的物理讀取總次數。

execution_count : 計劃自上次編譯以來所執行的次數。

[avg I/O]: 平均讀取的物理次數 (頁數)。

creation_time: 編譯計劃的時間。

query_text：執行計劃對應的 sql 腳本

后面來包括所在的數據庫 ID：dbid，數據庫名稱：dbname

1.2 按照邏輯讀的頁面數排序 前 50 名

SELECT TOP 50 qs.total_logical_reads, qs.execution_count, qs.max_elapsed_time, qs.min_elapsed_time, qs.total_logical_reads/qs.execution_count AS [AVG IO], 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, creation_time, 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_logical_reads DESC

如下圖所示：

通過上面的邏輯內存截圖來簡要分析下：

從內存掃描總量上看最多的是 8311268 次頁掃描，自執行編譯后運行 t -sql 腳本 358 次，這里的耗時是毫秒為單位包括最大耗時和最小耗時，平均 I / O 是 232115 次（頁），該語句文本是一個 update 修改，該表數據量大沒有完全走索引（權衡后不對該語句做索引覆蓋），但執行次數少，且每次執行時間是非工作時間，雖然掃描開銷大，但沒有影響白天客戶使用。

從執行次數是有一個 43188 次，內存掃描總量排名 39 位。該語句雖然只有 815 條，但執行次數很多，如里服務器有壓力可以優化，一般是該語句沒有走索引。把文本拿出來如下

SELECT Count(*) AS TotalCount FROM [MEM_FlagshipApply] WITH(NOLOCK) Where (((([Status] = 2) AND ([IsDeleted] = 1)) AND ([MemType] = 0)) AND ([MEMID]   6))

下面兩圖一個是分析該語句的執行計劃，sqlserver 提示缺少索引, 另一個是 i / o 統計掃描了 80 次。

新建索引后在來看看

 CREATE NONCLUSTERED INDEX ix_1ON [dbo].[MEM_FlagshipApply] ([Status],[IsDeleted],[MemType],[MEMID])

看完上述內容，你們對如何進行 sql server 性能調優 I/ O 開銷的深入解析有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注丸趣 TV 行業資訊頻道，感謝大家的支持。