SQL Server性能优化：初识元数据优化(2)

　　DMV是以SQL Server实例为级别进行保存的。也就说，如果在服务器上面，安装了一个SQL Server，那么此时这个SQL Server就是一个实例，那么这个实例里面的所有的数据库的DMV都是保存在相同的内存中。当然，我们在使用的时候，可以根据需要只提取更低级别的DMV，例如提取某个数据库的DMV，某个表的DMV，甚至是某个查询的DMV。

　　因为DMV信息是保存在内存中的，我们不需要额外的操作，只需要将这些信息取出来，按照我们的要求进行运算，统计，分析就够了，获取信息的数据非常快，并且不会对服务器产生压力。另外，因为DMV是SQL Server本身保存的，并且已经做了统计的信息，所以，数据更加的接近于数据库本身的状态。

　　SQL Server运行的时候越长，DMV中保存的信息就越多(当然，DMV非常小，不会对内存造成压力)，利用DMV分析就越准确。这一点和之前的Profiler和DTA是完全不一样的。唯一的一个问题就是：每次SQL Server服务重启，这些保存在内存中的DMV信息就没有了，又是从头开始，慢慢的保存。当然，对于这个问题，我们有很多的解决方案，例如，我们可以定期的将DMV的信息导出，保存在磁盘上。

　　DMV包含了的信息有很多：索引相关的，查询执行相关的，还有SQL Server OS相关的，Common Language Runtime(CLR)相关的，事务相关，安全相关的，资源管理相关的，数据备份相关的，I/O相关，全文查找相关，数据库镜像相关的，等等信息。所以，我们完全可以使用已经保存在DMV中的信息来进行我们的分析。

　　因为SQL Server内部的DMV很多，我们本次系类的文章注重在性能分析与调优上，所以，我们主要关注以下几类DMV：索引相关，执行相关，SQL Server OS相关，CLR相关，事务相关，I/O相关，数据库相关。

　　下面，我们分析一个查询的运行，看看在这个过程中SQL Server都记录了哪些信息(或者说，DMV中保存了什么信息)：

　　查询的执行计划(即描述了一个查询是如何被执行的)

　　什么索引被使用

　　什么索引本来应该被用到，但是又没有使用。(因为此时存在缺失索引的性能问题)

　　I/O的状态(包含逻辑I/O操作和物理的I/O操作)

　　查询执行消耗的时间

　　查询等待其他资源消耗的时间

　　查询在等待什么资源

　　通过分析这些信息，不仅仅可以使得我们更好的理解查询的是如何工作的，并且还可以让我们思考如何更加合理，高效的使用资源，提高性能。

　　一般而言，在使用DMV的时候，我们很多时候也需要将其与DMF(Dynamic Management Functions)一起使用。我们可以简单的将DMF理解为SQL Server内部的一系列函数。例如，通过分析sys.dm_exec_query_stats，可以知道查询的相关信息，如果将sys.dm_exec_query_stats里面的sql_handle传给sys.dm_exec_sql_text，那么，我们就可以知道查询的语句的内容。

　　DMV简单示例

　　为了使得大家对DMV有更加深入的了解，我们首先来看看一个使用DMV来找出那些查询运行的最慢。(朋友们可能对这里提到的相关的DMV和DMF不太熟悉，没关系，后续文章会介绍)

　　在SQL Server的查询分析器中，运行一下SQL 语句：

　　此时，运行的结果如下：

　　在这个查询中，我们主要是通过将sys.dm_exec_query_stats这个DMV与sys.dm_exec_sql_text和sys.dm_exec_query_plan这两个DMF结合，通过分析查询所消耗的时间，然后按照从高到低进行排序，选出前20个进行展示。

　　从这个示例中，我们可以知道几点：

　　查询DMV时，应该尽可能的将对数据库的影响降到最小。所以，我们在查询的最上面，加上了：SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED。因为每次在运行查询的时候，或多或少的会对数据库产生不同程度的锁定，并且锁定的级别各不一样。通过上面的设置，就告诉SQL Server，接下来的执行的查询将锁定的级别定为：Read Uncommitted。从而将影响减小到最小。

　　每次进行性能问题诊断的时候，首先要解决最严重的性能问题。所以，我们此处只是找出前20个运行最慢的查询语句。

　　虽然原生的DMV信息提供了很多的信息，但是很多时候需要对DMV进行复杂的统计分析。(这个成本比分析SQL Server Profiler收集到的数据小，也更加简单，准确。)

　　DMV可以解决哪些问题

　　看完了上面的简单的例子之后，相信朋友们对DMV有了一个感性的认识，下面，我们就来看看，利用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查询到。

原文转自：http://www.ltesting.net