SQL Server效能調優：資源管理之記憶體管理篇（下）

在上篇文章SQL Server效能調優：資源管理之記憶體管理篇（上），介紹了SQL Server的記憶體管理的一些理論知識，這篇利用這些知識來解決現實中常見的一些問題。

 

一、資料頁緩衝壓力的調優

前篇我們說過，如果使用者訪問的資料頁面都緩衝在記憶體裡，這樣的相應速度是最快的。但是現實中，資料庫的大小都是大於實體記憶體的，SQL Server不可能將使用者需要的所有資料都緩衝在記憶體中，當使用者需要的資料不在記憶體中，將會發生Paging動作從硬碟中讀取需要的資料，偶爾的Paging不會從整體上影響SQL Server的效能，但如果Paging動作經常發生將會嚴重影響SQL Server整體效能。

當我們進行資料頁緩衝的調優時，第一步先是確定是否有資料頁緩衝的壓力，第二步是確定資料頁快取頁面的壓力是由哪裡引起的，主要可以分成外部壓力和內部壓力。

1、是否有資料頁緩衝壓力

確定是否有資料頁緩衝壓力，主要可以從下面的一些記憶體效能計數器和sys.sysProcesses來確認。

SQL Server：Buffer Manager-Lazy Writes/Sec的值經常發生。

SQL Server：Buffer Manager-Page Life Expectancy的經常反覆變化，始終升不上去。

SQL Server：Buffer Manager-Page Reads/Sec的值經常不為0。

從sys.sysprocesses這一系統檢視表的wait_type中能看到ASYNC_IO_COMPLETION值，這一值代表的意思是“等待I/O操作的完成”，這通常代表記憶體不足發生了硬碟讀寫，也可能有人會說這是硬碟的速度太慢導致的，只要換上速度快的硬碟就能解決這個問題了。確實換上速度快的硬碟能使SQL Server的響應速度提高一些，但是如果上面那三個計數器的值經常，那硬碟的問題就不是主要問題，它只是記憶體不夠（因）導致的硬碟讀寫（果），根本原因還是在記憶體上。

從上面的分析中，可以確認系統中存在資料頁緩衝壓力，現在就來分析這一壓力的來源，是外部壓力還是內部壓力。

 

2、壓力的來源

1）外部壓力

SQL Server：Buffer Manager-Total Server Memory的值是否變小了。如果變小了那就說明是，SQL Server的能使用的記憶體被系統或者外部程式給壓縮了。這就是外部壓力。

2）內部壓力

SQL Server：Buffer Manager-Total Server Memory的值沒什麼變化，但是和SQL Server：Buffer Manager-Target Server Memory的大小基本相等。這就是SQL Server的資料頁的記憶體需求已經等於了系統能提供的記憶體大小了。說明是資料庫內部壓力。

 

3、解決辦法

1）外部壓力

發生外部壓力的大多數情形都是由於系統中還運行了其他的伺服器軟體，在它需要記憶體的時候搶掉了SQL Server的記憶體。因此解決方案也就是將SQL Server運行在專門的伺服器上。還有一種情形會導致外部壓力的發生，那就是作業系統在佔用大量記憶體的操作（比如備份），解決方案就是將這些操作方到SQL Server運行壓力小的時候（比如淩晨1、2點的時候）。

2）內部壓力

a、找出讀取資料頁面最多的語句，對它進行調優。找出這些語句可以通過sys.dm_exec_query_status動態視圖和sys.dm_exec_sql_text動態函數的關聯查詢。

-- 物理讀取頁面最多的100條語句
SELECT TOP 100
    qs.total_physical_reads,qs.execution_count,
    qs.total_physical_reads /qs.execution_count as avg_io,
    qt.text, db_name(qt.dbid) as dbname, qt.objectid
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
GO

-- 邏輯讀取頁面最多的100條語句
SELECT TOP 100
    qs.total_logical_reads,qs.execution_count,
    qs.total_logical_reads /qs.execution_count as avg_io,
    qt.text, db_name(qt.dbid) as dbname
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
GO

