CIO
|
PCDIY!
|
旗標圖書
|
旗景數位影像
|
讀者服務
首 頁
即時新聞
業界動態
最新活動
企業採購
精選文章
線上教學
品牌活動
程式碼下載
雲端運算智庫
最近新增的
精選文章
AP內建AI引擎 Mist Cloud平台分析能力強 Juniper Mist AI領先全球 改善WiFi穩定、效能首選
解決IT供應鏈攻擊
內部威脅的七個警訊
遠百以專案辦公室推動數位體驗
多廠牌與多重電信業者網路架構的挑戰與機會
德明科大啟用電貿暨AI實習基地 ViewSonic ViewBoard 智慧互動電子白板 扮要角
淺談計算誤差
秀傳醫療體系統 以Lenovo HyperConverged HX 超融合架構扎穩智慧醫療發展基礎
模組化設計 偵測率達99.99% 全面防杜惡意郵件入侵 首選Cellopoint Email UTM
滿足網路管理與檔案安全傳輸需求,Ipswitch的MOVEit及WhatsUp Gold一次完整提供
來自學界的資料分析利器 - Weka 與 R
北醫建置肺癌資料庫,透過深度標註訓練AI,協助醫師早期發現癌症
北榮AI門診上路!人工智慧判讀腦瘤,有效縮短醫師確診時間
台灣智慧機器人玩具聯盟攜手英閱音躍研創 推廣T. Robot程式教育,協助國中小學扎根培養運算思維
一場與時間賽跑的戰役 ,人工智慧加速心血管疾病診斷
最多人點閱的
精選文章
免費IT建置--Linux系統操作與管理
免費IT建置--網頁伺服器的完美組合LAMP(下)
初探Hadoop開放原始碼平台環境
Linux下的防火牆(基礎篇)
免費IT建置--檔案共享與檔案伺服器
Linux下的防火牆(進階篇)
N.Y.BAGELS CAFE善用SAP Business One
手機程式設計入門與應用 Android、iPhone、Windows Mobile─ 影片播放器範例
手機程式設計入門與應用 Android、iPhone、Windows Mobile─ 線上查詢匯率
手機程式設計入門與應用 Android、iPhone、Windows Mobile─ 擲骰子遊戲
手機程式設計入門與應用 Android、iPhone、Windows Mobile─ 音樂播放器範例
手機程式設計入門與應用 Android、iPhone、Windows Mobile─ 來電黑名單
免費IT建置--網頁伺服器的完美組合LAMP(上)
手機程式設計入門與應用 Android、iPhone、Windows Mobile─ 繪圖板範例
手機程式設計入門與應用 Android、iPhone、Windows Mobile─ Matrix應用範例
精選文章 - 商務應用
分享到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 使用狀況。
回首頁...
關於RUN!PC
|
廣告刊登
|
聯絡我們
|
讀者服務
|
雜誌訂閱
|
出刊&補寄時間
-- Copyright© FLAG INFORMATION CO., LTD. 旗訊科技(股)公司. All rights reserved. 本站圖文著作權所有 未經授權 不得任意轉載使用 --
-- 請使用1024*768螢幕解析度,IE 7.0或firefox 3.0以上瀏覽器,以達到最佳閱讀效果--