加入RUN!PC粉絲團
最近新增的精選文章
 
最多人點閱的精選文章
 
 
精選文章 - 商務應用
分享到Plurk
分享到FaceBook
 
以動態管理物件觀察SQL Server(1)
文‧圖/胡百敬 2011/6/21 下午 03:47:51

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

動態管理物件是 SQL Server 2005 版本後,提供一組呈現 SQL Server 當下各種狀況的動態管理檢視(dynamic management view)和動態管理函數(dynamic management function),以查詢伺服器執行個體當下的執行狀況,監視其健康與否、診斷問題。例如連線的狀況、CPU、記憶體、硬碟 I/O、檔案大小…等硬體資源的使用、鎖定(lock)與等待的情形等。它們取代了原先 SQL Server 2000 部份系統資料表和函數的功能,以提供更豐富、深入的資訊。

透過 SQL Server Management Studio 的「物件總管」,可以在各資料庫內「檢視」→「系統檢視(system view)」的目錄下看到大量的檢視,而內容可以大分兩種,提供靜態的系統中繼資料,以及動態管理檢視,以呈現當下的運行狀況。在名稱上,它們全都歸類在 sys 結構描述(schema)之下,而動態管理檢視和函數的名稱以 dm_ 前置詞開始。動態管理檢視在系統資料庫和使用者自訂資料庫都可以看到,因為一些用來呈現伺服器等級的資料,另一些則是呈現資料庫的狀況。

除了透過動態管理檢視查詢系統資料外,另有動態管理函數,其數量較檢視為少,集中呈現在 Master 系統資料庫的「可程式性」→「函數」→「系統函數」→「資料表值函數」之下。

使用權限

當使用動態管理物件時,必須搭配 sys 結構描述(schema)。動態管理物件的存取範圍可分為兩種,其所需的權限如下:
●伺服器範圍:使用者需具有伺服器的 VIEW SERVER STATE 權限,才能看到整個伺服器的資訊,否則只能看到與他自己有關的資訊,大多數的動態管理物件都需要有此權限。
●資料庫範圍:使用者需資料庫的 VIEW DATABASE STATE 權限。少數動態管理物件,如 sys.dm_db_partition_stats,需要此權限才能看該資料庫內的資訊。

用來授權的 T-SQL 語法如下:


一般登入並未賦予或拒絕 VIEW SERVER STATE 權限,若要查詢關於該權限的賦予狀況,可以執行如下的語法:


一般使用者在個別資料庫也並未賦予或拒絕 VIEW DATABASE STATE 權限,若要查詢關於該權限的賦予狀況,可以執行如下的語法:


若要查詢動態管理檢視或函數,除了上述權限外,還需要擁有對該物件的 SELECT 權限,以限制使用者存取動態管理檢視和函數。亦即對該使用者不准存取的動態管理檢視或函數;直接設定拒絕 SELECT 權限。

預設這些動態管理物件都有賦予 Public 角色 SELECT 權限,而所有的登入帳號(Login)又可以對應到 Master 資料庫的 Guest 使用者,Guest 使用者再屬於 Public 角色,因此一般的登入帳號應該都可以執行對系統動態管理物件的查詢。

另外,在 DDL 的 DENY、GRANT 和 REVOKE 中,以 DENY 的優先順序最高,因此,如果已授與使用者 VIEW SERVER STATE 權限,但拒絕 VIEW DATABASE STATE 權限,則使用者可以查看伺服器層級資訊,但不能查看資料庫層級資訊。

動態管理物件的分類

整體的動態管理檢視和函數的量不少,可大分成以下類型:
▲表1:SQL Server 所提供的各種動態管理檢視和函數類別

表1中的字首代表該類動態管理物件名稱是在 sys.dm_<字首>_ 命名規則中,角括號的位置。你可以在 master系統資料庫執行下述語法來查詢這些動態管理物件:


由於動態管理物件是存放在系統資源資料庫(mssqlsystemresource),而後映射到各個資料庫內,它能呈現整體系統的狀況,但大多不是針對特定的系統資料庫,例如 msdb 系統資料庫,所以若要查詢該資料庫所存放的特定資料,例如 msdb 所存放的各資料庫備份資訊、Agent作業狀況…等,依然要查詢msdb 系統資料庫內所提供的資料表或檢視等。

