第七章——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 TotalWritesFROM sys.dm_exec_requests AS R INNER JOIN sys.dm_exec_sessions AS S ON R.session_id = S.session_id CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST CROSS APPLY sys.dm_exec_query_plan(R.plan_handle) AS QPORDER 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 NumberOfWritesFROM sys.dm_exec_cursors (0) AS C INNER JOIN sys.dm_exec_sessions AS S ON C.session_id = S.session_id CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) AS STORDER 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提供查询和对象的详细缓存信息，对查询优化很有帮助。
