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