SQL Server性能調優：資源管理之記憶體管理篇

對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檢視記憶體使用情況 SELECT  type,  -- Clerk的類型

     sum (virtual_memory_reserved_kb)  as  vm_Reserved_kb,  --  保留的記憶體

     sum (virtual_memory_committed_kb)  as  vm_Committed_kb,  -- 送出的記憶體

     sum (awe_allocated_kb)  as  awe_Allocated_kb,  --  開啟AWE後使用的記憶體

     sum (shared_memory_reserved_kb)  as  sm_Reserved_kb,  --  共享的保留記憶體

     sum (shared_memory_committed_kb)  as  sm_Committed_kb,  --  共享的送出記憶體

     sum (single_pages_kb)  as  SinlgePage_kb,  --  Buffer Pool中的Stolen的記憶體

     sum (multi_pages_kb)  as  MultiPage_kb  --  MemToLeave的記憶體

FROM  sys.dm_os_memory_clerks 

GROUP   BY  type

ORDER   BY  type

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

 Reserved/Commit = sum(virtual_memory_reserved_kb) / sum(virtual_memory_committed_kb)

 Stolen = sum(single_pages_kb) + sum(multi_pages_kb)

 Buffer Pool = sum(virtual_memory_committed_kb) + sum(single_pages_kb)

 MemToLeave = sum(multi_pages_kb)

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

檢視記憶體中的資料頁面緩存的是哪個資料庫的哪個表格的資料 declare   @name   nvarchar ( 100 )

declare   @cmd   nvarchar ( 1000 )

declare  dbnames  cursor   for

select  name  from  master.dbo.sysdatabases

open  dbnames

fetch   next   from  dbnames  into   @name

while   @@fetch_status   =   0

begin

set   @cmd   =   ' select b.database_id, db=db_name(b.database_id),p.object_id,p.index_id,buffer_count=count(*) from  '  

-- 這裡的object_id代表是SQL Server中的對象号,index_id代表是索引号,buffer_count代表的是頁面數

+   @name   +   ' .sys.allocation_units a,  '

+   @name   +   ' .sys.dm_os_buffer_descriptors b,  '   +   @name   +   ' .sys.partitions p

where a.allocation_unit_id = b.allocation_unit_id

and a.container_id = p.hobt_id

and b.database_id = db_id( '''   +   @name   +   ''' )

group by b.database_id,p.object_id, p.index_id

order by b.database_id, buffer_count desc '  

exec  ( @cmd )

fetch   next   from  dbnames  into   @name

end

close  dbnames

deallocate  dbnames

go

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

SELECT     s.name  AS  table_schema, o.name  as  table_name  -- 使用的就是table_schema.table_name表

FROM     sys.sysobjects  AS  o  INNER   JOIN

          sys.schemas  AS  s  ON  o.uid  =  s.schema_id

WHERE     (o.id  =   @object_id )

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

SELECT     id, indid, name  as  index_name  --  index_name就是索引的名稱

FROM     sys.sysindexes

WHERE     (id  =   @object_id )  AND  (indid  =   @index_id )

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

EXEC  sp_helpindex  ' table_schema.table_name '

檢視記憶體中緩存的執行計劃，以及執行計劃對應的語句：

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

SELECT     usecounts, refcounts, size_in_bytes, cacheobjtype, objtype,  text  

FROM     sys.dm_exec_cached_plans cp  CROSS  APPLY sys.dm_exec_sql_text(plan_handle) 

ORDER   BY  objtype  DESC

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

一、資料頁緩存壓力的調優

前篇我們說過，如果使用者通路的資料頁面都緩存在記憶體裡，這樣的相應速度是最快的。但是現實中，資料庫的大小都是大于實體記憶體的，SQL Server不可能将使用者需要的所有資料都緩存在記憶體中，當使用者需要的資料不在記憶體中，将會發生Paging動作從硬碟中讀取需要的資料，偶爾的Paging不會從整體上影響SQL Server的性能，但如果Paging動作經常發生将會嚴重影響SQL Server整體性能。

當我們進行資料頁緩存的調優時，第一步先是确定是否有資料頁緩存的壓力，第二步是确定資料頁緩存頁的壓力是由哪裡引起的，主要可以分成外部壓力和内部壓力。

1、是否有資料頁緩存壓力

确定是否有資料頁緩存壓力，主要可以從下面的一些記憶體性能計數器和sys.sysProcesses來确認。

SQL Server：Buffer Manager-Lazy Writes/Sec的值經常發生。

SQL Server：Buffer Manager-Page Life Expectancy的經常反複變化，始終升不上去。

SQL Server：Buffer Manager-Page Reads/Sec的值經常不為0。

從sys.sysprocesses這一系統視圖的wait_type中能看到ASYNC_IO_COMPLETION值，這一值代表的意思是“等待I/O操作的完成”，這通常代表記憶體不足發生了硬碟讀寫，也可能有人會說這是硬碟的速度太慢導緻的，隻要換上速度快的硬碟就能解決這個問題了。确實換上速度快的硬碟能使SQL Server的響應速度提高一些，但是如果上面那三個計數器的值經常，那硬碟的問題就不是主要問題，它隻是記憶體不夠（因）導緻的硬碟讀寫（果），根本原因還是在記憶體上。

