- 相關(guān)推薦
探究SQL Server 數(shù)據(jù)頁緩沖區(qū)的內(nèi)存瓶頸
SQL Server會把經(jīng)常使用到的數(shù)據(jù)緩存在內(nèi)存里(就是數(shù)據(jù)頁緩存)，用以提高數(shù)據(jù)訪問速度。因為磁盤訪問速度遠遠低于內(nèi)存，所以減少磁盤訪問量同樣是數(shù)據(jù)庫優(yōu)化的重要方面。
當數(shù)據(jù)頁緩存區(qū)出現(xiàn)內(nèi)存不足，則會出現(xiàn)查詢慢，磁盤忙等等問題。
分析方法：主要是用到性能計數(shù)器。
查看如下性能計數(shù)器：
1. SQL SERVER:Buffer Manager-Lazy Writes/sec:內(nèi)存不足則會頻繁調(diào)用Lazy Writer把數(shù)數(shù)據(jù)寫入磁盤，此值會經(jīng)常不為0.
2. SQL SERVER:Buffer Manager-Page life expectancy：內(nèi)存不足時，此計數(shù)器表現(xiàn)為下降趨勢或者一直停留在較低值。
3. SQL SERVER:Buffer Manager-Page reads/sec:內(nèi)存不足時，則查詢那些經(jīng)常使用但又沒有緩存在內(nèi)存里的數(shù)據(jù)時，就不需要讀取磁盤，這此值表現(xiàn)為持續(xù)上升或者停留在較高值。
4. SQL SERVER:Buffer Manager-Stolen pages:Stolen pages通常用于緩存執(zhí)行計劃，以備重用。內(nèi)存不足時，SQL Server本身機制會優(yōu)先清除執(zhí)行計劃緩存，則此值表現(xiàn)為下降或者較低水平。
查詢當前用戶任務(wù)等待：
復制代碼 代碼如下:
select * from sys.sysprocesses
如果內(nèi)存不足則，會看到較多的ASYNC_IO_COMPLETION等待類型。這是因為內(nèi)存不足時：a.內(nèi)存和磁盤間會頻繁進行交互,磁盤負載增加 b.需要讀取磁盤上的數(shù)據(jù)完成查詢，磁盤負載增加。
也就是說這時候磁盤也出現(xiàn)了性能瓶頸，但是這只是“表面”的，我們要結(jié)合多個性能指標來認清根本原因是“內(nèi)存不足”。
確定壓力來源及解決辦法：
通過前的分析，確定了數(shù)據(jù)頁緩存相關(guān)的內(nèi)存瓶頸。就要分析為什么會這樣及解決辦法。主要分為如下5個方面：
1. 外部壓力
如果OS層面或者其它應用服務(wù)需要更多的內(nèi)存，windows會壓縮Database Pages的內(nèi)存量。這時內(nèi)存壓力來自外部。可以查看如下性能計數(shù)器確定是否是外部壓力：
1. SQL Server:Memory Manager-Total Server Memory:此計數(shù)器值會下降。
2. Memory:Available Mbytes:此值會下降到較低水平。
3. 在沒有使用AWE或者Lock page in memory前提下，查看Process:Private Bytes-SqlServer和Process：Working Set-SqlServer，兩者值會有顯著下降。
解決方法：如果非DB專用服務(wù)器，則要權(quán)衡各個應用服務(wù)之間重要性來分配內(nèi)存或者加大內(nèi)存。盡量讓服務(wù)器只運行SQL Server，成為DB專用服務(wù)器。
2. SQL Server自身對Database Page的使用壓力
當Total Server Memory已經(jīng)達到設(shè)定的Max Server Memory或者無法從OS獲得更多內(nèi)存，但是經(jīng)常訪問的數(shù)據(jù)量又遠大于物理內(nèi)存用于數(shù)據(jù)緩存的容量時，SQL Server被迫將內(nèi)存的數(shù)據(jù)移入又移出，用于完成當前查詢。
觀察如下性能計數(shù)器：
1. SQL Server:Memory Manager-Total Server Memory 和 SQL Server:Memory Manager-Target Server Memory兩者值將會相等。但是前者不會大于后者。
2. 將會出現(xiàn)“分析方法”所述之情況。
解決方法：既然SQL Server沒有足夠內(nèi)存存放Database Page，那就要么增加SQL Server使用的內(nèi)存量或者減少其使用的內(nèi)存里。
增加：可以通增加物理內(nèi)存，啟用AWE等方法。
減少：可以通過橫向擴展，有兩臺或者多臺服務(wù)器分別載部分庫;優(yōu)化相關(guān)讀取量較大的語句等。
3. Buffer Pool中的Stolen Memory壓力
正常情況下Buffer Pool中的Stolen Memory不會給Database Pages造成壓力。因為Database Pages有壓力，會觸發(fā)Lazy Writes,同時SQL Server 會清理Stolen Memory中的執(zhí)行計劃緩存。
但是，如果用戶申明了過多的對象，而沒有登出，并且占用內(nèi)存過多，就會壓縮Database Pages.如：游標，自定義引用的執(zhí)行計劃等。
解決方法：通常是會表現(xiàn)為a)用戶提交的請求因內(nèi)存不足無法完成，701錯誤;b)需要壓縮某些clerk的內(nèi)存量，來完成用戶請求，造成響應延時和緩慢。
通過查詢sys.dm_os_memory_clerks的字段Single_pages_kb，找出是哪個clerk使用了過多內(nèi)存并分析其原因，然后解決之。
4. Multi-Page的壓力
multi-page跟Buffer Pool共享OS的虛擬地址空間，如果multi-page使用過多內(nèi)存，就會壓縮Datbase pages。multi-page內(nèi)存用量一般較小且相對固定，可能發(fā)生的情況有：
a. 未開啟AWE的32位SQL Server只有2G地址空間，且用-g啟動參數(shù)擴展的MemToLeave的上限。
b. 64位SQL Server調(diào)了內(nèi)存泄露的第三方代碼。
c. 使用帶有大量參數(shù)或者較長的”IN”語句
d. 調(diào)高了Network Packet Size，大于或等于8KB,并且較多這種連接。
e. 大量復雜XML查詢，或者第三代碼。
解決方法： 通過查詢sys.dm_os_memory_clerks的字段multi_pages_kb，找出是哪個clerk使用了過多內(nèi)存并分析其原因，然后解決之。
【探究SQL Server 數(shù)據(jù)頁緩沖區(qū)的內(nèi)存瓶頸】相關(guān)文章：
SQL Server常用數(shù)據(jù)類型06-18
Sql Server、Access數(shù)據(jù)排名的實現(xiàn)方法07-09
如何快速掌握SQL Server中的日志轉(zhuǎn)移08-04
有關(guān)清除SQL Server日志的兩種方法07-20
關(guān)于SQL在數(shù)據(jù)檢查中的應用10-14
用SQL實現(xiàn)查詢數(shù)據(jù)不顯示錯誤數(shù)據(jù)的方法08-19
如何使用 SQL 構(gòu)建動態(tài)數(shù)據(jù)透視表介紹09-12
oracle的sql語句01-21
SQL優(yōu)化大全09-09