SQL Server資源管理之記憶體管理篇（上）

標籤：des   blog   http   io   os   ar   使用   for   strong   

對SQL Server來說，最重要的資源是記憶體、Disk和CPU，其中記憶體又是重中之重，因為SQL Server為了效能要求，會將它所要訪問的資料全部（只要記憶體足夠）放到緩衝中。這篇就來介紹SQL Server的記憶體管理體系。

SQL Server作為Windows上啟動並執行應用程式，必須接受Windows的資源管理，利用Windows的API來申請和調度各類資源。但是，由於Windows的資源管理體系，是為了滿足大多數的應用程式所設計的，這對於SQL Server這種定位於企業級、支援多使用者和高並發性的資料庫應用程式來說不是很適合，為此SQL Server開發了自己的一套資源管理體系——SQLOS(SQL作業系統)。也就是說SQL Server的資源管理分兩層，第一層是在Windows上，通過Windows的API來申請資源。第二層是在SQL Server上，利用SQLOS來決定如何使用從Windows那裡申請來的資源。

一、作業系統層面的SQL Server記憶體管理

由於SQL server的記憶體是通過Windows的API來申請的，如果Windows自己本身就缺少記憶體，SQL Server由於申請不到記憶體，效能自然受影響。因此做SQL Server的記憶體檢測，第一步就是查看系統層面的記憶體，以確保系統本身不缺記憶體，這一步簡單但是必不可少。這裡先介紹Windows的一些記憶體管理理念，然後介紹如何檢查系統的記憶體情況。

1、Windows的一些記憶體術語

Virtual Address Space（虛擬位址空間）：應用程式能夠申請訪問的最大地址空間。對於32位的伺服器，地址定址空間為2的32次方，也就是4GB，但是這4GB並不是都給SQL Server使用的，預設情況下是使用者態2GB，核心態2GB，所以說對於32位的系統SQL Server只有2GB的記憶體可供使用。不過可以通過設定/3GB boot.int參數，來調整系統的配置，使使用者態為3GB，核心態為1GB。或者開啟AWE（地址空間擴充），將定址空間擴充為64GB，不過該設定有缺陷，下面會分析。

Physical Memory（實體記憶體）：也就是通常所說的電腦的記憶體大小。

Reserved Memory（保留地址）：應用程式訪問記憶體的方式之一，先保留（Reserve）一塊記憶體位址空間，留著將來使用（SQL Server中的資料頁面使用的記憶體就是通過這個方式申請  的）。被保留的地址空間，不能被其他程式訪問，不然會出現訪問越界的報錯提示。

Committed Memory（提交記憶體）：將保留（Reserve）的記憶體頁面正式提交（Commit）使用。

Shared Memory（共用記憶體）：對一個以上進程可見的記憶體。

Private Bytes（私人記憶體）：某進程提交的地址空間中，非共用的部分。

Working Set：進程的地址空間中存放在實體記憶體中的部分。

Page Fault（頁面訪問錯誤）：訪問在虛擬位址空間，但不存在於Working Set中會發生Page Fault。這個又分兩種情況，第一種是目標頁面在硬碟上，這鐘訪問會帶來硬碟讀寫，這種稱為Hard Fault。另外一種是目標頁面在實體記憶體中，但是不是該進程的Working Set下，Windows只需要重新定向一下，成為Soft Fault。由於Soft Hard不帶來硬碟讀寫，對系統的效能影響很小，因此管理員關心的是Hard Fault。

System Working Set：Windows系統的Working Set。

2、Windows的記憶體檢測

可以通過Windows的效能監控器來檢測Windows的記憶體使用量情況，如何使用效能監控器，可以看這篇文章《使用“效能監控器”監視系統效能/運行情況》 。在檢測記憶體上，比較重要的計數器有下面一些：

分析Windows系統的記憶體總體使用方式的計數器：

Memory：Available MBytes：系統中閒置實體記憶體數。

 

Memory：Pages/Sec：由於Hard Page的發生，每秒鐘從硬碟中讀取或者寫入的頁面數。該計數器等於Memory：Pages Input/Sec與Memory：Pages Output/Sec之和。
分析Windows系統自身的記憶體使用量情況的計數器：

