第七章——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、? 打開新查詢窗口，并輸入以下代碼，用于監控當前查詢：
?
SELECT DB_NAME(R.database_id) AS DatabaseName ,S.original_login_name AS LoginName ,S.host_name AS ClientMachine ,S.program_name AS ApplicationName ,R.start_time AS RequestStartTime ,ST.text AS SQLQuery ,QP.query_plan AS ExecutionPlan ,R.cpu_time AS CPUTime ,R.total_elapsed_time AS TotalTimeElapsed ,R.open_transaction_count AS TotalTransactionOpened ,R.reads ,R.logical_reads ,R.writes AS TotalWrites FROM sys.dm_exec_requests AS RINNER JOIN sys.dm_exec_sessions AS S ON R.session_id = S.session_idCROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS STCROSS APPLY sys.dm_exec_query_plan(R.plan_handle) AS QP ORDER BY TotalTimeElapsed DESC GO
?
?
3、? 打開新窗口輸入一下腳本，用于監控當前打開的游標：
?
SELECT S.host_name AS ClientMachine ,S.program_name AS ApplicationName ,S.original_login_name AS LoginName ,C.name AS CursorName ,C.properties AS CursorOptions ,C.creation_time AS CursorCreatinTime ,ST.text AS SQLQuery ,C.is_open AS IsCursorOpen ,C.worker_time / 1000 AS DurationInMiliSeconds ,C.reads AS NumberOfReads ,C.writes AS NumberOfWrites FROM sys.dm_exec_cursors (0) AS CINNER JOIN sys.dm_exec_sessions AS S ON C.session_id = S.session_idCROSS APPLY sys.dm_exec_sql_text(C.sql_handle) AS ST ORDER BY DurationInMiliSeconds DESC GO
?
?
分析：
在上面步驟中，使用了以下的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提供查詢和對象的詳細緩存信息，對查詢優化很有幫助。
posted on 2015-03-06 14:56 NET未來之路 閱讀(...) 評論(...) 編輯 收藏轉載于:https://www.cnblogs.com/lonelyxmas/p/4318256.html
總結
以上是生活随笔為你收集整理的第七章——DMVs和DMFs（1）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 设置div滚动条
- 下一篇： C++标准：C++不允许修改任何基本型别