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