SQL Server DMVs

Rainie999

　　扫盲： DMV = Dynamic Management Views，动态管理视图
　　
　　Microsoft introduced a few more dynamic management views to also assist with identifying possible index candidates based on query history.
　　
　　The dynamic management views are:
　　

• 
  sys.dm_db_missing_index_details - Returns detailed information about a missing index
  
• 
  sys.dm_db_missing_index_group_stats  - Returns summary information about missing index groups
  
• 
  sys.dm_db_missing_index_groups - Returns information about a specific group of missing indexes
  
• 
  sys.dm_db_missing_index_columns(index_handle) - Returns information about the database table columns that are missing for an index. This is a function and requires the index_handle to be passed.
  

DMV可以解决哪些问题
　　

故障诊断
　　
　　诊断就是要识别出问题的所在。有很多的方式和工具可以帮助我们达到这个目的，但是，有了DMV，可能效率会更快：没有什么比分析SQL Server内部的元数据来的更快。
　　
　　很多时候，对问题的诊断也是性能调优的第一步，搞清楚了问题，才好对症下药。
　　
　　利用DMV可以诊断出以下问题：最慢的查询语句，常见的等待与阻塞，没有用的索引，大量的I/O操作，利用率最低的执行计划。
　　
　　正如之前所说，我们可以在不同的级别上面分析问题，例如从整个服务器级别，数据库级别，甚至是某个查询。我们可以通过在获取DMV信息时，设置获取 信息的条件来办到。例如，在上一小节的示例中，就是获取整个SQL Server中找出最慢的前20个查询，如果需要，我们完全可以将条件缩小到某个数据库。
　　
　　很多时候，在识别问题的时候，不是那么容易，仅仅通过一个DMV就搞定了的，需要和DMF结合。甚至要和其他的DMV一起结合分析（在后续文章中，我们会理解的更加深刻）。
　　
　　诊断出了问题，是一个方面，解决问题也尤为重要。
　　
　　

性能调优
　　
　　性能调优主要是利用相关的技巧技术之前诊断中出现的问题，从而提升性能。我们后续会详细讲述，这里就不再赘述了。
　　
　　

状态监控
　　
　　很多的DMV（特别是那些以 sys.dm_exec_开头的）都反映了数据库服务器执行的状态。通过查看这些DMV，我们可以清楚的知道数据库服务器的现在的状态和历史的状态（当 然，如何SQL Server服务被重启，那么之前的信息都丢失了，除非定期做了保存）。例如，数据库需要做批处理等长时间的操作，如果其中操作执行超时或运行的非常慢， 这个时候，我们就可以查询DMV来分析。如果采用Profiler或者相关的Profiler脚本跟踪，会对数据库服务器的压力相当大。再如，还可以分析 数据库中现在有哪些查询在运行，有多少请求在处理，打开多少连接等等，主要是对数据库的操作，都可以通过DMV查询到。
　　
　　
　　使用DMV，查询Missing Index的方案

　　
　　

    SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
　　
　　
　　Reference:
　　http://www.mssqltips.com/sqlservertip/1634/using-sql-server-dmvs-to-identify-missing-indexes/
　　http://www.infoq.com/cn/articles/wy-sqlserver-performance-optimization/
　　http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
　　http://www.devguru.com/technologies/t-sql/7108