第七章——DMVs和DMFs（1）簡介：監控目前查詢執行的統計資訊：

        從SQLServer2005開始，微軟引入了一個名叫DMO（動态管理對象）的新特性，DMO可以分為DMFs（Dynamic

Manage Functions，動态管理函數）和DMVs（Dynamic Manage Views，動态管理視圖）兩部分。這些函數和視圖用于查找SQLServer執行個體内部統計資訊以供性能監控所用。它們提供實時的，關于SQLServer内部工作的，能用于性能分析和性能故障排除的各種統計資訊。

        所有的DMO都屬于sys架構，并且以dm_開頭。執行DMO需要有VIEW

SERVER STATE和VIEW DATABASE STATE權限。

下面簡述一下本系列将要介紹的DMO：

Ø  執行相關的DMO（sys.dm_exec_*）：提供與執行相關的統計資訊。可以用于監控與緩存查詢、執行計劃、活動連接配接/會話和帶有執行計劃的目前運作的查詢的相關統計資訊。

Ø  索引相關的DMO（sys.dm_db_index_*和sys.dm_db_missing_*）：提供關于索引的統計資訊。這些DMO可以用于監控和分析因為丢失索引、無效索引而導緻的性能問題，也可以用來檢查索引的使用情況。

Ø  資料庫相關DMO（sys.dm_db_*）：提供資料庫相關統計資訊。可以用于監控和分析資料庫的性能問題，分析資料庫相關檔案的統計資訊、會話統計資訊和任務統計資訊。

Ø  I/O相關DMO（sys.dm_io_*）：提供I/O操作的統計資訊，用于監控和分析SQLServer的I/O性能問題。

Ø  OS相關DMO（sys.dm_os_*）：提供關于sqlos内部統計資訊，用于監控和分析伺服器配置問題。

Ø  事務相關的DMO（sys.dm_trn_*）：提供事務相關的統計資訊，用于監控和分析長時間運作的事務的鎖定、死鎖問題。

這些DMO的資料可以通過DBCC SQLPERF(‘SYS.DM_OS_WAIT_STATS’,CLEAR)或者重新開機伺服器來重置。

        為了使得伺服器上的查詢足夠的好，需要識别那些消耗資源的查詢和找到這些資源的來源。為了實作這些功能，需要監控查詢的請求和檢查它們的運作時間、IO操作等等。

        SQLServer有專用的DMO來監控查詢的執行資訊，這些DMO包含廣泛的資訊，以sys.dm_exec_開頭。通過這些DMO可以快速發現問題查詢，進而進行優化。

        本文将示範使用DMO來擷取目前正在請求SQLServer查詢的資訊，并找到長時間運作的查詢，同時可以監控目前正在運作的遊标，這個通常也會引起性能問題。

        本文中将使用DMO來監控目前查詢請求的一些有用資訊，如資料庫名、登入名、程式名、查詢開始時間、讀寫數。

        衆所周知，遊标是非常消耗資源且影響查詢性能的，如非必要，不建議使用。

        本例中使用SQLServer 2008R2，并在微軟示例資料庫AdventureWorks上操作。

1、 

打開SSMS，連到SQLServer執行個體。

2、 

打開新查詢視窗，并輸入以下代碼，用于監控目前查詢：

3、 

打開新視窗輸入一下腳本，用于監控目前打開的遊标：

在上面步驟中，使用了以下的DMOs：

Ø 

Sys.dm_exec_requests

Sys.dm_exec_sessions

Sys.dm_exec_sql_text

Sys.dm_exec_query_plan

對于上面的查詢結果，需要思考的問題：

Ø  哪個庫正在接受請求？

Ø  那個登入名執行了這個請求？

Ø  請求是從哪個計算機發出的？

Ø  請求是從那個應用程式發出的？

Ø  請求是何時到達SQLServer的？

Ø  請求中需要執行什麼SQL語句？

Ø  執行的SQL語句的執行計劃是什麼？

Ø  請求的持續時間有多少？

Ø  請求是否開啟了事務？

Ø  請求造成的讀寫數是多少？

Ø  請求是否被阻塞了？如果是，是哪個會話造成的？

        為了找到這些資訊，需要把sys.dm_exec_requests和sys.dm_exec_sessions的session_id列關聯。

同時，使用CROSS APPLY來關聯sys.dm_exec_sql_text()函數來查找請求的SQL文本。關聯sys.dm_exec_query_plan()函數來查找請求的執行計劃。這兩個函數需要從查詢中分别獲得sql_handle和plan_handle。在結果集中，按TotalTimeElapsed列排序，可以知道最耗資源的查詢。

        第二個查詢中使用了sys.dm_exec_cursors()函數來傳回目前正在使用的遊标的詳細。這個函數接受session_id作為參數。如果傳入了特定session_id，隻會傳回該會話的遊标，如果傳入0，則傳回所有會話的遊标。結果集按照DurationInMiliSecondes排序，一邊查找最耗資源的遊标，注意worker_time除以了1000，因為這個的機關是微妙，除以1000可以得到毫秒。

由于這些dmo的解釋較長，詳細請看聯機叢書。除了上面列出的dmo之外，還有一些與執行相關的dmo，如：

Sys.dm_exec_cached_plans(DMV)

Sys.dm_exec_procedure_stats(DMV)

Sys.dm_exec_query_stats(DMV)

Sys.dm_exec_cached_plan_dependent_objects(DMF)

這些DMO提供查詢和對象的詳細緩存資訊，對查詢優化很有幫助。