今天就跟大家聊聊有關(guān)如何進行sql server性能調(diào)優(yōu) I/O開銷的深入解析，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

為嵐皋等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計制作服務(wù)，及嵐皋網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都做網(wǎng)站、網(wǎng)站設(shè)計、嵐皋網(wǎng)站設(shè)計，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達到每一位用戶的要求，就會得到認(rèn)可，從而選擇與我們長期合作。這樣，我們也可以走得更遠！

一.概述

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

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

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

1.1 按照物理讀的頁面數(shù)排序 前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:計劃自編譯后在執(zhí)行期間所執(zhí)行的物理讀取總次數(shù)。

execution_count :計劃自上次編譯以來所執(zhí)行的次數(shù)。

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

creation_time:編譯計劃的時間。

query_text：執(zhí)行計劃對應(yīng)的sql腳本

后面來包括所在的數(shù)據(jù)庫ID：dbid，數(shù)據(jù)庫名稱：dbname

1.2 按照邏輯讀的頁面數(shù)排序 前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

如下圖所示：

通過上面的邏輯內(nèi)存截圖來簡要分析下：

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

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

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

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

新建索引后在來看看

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

看完上述內(nèi)容，你們對如何進行sql server性能調(diào)優(yōu) I/O開銷的深入解析有進一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝大家的支持。

網(wǎng)站欄目：如何進行sqlserver性能調(diào)優(yōu)I/O開銷的深入解析-創(chuàng)新互聯(lián)
當(dāng)前URL：http://weahome.cn/article/coijeh.html