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