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。
這篇寫得很亂，大家湊合看吧......
原文鏈接：http://www.cnblogs.com/caspnet/archive/2011/02/26/1965795.html
【編輯推薦】