Memory：Cache Bytes：系統的Working Set，也就是Windows系統使用的實體記憶體數。

 

對於每個進程的記憶體使用量情況的計數器：

Process：Private Bytes：進程提交的地址空間中非共用的部分。

 

Process：Working Set：進程的地址空間中存放在實體記憶體中的那部分。

從這些計數器中，我們可以看到系統中是否還有空閑記憶體，哪個進程使用的記憶體最多，在發生問題的時候是否有記憶體使用量量突變等情況。這為接下來分析SQL Server的使用提供一個前提條件。

二、SQL Server內部的記憶體管理

1、記憶體使用量分類

按用途分類

1）Database cache（資料頁面）。SQL Server中的頁面都是以8KB為一個頁面儲存的。當SQL Server需要用到某個頁面時，它會將該頁面讀到記憶體中，使用完後會緩衝在記憶體中。在記憶體沒有壓力的情況下，SQL Server不會將頁面從記憶體中刪除。如果SQL Server感覺到記憶體的壓力時，會將最長時間沒有使用的頁面從記憶體中刪除來空出記憶體。

2）各類Consumer（功能組件）

 

Connection的串連資訊

General：一組大雜燴。語句的編譯、範式化、每個鎖資料結構、事務上下文、表格和索引的中繼資料等

Query Plan：語句和預存程序的執行計畫。和Database cache類似，SQL Server也會將執行計畫緩衝以供將來使用，減少編譯時間。

 

Optimizer：產生執行計畫的過程中消耗的記憶體。

Utilities：像BCP、Log Manager、Backup等比較特殊的操作消耗的記憶體。

3）線程記憶體：存放進程內每個線程的資料結構和相關資訊消耗的記憶體，每個線程需0.5MB的記憶體。

4）第三方代碼消耗的記憶體：SQL Server的進程裡，會運行一些非SQL Server自身的代碼。例如：使用者定義的CLR或Extended Stored Procedure代碼。

按申請方式分類

 

1）預先Reserve一塊大的記憶體，然後在使用的時候一塊一塊的Commit。Database Page是按這種方式申請的。

2）直接用Commit方式申請的記憶體，成為Stolen方式。除了Database Page之外其他記憶體基本都是按這種方式申請的。

 

按申請記憶體的大小分類

1）申請小於等於8KB為一個單位的記憶體，這些記憶體稱為Buffer Pool

 

2）申請大於8KB為一個單位的記憶體，這些記憶體稱為Multi-Page（或MemToLeave）

 

SQL Server對於Database Page都是採用先Reserved後Commit的方式申請的，而資料頁都是以8KB為單位進行申請的。

對於Consumer中的記憶體申請，一般都是按Stolen方式申請的，且大多數的執行計畫的大小都是小於8KB的，少數特別複雜的預存程序的執行計畫會超過8KB，預設的串連的資料包是4KB，除非用戶端特別設定了超過8KB（不建議）

第三方代碼的記憶體申請一般是按Stolen方式申請的，個別比如CLR中可能會用Reserved/Commit的方式申請。

線程的記憶體每個都以0.5MB的方式申請，自然是放在MemToLeave中。

之所以花了這麼大篇幅來講SQL Server的記憶體分類，是因為SQL Server尤其是32位的SQL Server對不同種類的記憶體的申請大小是不一樣的，對Commit、Stolen和MemTOLeave等類型的記憶體是有限制的。因此會出現系統中還有空閑記憶體，但是SQL Server不會申請使用的現象。

2、各部分記憶體的大小限制

 

1）32位的Windows

 

在SQL Server啟動時，會預先分配好MemToLeave地區的大小。預設大小為256MB+256（SQL Server配置的允許最大線程數）* 0.5MB=384MB，因此Buffer Pool中的最大值為2GB-384MB=1.664G。如果使用了AWE技術，可以將系統的擴充地址空間達到64GB，但由於AWE擴充出來的地址只能用Reserved/Commit方式申請，為此MemToLeave的記憶體還是384MB，Buffer Pool中的Stolen的最大記憶體為1.664G，剩餘的記憶體都可以為Database Page頁面使用。