我們接下來就以列舉的方式,介紹幾個動態管理檢視和函數的使用方式。從表1可以看得出來,動態管理物件種類繁多,我們劃分為檢視當下狀況和從 SQL Server 執行個體啟動累計至今的狀況兩方面來討論。

SQL Server執行個體當下狀況

在此討論如何觀察伺服器執行個體的狀況,當下有哪些前端程式連接上來,那些語法耗用大量資源,當下伺服器的記憶體、CPU、I/O、索引等使用情形,乃至於是否有大量的鎖定、被鎖定等。

前端應用程式的連接資訊屬於伺服器執行個體等級,代表存取 SQL Server 的前端程式來源,通常用在與其他 DMO 的整合,以提供資源使用與前端程式的關係。

sys.dm_exec_sessions動態管理檢視

sys.dm_exec_sessions 動態管理檢視提供豐富的資料,由於橫向欄位過多,在此將結果轉成直行的資料表呈現,範例1如下:


執行結果如圖1所示。sys.dm_exec_sessions 動態管理檢視提供傳回的每一筆紀錄描述一個連結的工作階段,該階段的各種設定、登入帳號,以及累積使用的 CPU、RAM、I/O 等資源,但該連接當下正在執行的工作所耗之資源不會累加進去。雖然它的統計資料是呈現連接對 SQL Server 累計的使用狀況,但當我們要檢視當下誰在耗用大量資源、鎖定的源頭時,往往需要連結這個檢視來得知前端程式的資料。


▲圖1:sys.dm_exec_sessions 動態管理檢視所提供 Session 的各項資訊

如果使用者具有伺服器的VIEW SERVER STATE權限,將可以看到SQL Server執行個體上所有執行中的工作階段,否則只會看到自己目前的工作階段。換句話說,若你是用一般的使用者身份查詢這個檢視,將只傳回自己的連線設定,若以管理者身分執行,才需要透過 @@spid 限制回傳的工作階段,否則會傳回 SQL Server 執行個體當下全部工作階段的資訊。你也可以透過is_user_process欄位設定過濾條件is_user_process=1,限制只傳回使用者所建立的工作階段,排除 SQL Server 自己使用的工作階段。

sys.dm_exec_connections 動態管理檢視

另一個與上述查詢 Session 資訊近似的是 sys.dm_exec_connections 動態檢視,但該管理檢視偏向連接資料庫的方式,如登入認證的方式,網路組態設定等,它會傳回使用者連線的認證方式與網路 IP,這是一般管理人員常需要的資訊。

sys.dm_exec_requests 動態管理檢視/ sys.dm_exec_sql_text 動態管理函數

若要查詢每一工作階段正在執行的工作,可以透過sys.dm_exec_requests 動態管理檢視,它有部份內容與上述的 sys.dm_exec_sessions 動態管理檢視相似,但前半段提供當下所執行工作的描述。若是在 SQL Server 執行有問題的當下;要找最耗資源的語法,則需靠sys.dm_exec_requests 動態管理檢視。 在此透過 SQL Server Management Studio 的一條連接簡單地浪費 SQL Server 的 CPU 資源,其執行語法如下:


而後在 Management Studio 用另一條連接執行以下的查詢語法:


這時傳回多筆記錄代表 SQL Server 正在執行的作業。透過 cpu_time、reads、writes 等欄位可以分析資源使用狀況。若是一些耗時的工作,也可查詢 percent_complete 欄位所呈現的進度。在 SQL Server 線上說明有解釋那些語法才有執行進度。例如:備份資料庫,你可以在 Backup 語法搭配 with STATS=<百分比> 選項,即可透過 sys.dm_exec_requests 動態管理檢視追蹤執行進度。

sys.dm_exec_requests 動態管理檢視回傳的 sql_handle 欄位代表某個批次內,某段 SQL 文字的雜湊值,可藉由 sys.dm_exec_sql_text 動態管理函數傳回某對應之 SQL 陳述式的文字。針對資料庫物件(如預存程序、觸發程序或函數),SQL 控制代碼(sql_handle)是從資料庫識別碼、物件識別碼和物件編號衍生而來。在使用sys.dm_exec_sql_text 動態管理函數時,一般以 CROSS APPLY 語法搭配其他動態檢視,讓回傳的語法對應到每一筆代表不同執行需求之記錄。範例如下:


