SQL Server資源管理之內(nèi)存管理篇（下）
在上篇文章SQL Server資源管理之內(nèi)存管理篇（上），介紹了SQL Server的內(nèi)存管理的一些理論知識，這篇利用這些知識來解決現(xiàn)實中常見的一些問題。
一、數(shù)據(jù)頁緩存壓力的調(diào)優(yōu)
前篇我們說過，如果用戶訪問的數(shù)據(jù)頁面都緩存在內(nèi)存里，這樣的相應(yīng)速度是最快的。但是現(xiàn)實中，數(shù)據(jù)庫的大小都是大于物理內(nèi)存的，SQL Server不可能將用戶需要的所有數(shù)據(jù)都緩存在內(nèi)存中，當(dāng)用戶需要的數(shù)據(jù)不在內(nèi)存中，將會發(fā)生Paging動作從硬盤中讀取需要的數(shù)據(jù)，偶爾的Paging不會從整體上影響SQL Server的性能，但如果Paging動作經(jīng)常發(fā)生將會嚴(yán)重影響SQL Server整體性能。
當(dāng)我們進(jìn)行數(shù)據(jù)頁緩存的調(diào)優(yōu)時，第一步先是確定是否有數(shù)據(jù)頁緩存的壓力，第二步是確定數(shù)據(jù)頁緩存頁的壓力是由哪里引起的，主要可以分成外部壓力和內(nèi)部壓力。
1、是否有數(shù)據(jù)頁緩存壓力
確定是否有數(shù)據(jù)頁緩存壓力，主要可以從下面的一些內(nèi)存性能計數(shù)器和sys.sysProcesses來確認(rèn)。
SQL Server：Buffer Manager-Lazy Writes/Sec的值經(jīng)常發(fā)生。
SQL Server：Buffer Manager-Page Life Expectancy的經(jīng)常反復(fù)變化，始終升不上去。
SQL Server：Buffer Manager-Page Reads/Sec的值經(jīng)常不為0。
從sys.sysprocesses這一系統(tǒng)視圖的wait_type中能看到ASYNC_IO_COMPLETION值，這一值代表的意思是“等待I/O操作的完成”，這通常代表內(nèi)存不足發(fā)生了硬盤讀寫，也可能有人會說這是硬盤的速度太慢導(dǎo)致的，只要換上速度快的硬盤就能解決這個問題了。確實換上速度快的硬盤能使SQL Server的響應(yīng)速度提高一些，但是如果上面那三個計數(shù)器的值經(jīng)常，那硬盤的問題就不是主要問題，它只是內(nèi)存不夠(因)導(dǎo)致的硬盤讀寫(果)，根本原因還是在內(nèi)存上。
從上面的分析中，可以確認(rèn)系統(tǒng)中存在數(shù)據(jù)頁緩存壓力，現(xiàn)在就來分析這一壓力的來源，是外部壓力還是內(nèi)部壓力。
2、壓力的來源
1)外部壓力
SQL Server：Buffer Manager-Total Server Memory的值是否變小了。如果變小了那就說明是，SQL Server的能使用的內(nèi)存被系統(tǒng)或者外部程序給壓縮了。這就是外部壓力。
2)內(nèi)部壓力
SQL Server：Buffer Manager-Total Server Memory的值沒什么變化，但是和SQL Server：Buffer Manager-Target Server Memory的大小基本相等。這就是SQL Server的數(shù)據(jù)頁的內(nèi)存需求已經(jīng)等于了系統(tǒng)能提供的內(nèi)存大小了。說明是數(shù)據(jù)庫內(nèi)部壓力。
3、解決辦法
1)外部壓力
發(fā)生外部壓力的大多數(shù)情形都是由于系統(tǒng)中還運行了其他的服務(wù)器軟件，在它需要內(nèi)存的時候搶掉了SQL Server的內(nèi)存。因此解決方案也就是將SQL Server運行在專門的服務(wù)器上。還有一種情形會導(dǎo)致外部壓力的發(fā)生，那就是操作系統(tǒng)在占用大量內(nèi)存的操作(比如備份)，解決方案就是將這些操作方到SQL Server運行壓力小的時候(比如凌晨1、2點的時候)。
2)內(nèi)部壓力
a、找出讀取數(shù)據(jù)頁面最多的語句，對它進(jìn)行調(diào)優(yōu)。找出這些語句可以通過sys.dm_exec_query_status動態(tài)視圖和sys.dm_exec_sql_text動態(tài)函數(shù)的關(guān)聯(lián)查詢。
-- 物理讀取頁面最多的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
找出這些語句然后經(jīng)可以用語句調(diào)優(yōu)的方式來進(jìn)行調(diào)優(yōu)了。
b、如果你認(rèn)為語句已經(jīng)沒有調(diào)優(yōu)的空間了，或者像快速的提高服務(wù)器性能就只能增加物理內(nèi)存了。
二、Buffer Pool中的Stolen Memory的壓力調(diào)優(yōu)
1、通過Memory Clerk的分析
由于Buffer Pool里的Stolen內(nèi)存都是SQL Server自己申請的，所以在Memory Clerk的動態(tài)管理視圖里可以查看。通過分析各Clerk的大小，基本就能判斷Stolen內(nèi)存壓力的來源。常見的使用Stolen的內(nèi)存較多的Memory Clerk。
a)CACHESTORE_SQLCP：緩存動態(tài)TSQL語句的執(zhí)行計劃的地方。這通常和程序員的代碼有關(guān)，如果程序員習(xí)慣使用動態(tài)TSQL語句，這部分的內(nèi)存中緩存的執(zhí)行計劃就會非常大。解決方法就是使用存儲過程或者參數(shù)話的TSQL。
b)OBJECTSTORE_LOCK_MANAGER：SQL Server里鎖結(jié)構(gòu)使用的內(nèi)存。如果SQL Server中的阻塞嚴(yán)重的話，這部分內(nèi)存的內(nèi)存使用量會很大。解決方案就是解決阻塞問題了。
2、通過sys.sysprocesses里面的waittype字段進(jìn)行分析
1)CMEMTHREAD(0X00B9)
當(dāng)多個用戶向同一緩存區(qū)中申請內(nèi)存或者釋放內(nèi)存，在某一時刻只會有一個連接的操作可以成功，其他的連接必須等待。這種情況比較少，主要是發(fā)生在哪些并發(fā)度非常高的系統(tǒng)中，而且通常都是在編譯動態(tài)的TSQL語句。解決方案就是使用存儲過程或者參數(shù)化的TSQL語句，提高執(zhí)行計劃的重用。
2)RESOURCE_SEMAPHORE_QUERY_COMPLIE(0X011A)
當(dāng)用戶傳送過的語句或者調(diào)用的存儲過程過分復(fù)雜，SQL Server編譯它所需要的內(nèi)存會非常大。SQL Server為了防止過多的內(nèi)存被用來做編譯動作，所以設(shè)置了編譯內(nèi)存的上限。當(dāng)有太多復(fù)雜的語句同時在編譯，編譯所需要的內(nèi)存可能達(dá)到這個上限，這將有部分語句將處于等待內(nèi)存進(jìn)行編譯的狀態(tài)，也就該waittype。
解決方法有：盡量多的使用存儲過程或參數(shù)化的TSQL語句，簡化每次需編譯的語句復(fù)雜度，分成幾個存儲過程，實在不行的話可以考慮定期運行DBCC FREEPROCCACHE語句來手工清除緩存中的執(zhí)行計劃，保證stolen中內(nèi)存量。
三、Multi-Page Memory壓力調(diào)優(yōu)
由于32位的SQL Server會在啟動的時候分配好Multi-Page的大小而且比較小，默認(rèn)是384MB，因此對于32位的SQL Server比較容易發(fā)生Multi-Page Memory的壓力。該部分的壓力主要可能由下面三種情形導(dǎo)致。
1、程序連接數(shù)據(jù)庫時的Network Packet Size大小，如果設(shè)置成8KB或者更高的時候，而且連接又非常大時。對于32位的SQL Server該部分的內(nèi)存使用量會很快達(dá)到上限。解決方法就是將程序中設(shè)置的Network Packet Size改成默認(rèn)的4KB，或者升級到64位SQL Server，這樣Multi-Page的大小就沒有限制了。
2、程序員使用了很多復(fù)雜的TSQL語句或者存儲過程，它的執(zhí)行計劃超過了8KB，這將占用Multi-Page的空間。由于32位的SQL Server中該部分的大小比較小，它將很快被填滿，而由于Buffer Pool很大沒有壓力，它將不會觸發(fā)Lazy Writer，Mullti-Page中的執(zhí)行計劃將不會被清理。而這時如果用戶需要申請Multi-Page Memory就必須等待。這會體現(xiàn)在sys.sysprocessed的waittype字段上，該值等于SOS_RESERVEDMEMBLOCKLIST。解決方案：語句進(jìn)行調(diào)整，將它的執(zhí)行計劃控制在8KB以內(nèi)，如果不行的話可以考慮定期運行DBCC FREEPROCCACHE語句來手工清理執(zhí)行計劃，或者升級到64位SQL Server。
這篇寫得很亂，大家湊合看吧......
原文鏈接：http://www.cnblogs.com/caspnet/archive/2011/02/26/1965795.html
【編輯推薦】