2）64位的Windows

32位的SQL Server。由於64位的作業系統，核心態不再佔用32位進程的虛擬位址空間，因此MemToLeave的大小還是為384MB，Buffer Pool可以達到3.664G。如果還開啟了AWE，這3.664GB可以全部用於Buffer Pool中的Stolen，剩餘的記憶體都可以給Database Page頁面使用。不過這種情況很少見，哪裡用64位作業系統的機器裝32位的哦-_- 。

64位的SQL Server。所有的記憶體都無限申請的，有需要就申請。

 

3、SQL Server記憶體使用量情況的分析

一般來說有兩種方式，第一種就是用來分析系統記憶體情況時使用的用效能計數器來分析，第二種是使用動態管理檢視（DMV，只適用於SQL Server2005和2008）

1）SQL Server效能計數器

SQLServer：Memory Manager：Total Server Memory（KB)：SQL Server緩衝區提交的記憶體。不是SQL Server總的使用記憶體，只是Buffer Pool中的大小。

SQLServer：Memory Manager：Target Server Memory（KB）：伺服器可供SQL Server使用的記憶體量。一般是由SQL Server能訪問到的記憶體量和SQL Server的sp_Configure配置中的Max Server Memory值中的較小值算得。

 

SQLServer：Memory Manger：Memory Grants Pending：等待記憶體授權的進程總數。如果該值不為0，說明當前有使用者的記憶體申請由於記憶體壓力被延遲，這意味著比較嚴重的記憶體瓶頸。

SQLServer：Buffer Manager：Buffer Cache Hit Ratio：資料從緩衝區中找到而不需要從硬碟中去取的百分比。SQL Server在運行一段時間後，該比率的變化應該很小，而且都應該在98%以上，如果在95%以下，說明有記憶體不足的問題。

SQLServer：Buffer Manager：Lazy Writes/Sec：每秒鐘被惰性編輯器（Lazy writer）寫入的緩衝數。當SQL Server感覺到記憶體壓力的時候，會將最久沒有使用的資料頁面和執行計畫從緩衝池中清理掉，做這個動作的就是Lazy Writer。
 Page Life Expectancy：頁面不被引用後，在緩衝池中停留的秒數。在記憶體沒有壓力的情況下，頁面會一直待在緩衝池中，Page Life Expectancy會維持在一個比較高的值，如果有記憶體壓力時，Page Life Expectancy會下降。所以如果Page Life Expectancy不能維持在一個值上，就代表SQLServer有記憶體瓶頸。 
 SQLServer：Buffer Manager：Database Pages ：就是Database Cache的大小。
 SQLServer：Buffer Manager：Free Pages：SQL Server中空閑可用的大小。

 

SQLServer：Buffer Manager：Stolen Pages：Buffer Pool中Stolen的大小。

SQLServer：Buffer Manager：Total Pages：Buffer Pool的總大小（等於Database Pages+Free Pages+Stolen Pages）。該值乘以8KB，應該等於Memory Manager：Total Server Memory的值。

從上面這些計數器中我們就能瞭解SQL Server的記憶體使用量情況，結合前面說的系統層的計數器大概能看出是否存在記憶體瓶頸。

 

2）記憶體動態管理檢視

在SQL Server 2005以後，SQL Server的記憶體管理是使用Memory Clerk的方式統一管理。所有的SQL Server的記憶體的申請或釋放，都需要通過它們的Clerk，SQL Server也通過這些Clerk的協調來滿足不同需求。通過查詢這些DMV，可以得到比用效能計數器更加詳細的記憶體使用量情況。

我們可以通過下面的查詢語句來檢測SQL Server的Clerk的記憶體使用量情況。

使用sys.dm_os_memory_clerks查看記憶體使用量情況

