SQL Server DMVs

扫盲： 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