程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server性能調優:資源管理之內存管理篇(上)

SQL Server性能調優:資源管理之內存管理篇(上)

編輯:關於SqlServer

對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三部分的具體瓶頸來講解。
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved