資料庫管理師常常面臨不知 SQL Server 如何使用資源的困擾，尤其是程式開發人員誤用 T-SQL 語法，索引設計不佳、大量來回存取…等，造成多人同時使用時耗盡系統資源，或是互相鎖定等狀況。但要資料庫管理師抓出元兇，或是分析使用趨勢，利用既有工具程式如 SQL Server Management Studio 所內建的報表、活動監視器、SQL Trace/Profiler、Windows 效能監視器…等，仍力有未逮。還要再進一步深入分析 SQL Server，則需使用動態管理物件(Dynamic Management Object, DMOs)。

與 OS 相關

記憶體相關

sys.dm_os_sys_memory ：從作業系統傳回記憶體資訊。
sys.dm_os_memory_clerks ：傳回 SQL Server 執行個體目前記憶體的使用狀況。

SQL Server 大量利用記憶體來存放暫存資料、中繼資料、執行計畫快取…等，當它逐步累積各種結構時，會越吃越大直到我們所設定的「最大伺服器記憶體」量。以下的效能計數器可以用來分析記憶體是否夠用：

SQL Server: Buffer Manager\Buffer cache hit ratio
越大越好，代表 SQL Server 使用的資料大多從記憶體取出，一般建議 > 95%。
SQL Server: Buffer Manager\Page Life Expectancy
越大越好，代表記憶體夠，不須把暫存資料清掉，一般平均值要 > 300 秒，代表快取的資料頁在記憶體中保有 5 分鐘以上。
SQL Server: Memory Manager\Memory Grants Pending
越小越好，代表需要記憶體時不必等待，其值最好為 0。

除了透過以上的效能計數器來分析記憶體使用狀況，一般，DBA 還會希望知道 SQL Server 把記憶體用到哪去了？

一開始若想要綜觀伺服器硬體的記憶體資源，可以查詢 sys.dm_os_sys_memory 動態管理檢視。它會告訴你該機器的實體與虛擬記憶體的大小、當下可用的量有多少，記憶體是否足夠等資訊。

select 
     total_physical_memory_kb/1024 as [總記憶體(MB)]
    ,available_physical_memory_kb/1024 as [可用記憶體(MB)]
    ,total_page_file_kb/1024 as [總分頁檔(MB)]
    ,available_page_file_kb /1024 as [可用分頁檔(MB)]
    ,system_cache_kb/1024 as [系統快取記憶體(MB)]
    ,system_high_memory_signal_state
    ,system_low_memory_signal_state
    ,system_memory_state_desc 
from sys.dm_os_sys_memory

接下來，可以透過 sys.dm_os_memory_clerks 動態管理檢視觀察握在 SQL Server 執行個體手上的記憶體用在何處。當上層的記憶體結構物件，如資料暫存區(Data Buffer)、執行計畫快取(Procedure Cache)、鎖定管理…等，需要記憶體時，要憑藉「記憶體 Clerk」存取記憶體節點以配置記憶體。

前述需大量記憶體的每個物件須建立自己的「記憶體 Clerk」，並透過 Clerk 介面來配置它所有的記憶體。由於一個物件可能建立多個「記憶體 Clerk」以配置不同的記憶體區塊，因此，需要依物件類型彙總記憶體的使用量，範例語法如下，其執行結果如下圖所示：

select type 
,sum(pages_kb)/1024						[記憶體佔用大小(MB)]
,sum(virtual_memory_reserved_kb)/1024   [保留虛擬記憶體的數量(MB)]
,sum(virtual_memory_committed_kb)/1024	[使用虛擬記憶體的數量(MB)]
from sys.dm_os_memory_clerks
group by type
order by [記憶體佔用大小(MB)] DESC

以上是目前這台 SQL Server 執行個體主要的記憶體耗用情形，排名前面的幾項也正是一般常見耗用 SQL Server 記憶體的主要部分

SQL Buffer Pool：資料的暫存區域。
Cachestore_Sqlcp：前端應用程式組織 T-SQL 語法要 SQL Server 執行，SQL Server 編譯成執行計畫並放在快取區所用的記憶體。
Cachestore_OBJCP：伺服器物件，如預存程序，所耗的執行計畫快取。

SQL Buffer Pool

查詢暫存區放置了哪些資料

當 SQL Server 從硬碟讀取資料進記憶體後，就放到資料暫存區。你可以利用以下語法，查詢暫存區放置了哪些資料：

select
ISNull(DB_NAME(database_id),N'資料資料庫') [資料庫],
Count(*)/128 as [耗用暫存記憶體(MB)]
from sys.dm_os_buffer_descriptors
group by database_id
order by 2 desc

此時就算利用語法清空資料暫存區：「DBCC DROPCLEANBUFFERS」，SQL Server也不會將記憶體還給作業系統，避免需要記憶體時又要請作業系統配置記憶體，久而久之就造成記憶體零碎。

接著執行語法故意放置一些資料到資料暫存區，然後再查詢一次暫存區放置了哪些資料

SELECT * FROM 
Sales.SalesOrderHeader H
INNER JOIN Sales.SalesOrderDetail D ON H.SalesOrderID=D.SalesOrderID

由以上，你可以看到，AdvancetureWorks2012 用掉了 17 MB 的暫存記憶體。若先切換到 AdvancetureWorks2012 資料庫，再執行以下語法：

USE [AdvancetureWorks2012]

SELECT
OBJECT_NAME(p.object_id)  [物件名稱],
p.index_id,
Count(*)/128 as [耗用暫存記憶體(MB)],
Count(*) as [暫存區數量]
FROM sys.dm_os_buffer_descriptors dobd
join sys.allocation_units au on au.allocation_unit_id=dobd.allocation_unit_id
join sys.partitions p on au.container_id=p.hobt_id
WHERE dobd.database_id=DB_ID() AND
	  ObjectProperty(P.object_id,'ISSystemTable')=0
group by p.object_id, P.index_id
order by 4 desc

PS.由於暫存區數量是以資料頁(page)為單位(大小為8 k bytes)，因此乘以8再除以 1024，也就是除以 128 即為 Mega Bytes。

Cachestore_Sqlcp

Cachestore_OBJCP

與 I/O 相關

顯示資料庫大小

要取得資料庫的空間大小和使用狀況，最簡單的方法就是透過 sp_spaceused 預存程序。

--顯示目前資料庫的空間大小和使用狀況
EXEC sp_spaceused 

--先更新即時資訊, 再顯示目前資料庫的空間大小和使用狀況
EXEC sp_spaceused @updateusage = N'TRUE';

不過上面結果中的 database_size 數值包含 mdf 和 ldf 的檔案大小。若要區分資料檔和交易記錄的使用狀況，可以分別使用 DBCC showfilestats 和 DBCC SQLPERF 指令取得相關資訊。

SET NOCOUNT ON
DECLARE @tDBSize TABLE
(DBName SYSNAME DEFAULT(DB_NAME()),
Fileid INT, FileGroup INT, TotalExtents INT,
UsedExtents INT, Name SYSNAME, FileName NVARCHAR(4000)) 

DECLARE @tLogSize TABLE
(DBName sysname, logsize float, used float, status int)

INSERT @tDBSize(Fileid,FileGroup,TotalExtents,UsedExtents,Name,FileName)
EXEC ('DBCC showfilestats')

INSERT INTO @tLogSize
EXECUTE ('DBCC SQLPERF(LOGSPACE)')

SELECT D.DBName N'資料庫',D.Name N'資料檔案',
TotalExtents*64.0/1024 N'資料檔案使用硬碟空間(MB)',
UsedExtents*64.0/1024 N'資料實際使用空間(MB)',
logsize N'交易記錄檔整體使用的硬碟空間(MB)',
(logsize*used/100) N'交易記錄檔整體的實際使用(MB)'
FROM @tLogSize L INNER JOIN @tDBSize D
ON L.DBName=D.DBName

另外使用 SSMS 的報表功能，也可以取得磁碟使用狀況。

關於 DBCC showfilestats 回傳內容的意義，可參考這篇介紹：Understanding SQL Server's DBCC SHOWCONTIG

顯示 Table 大小

--顯示 Purchasing.Vendor 的 筆數,大小
EXEC sp_spaceused N'Purchasing.Vendor';

----顯示所有 table 的 筆數,大小
EXEC sys.sp_MSforeachtable "EXEC sp_spaceused '?'"

與 process 相關

sys.dm_exec_sessions

sys.dm_exec_sessions 檢視表會回傳目前所有的工作階段(session)。

sys.dm_os_waiting_tasks

sys.dm_os_waiting_tasks 檢視表會回傳正在等候某項資源的工作。

sys.dm_os_wait_stats

sys.dm_os_wait_stats 檢視表會回傳等候的相關資訊。

其他校能調校常使用的 DMO

DMOs 物件依功能性大至可分成以下幾類：

SQL Server Operating System (SQLOS)–related DMOs
Execution-related DMOs
Index-related DMOs

VITO の學習筆記

2013年6月24日星期一

效能調校(7)-使用 DMV 查詢系統消耗資源

與 OS 相關

記憶體相關

SQL Buffer Pool

查詢暫存區放置了哪些資料

Cachestore_Sqlcp

Cachestore_OBJCP

與 I/O 相關

顯示資料庫大小

顯示 Table 大小

與 process 相關

sys.dm_exec_sessions

sys.dm_os_waiting_tasks

sys.dm_os_wait_stats

其他校能調校常使用的 DMO

沒有留言:

張貼留言

2013年6月24日 星期一

效能調校(7)-使用 DMV 查詢系統消耗資源

與 OS 相關

記憶體相關

SQL Buffer Pool

查詢暫存區放置了哪些資料

Cachestore_Sqlcp

Cachestore_OBJCP

與 I/O 相關

顯示資料庫大小

顯示 Table 大小

與 process 相關

sys.dm_exec_sessions

sys.dm_os_waiting_tasks

sys.dm_os_wait_stats

其他校能調校常使用的 DMO

沒有留言:

張貼留言

2013年6月24日星期一