上述語法的執行結果,就是自己一模一樣的語法。但當執行一段批次時,當下正在執行的語法可能是批次中的局部語法,範例如下:


當執行範例中的預存程序sp時,絕大部分的時間將停留在 waitfor 語句。若要擷取正在執行的語句,可以透過sys.dm_exec_requests 動態管理檢視的 statement_start_offset 和 statement_end_offset 兩個欄位,statement_start_offset 代表著起始位置,而 statement_end_offset 代表結束位置。但若 statement_end_offset 的值為 -1,則代表正在執行語句的結束位置等於整個批次文字的結束位置。 當執行上述範例預存程序 sp 後,透過範例程式碼2查詢,得到如圖2的結果:



▲圖2:擷取 SQL Server 正在執行各個連接的單句 T-SQL

從圖2的「整個批次的語法」欄位可以看到預存程序的完整定義,但從「正在執行語法」欄位,則可以看到當下 SQL Server 正在處理批次中的某句語法。

而從以上三個檢視,我們大概可以獲得各個連接的設定與使用 SQL Server 概況了。接下來檢視 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 動態管理檢視。它會告訴你該機器的實體與虛擬記憶體的大小、當下可用的量有多少,記憶體是否足夠等資訊。

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

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



▲圖3:查詢SQL Server執行個體各種物件使用記憶體的狀況

圖3僅擷取了局部的執行結果。從圖3可以看出筆者的SQL Server執行個體主要將記憶體耗用在以下的類型:
●SQL Buffer Pool:資料的暫存區域。
●Cachestore_Sqlcp:前端應用程式組織 T-SQL 語法要 SQL Server 執行,SQL Server 編譯成執行計畫並放在快取區所用的記憶體。
●Cachestore_OBJCP:伺服器物件,如預存程序,所耗的執行計畫快取。

其實,就筆者經驗,這三項往往也是在各位讀者機器上大量耗用SQL Server記憶體的主要部分。

首先來看資料暫存區(SQL Buffer Pool)的使用狀況。當 SQL Server 從硬碟讀取資料進記憶體後,就放到資料暫存區。就算利用語法清空資料暫存區:「DBCC DROPCLEANBUFFERS」,SQL Server也不會將記憶體還給作業系統,避免需要記憶體時又要請作業系統配置記憶體,久而久之就造成記憶體零碎。

在此先清空資料暫存區後,利用前述的查詢語法可以看出所使用的MemoryClerk_SqlBufferPool依然佔據了相同的大小。若想要分析資料暫存區放置了哪些資料,可以先用如下的語法查詢,在筆者電腦的執行結果如圖4所示:



▲圖4:分析各資料庫佔據多少資料暫存區

接著執行語法故意放置一些資料到資料暫存區:「SELECT * FROM AdventureWorks2008.Person.Person」。重新查詢上述語法可以發現,AdventureWorks2008資料庫吃掉了30 Mega Bytes左右的資料暫存區。而後切換到AdventureWorks2008執行以下的語法,透過sys.dm_os_buffer_descriptors動態管理檢視分析是那些物件用了記憶體:


由於暫存區數量是以資料頁(page)為單位(大小為8 k bytes),因此乘以8再除以 1024,也就是除以 128 即為 Mega Bytes。其執行結果如圖 5 所示。

▲圖 5:分析資料庫內物件耗用資料暫存區的空間大小

從圖 5 可以看到主要耗用資料暫存區的就是先前查詢的 Person 資料表,占掉了約 30 Mega bytes 的空間,其 index_id 欄位為 1,表示快取的是叢集索引,也就是資料表本身。

分析完了資料暫存區,本文針對動態管理物件就介紹到此,下篇文章中,筆者再繼續介紹圖 3 中耗用記憶體資源分列 2、3 名的 Cachestore_Sqlcp 和 Cachestore_OBJCP,其代表不同類型的執行計畫快取,以及如何觀察交易管理與 CPU 使用狀況。