從上面的分析中，可以确認系統中存在資料頁緩存壓力，現在就來分析這一壓力的來源，是外部壓力還是内部壓力。

2、壓力的來源

1）外部壓力

SQL Server：Buffer Manager-Total Server Memory的值是否變小了。如果變小了那就說明是，SQL Server的能使用的記憶體被系統或者外部程式給壓縮了。這就是外部壓力。

2）内部壓力

SQL Server：Buffer Manager-Total Server Memory的值沒什麼變化，但是和SQL Server：Buffer Manager-Target Server Memory的大小基本相等。這就是SQL Server的資料頁的記憶體需求已經等于了系統能提供的記憶體大小了。說明是資料庫内部壓力。

3、解決辦法

1）外部壓力

發生外部壓力的大多數情形都是由于系統中還運作了其他的伺服器軟體，在它需要記憶體的時候搶掉了SQL Server的記憶體。是以解決方案也就是将SQL Server運作在專門的伺服器上。還有一種情形會導緻外部壓力的發生，那就是作業系統在占用大量記憶體的操作（比如備份），解決方案就是将這些操作方到SQL Server運作壓力小的時候（比如淩晨1、2點的時候）。

2）内部壓力

a、找出讀取資料頁面最多的語句，對它進行調優。找出這些語句可以通過sys.dm_exec_query_status動态視圖和sys.dm_exec_sql_text動态函數的關聯查詢。

-- 實體讀取頁面最多的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

找出這些語句然後經可以用語句調優的方式來進行調優了。

b、如果你認為語句已經沒有調優的空間了，或者像快速的提高伺服器性能就隻能增加實體記憶體了。

二、Buffer Pool中的Stolen Memory的壓力調優

1、通過Memory Clerk的分析

由于Buffer Pool裡的Stolen記憶體都是SQL Server自己申請的，是以在Memory Clerk的動态管理視圖裡可以檢視。通過分析各Clerk的大小，基本就能判斷Stolen記憶體壓力的來源。常見的使用Stolen的記憶體較多的Memory Clerk。

a）CACHESTORE_SQLCP：緩存動态TSQL語句的執行計劃的地方。這通常和程式員的代碼有關，如果程式員習慣使用動态TSQL語句，這部分的記憶體中緩存的執行計劃就會非常大。解決方法就是使用存儲過程或者參數話的TSQL。

b)OBJECTSTORE_LOCK_MANAGER：SQL Server裡鎖結構使用的記憶體。如果SQL Server中的阻塞嚴重的話，這部分記憶體的記憶體使用量會很大。解決方案就是解決阻塞問題了。

2、通過sys.sysprocesses裡面的waittype字段進行分析

1）CMEMTHREAD（0X00B9）

當多個使用者向同一緩存區中申請記憶體或者釋放記憶體，在某一時刻隻會有一個連接配接的操作可以成功，其他的連接配接必須等待。這種情況比較少，主要是發生在哪些并發度非常高的系統中，而且通常都是在編譯動态的TSQL語句。解決方案就是使用存儲過程或者參數化的TSQL語句，提高執行計劃的重用。

2）RESOURCE_SEMAPHORE_QUERY_COMPLIE（0X011A）

當使用者傳送過的語句或者調用的存儲過程過分複雜，SQL Server編譯它所需要的記憶體會非常大。SQL Server為了防止過多的記憶體被用來做編譯動作，是以設定了編譯記憶體的上限。當有太多複雜的語句同時在編譯，編譯所需要的記憶體可能達到這個上限，這将有部分語句将處于等待記憶體進行編譯的狀态，也就該waittype。

解決方法有：盡量多的使用存儲過程或參數化的TSQL語句，簡化每次需編譯的語句複雜度，分成幾個存儲過程，實在不行的話可以考慮定期運作DBCC FREEPROCCACHE語句來手工清除緩存中的執行計劃，保證stolen中記憶體量。

三、Multi-Page Memory壓力調優

由于32位的SQL Server會在啟動的時候配置設定好Multi-Page的大小而且比較小，預設是384MB，是以對于32位的SQL Server比較容易發生Multi-Page Memory的壓力。該部分的壓力主要可能由下面三種情形導緻。

1、程式連接配接資料庫時的Network Packet Size大小，如果設定成8KB或者更高的時候，而且連接配接又非常大時。對于32位的SQL Server該部分的記憶體使用量會很快達到上限。解決方法就是将程式中設定的Network Packet Size改成預設的4KB，或者更新到64位SQL Server，這樣Multi-Page的大小就沒有限制了。

2、程式員使用了很多複雜的TSQL語句或者存儲過程，它的執行計劃超過了8KB，這将占用Multi-Page的空間。由于32位的SQL Server中該部分的大小比較小，它将很快被填滿，而由于Buffer Pool很大沒有壓力，它将不會觸發Lazy Writer，Mullti-Page中的執行計劃将不會被清理。而這時如果使用者需要申請Multi-Page Memory就必須等待。這會展現在sys.sysprocessed的waittype字段上，該值等于SOS_RESERVEDMEMBLOCKLIST。解決方案：語句進行調整，将它的執行計劃控制在8KB以内，如果不行的話可以考慮定期運作DBCC FREEPROCCACHE語句來手工清理執行計劃，或者更新到64位SQL Server。