1. SELECT type, --Clerk的類型  
2.     sum(virtual_memory_reserved_kb) as vm_Reserved_kb, -- 保留的記憶體  
3.     sum(virtual_memory_committed_kb) as vm_Committed_kb, --提交的記憶體  
4.     sum(awe_allocated_kb) as awe_Allocated_kb, -- 開啟AWE後使用的記憶體  
5.     sum(shared_memory_reserved_kb) as sm_Reserved_kb, -- 共用的保留記憶體  
6.     sum(shared_memory_committed_kb) as sm_Committed_kb, -- 共用的提交記憶體  
7.     sum(single_pages_kb) as SinlgePage_kb, -- Buffer Pool中的Stolen的記憶體  
8.     sum(multi_pages_kb) as MultiPage_kb -- MemToLeave的記憶體  
9. FROM sys.dm_os_memory_clerks   
10. GROUP BY type  
11. ORDER BY type 

從上面的查詢語句，我們可以算出前面提到的記憶體大小

1. Reserved/Commit = sum(virtual_memory_reserved_kb) / sum(virtual_memory_committed_kb)  
2. Stolen = sum(single_pages_kb) + sum(multi_pages_kb)  
3. Buffer Pool = sum(virtual_memory_committed_kb) + sum(single_pages_kb)  
4. MemToLeave = sum(multi_pages_kb) 

通過上面的介紹我們可以知道SQL Server總體和各部分記憶體的使用方式，如果我想知道資料頁的緩衝中到底緩衝了哪些資料，這些資料是屬於哪個資料庫的哪個表中的呢？執行計畫又是緩衝了哪些語句的執行計畫呢？這也可以通過DMV查看的到。

查看記憶體中的資料頁面緩衝的是哪個資料庫的哪個表格的資料

 

1. declare @name nvarchar(100)  
2. declare @cmd nvarchar(1000)  
3. declare dbnames cursor for 
4. select name from master.dbo.sysdatabases  
5. open dbnames  
6. fetch next from dbnames into @name 
7. while @@fetch_status = 0  
8. begin 
9. set @cmd = ‘select b.database_id, db=db_name(b.database_id),p.object_id,p.index_id,buffer_count=count(*) from ‘   
10. --這裡的object_id代表是SQL Server中的對象號,index_id代表是索引號,buffer_count代表的是頁面數  
11. + @name + ‘.sys.allocation_units a, ‘ 
12. + @name + ‘.sys.dm_os_buffer_descriptors b, ‘ + @name + ‘.sys.partitions p  
13. where a.allocation_unit_id = b.allocation_unit_id  
14. and a.container_id = p.hobt_id  
15. and b.database_id = db_id(‘‘‘ + @name + ‘‘‘)  
16. group by b.database_id,p.object_id, p.index_id  
17. order by b.database_id, buffer_count desc‘   
18. exec (@cmd)  
19. fetch next from dbnames into @name 
20. end 
21. close dbnames  
22. deallocate dbnames  
23. go 

-- 根據上面取出來的@object_id找出是哪個資料庫的哪個表

1. SELECT    s.name AS table_schema, o.name as table_name --使用的就是table_schema.table_name表  
2. FROM    sys.sysobjects AS o INNER JOIN 
3.           sys.schemas AS s ON o.uid = s.schema_id  
4. WHERE    (o.id = @object_id) 

-- 根據上面取出來的@object_id和@index_id找出索引的名稱

1. SELECT    id, indid, name as index_name -- index_name就是索引的名稱  
2. FROM    sys.sysindexes  
3. WHERE    (id = @object_id) AND (indid = @index_id) 

-- 根據上面取出來的表名table_schema.table_name和索引的名稱index_name，還可以找出該索引是建立在哪些欄位上的

1. EXEC sp_helpindex ‘table_schema.table_name‘ 

查看記憶體中緩衝的執行計畫，以及執行計畫對應的語句：

-- 輸出可能較大，請小心使用

 

1. SELECT    usecounts, refcounts, size_in_bytes, cacheobjtype, objtype, text   
2. FROM    sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle)   
3. ORDER BY objtype DESC 

寫了這麼多竟然發現大多數講的還是資料收集的這一部分，相應的解決辦法還沒有講到。。。由於文章太長，具體的解決方案將在下一篇講解，下一篇將從Database Page、Stolen和Multi-Page三部分的具體瓶頸來講解。

原文連結：http://www.cnblogs.com/caspnet/archive/2011/02/21/1959539.html

SQL Server資源管理之記憶體管理篇（上）