找出這些語句然後經可以用語句調優的方式來進行調優了。

b、如果你認為語句已經沒有調優的空間了，或者像快速的提高伺服器效能就只能增加實體記憶體了。

 

二、Buffer Pool中的Stolen Memory的壓力調優

1、通過Memory Clerk的分析

由於Buffer Pool裡的Stolen記憶體都是SQL Server自己申請的，所以在Memory Clerk的動態管理檢視裡可以查看。通過分析各Clerk的大小，基本就能判斷Stolen記憶體壓力的來源。常見的使用Stolen的記憶體較多的Memory Clerk。

a）CACHESTORE_SQLCP：緩衝動態TSQL語句的執行計畫的地方。這通常和程式員的代碼有關，如果程式員習慣使用動態TSQL語句，這部分的記憶體中緩衝的執行計畫就會非常大。解決方案就是使用預存程序或者參數話的TSQL。

b)OBJECTSTORE_LOCK_MANAGER：SQL Server裡鎖結構使用的記憶體。如果SQL Server中的阻塞嚴重的話，這部分記憶體的記憶體使用量量會很大。解決方案就是解決阻塞問題了。

 

2、通過sys.sysprocesses裡面的waittype欄位進行分析

1）CMEMTHREAD（0X00B9）

當多個使用者向同一緩衝區中申請記憶體或者釋放記憶體，在某一時刻只會有一個串連的操作可以成功，其他的串連必須等待。這種情況比較少，主要是發生在哪些並發度非常高的系統中，而且通常都是在編譯動態TSQL語句。解決方案就是使用預存程序或者參數化的TSQL語句，提高執行計畫的重用。

2）RESOURCE_SEMAPHORE_QUERY_COMPLIE（0X011A）

當使用者傳送過的語句或者調用的預存程序過分複雜，SQL Server編譯它所需要的記憶體會非常大。SQL Server為了防止過多的記憶體被用來做編譯動作，所以設定了編譯記憶體的上限。當有太多複雜的語句同時在編譯，編譯所需要的記憶體可能達到這個上限，這將有部分語句將處於等待記憶體進行編譯的狀態，也就該waittype。

解決方案有：盡量多的使用預存程序或參數化的TSQL語句，簡化每次需編譯的語句複雜度，分成幾個預存程序，實在不行的話可以考慮定期運行DBCC FREEPROCCACHE語句來手工清除緩衝中的執行計畫，保證stolen中記憶體量。

 

三、Multi-Page Memory壓力調優

由於32位的SQL Server會在啟動的時候分配好Multi-Page的大小而且比較小，預設是384MB，因此對於32位的SQL Server比較容易發生Multi-Page Memory的壓力。該部分的壓力主要可能由下面三種情形導致。

1、程式串連資料庫時的Network Packet Size大小，如果設定成8KB或者更高的時候，而且串連又非常大時。對於32位的SQL Server該部分的記憶體使用量量會很快達到上限。解決方案就是將程式中設定的Network Packet Size改成預設的4KB，或者升級到64位SQL Server，這樣Multi-Page的大小就沒有限制了。

2、程式員使用了很多複雜的TSQL語句或者預存程序，它的執行計畫超過了8KB，這將佔用Multi-Page的空間。由於32位的SQL Server中該部分的大小比較小，它將很快被填滿，而由於Buffer Pool很大沒有壓力，它將不會觸發Lazy Writer，Mullti-Page中的執行計畫將不會被清理。而這時如果使用者需要申請Multi-Page Memory就必須等待。這會體現在sys.sysprocessed的waittype欄位上，該值等於SOS_RESERVEDMEMBLOCKLIST。解決方案：語句進行調整，將它的執行計畫控制在8KB以內，如果不行的話可以考慮定期運行DBCC FREEPROCCACHE語句來手工清理執行計畫，或者升級到64位SQL Server。

 

這篇寫得很亂，大家湊合看吧。。。