SQL Server大负载的生产环境下的性能优化：初识元数据优化

淡淡的烟草味﹌ 2022-09-17 14:13 65阅读 0赞

相信朋友对SQL Server性能调优相关的知识或多或少都有一些了解。虽然说现在NOSQL相关的技术非常的火热，但是RMDB(关系型数据库)与NOSQL是并存的，并且适用在各种的项目中。在一般的企业级开发中，主要还是RMDB占据主导地位。并且在互联网项目中，也不是摒弃了RMDB，例如MySQL就在很多的互联网应用中发挥着作用。所以，对数据库的调优是个值得深入学习的课题。本系列文章，主要讲述与SQL Server相关的调优知识，希望能够为朋友们带来一些帮助。

本篇提纲如下：

*  传统SQL Server调优方式的比较
 *  什么是DMV
 *  DMV简单示例
 *  DMV可以解决哪些问题

## 传统SQL Server调优方式的比较 ##

我们平时可以采用很多工具对SQL Server的性能进行诊断分析与调优：性能计数器，SQL Server Profiler和Database Engine Tuning Advisor(数据库引擎优化顾问，简称DTA)。下面我们就来对每一个简单的说明一下，并且讲述各自的优缺点。

### 性能计数器 ###

对于使用Windows操作系统的开发朋友，对性能计数器已经不陌生了，最简单的方式就是运行”perfmon”，就可以打开性能监视器的窗口，然后添加对应的计数器，进行监控。如下图所示：

![image1.jpg][]

性能计数器每隔一段时间就回去收集相关的数据，并且我们还可以把这些数据保存起来，便于以后的分析。

相关厂商内容

### [百度技术沙龙第四十四期： 大数据面面观（2013年11月23日周六）][_2013_11_23] ###

### [记录2013年度top100件设计实践][2013_top100] ###

### [2013年10月26日QClub大连站：大连软件开发者大会][2013_10_26_QClub] ###

### [QCon上海技术训练营：OSGi、GitHub、Scrum深度培训，10月29-31日与您相约，了解详情！][QCon_OSGi_GitHub_Scrum_10_29-31] ###

### [GitHub中国上海Drinkup活动，就在QCon上海2013前夜，贝尼酒吧][GitHub_Drinkup_QCon_2013] ###

优点：使用方便，并且数据收集的比较全面。因为这些操作系统是集成的，不需要额外的花费，就可以直接使用。

缺点：数据不够准确，分析数据成本很高，并且对系统的性能产生影响。因为性能计数器在收集数据的时候，要定时的去抓取系统相关的数据，这样，会对性能造成影响，如果抓取的时间间隔越短，那么对系统的影响就越大。另外，对于收集到的数据，也需要有经验丰富的人去分析数据，并且如果要准确的分析出结果，需要收集大量的数据，人力和时间的成本也高。

### SQL Server Profiler ###

每次谈到SQL Server调优，势必要讲到SQL Server Profiler（为了后续的讲述方便，我们将会把它简称为Profiler）。使用Profiler能够捕获一段时间内SQL执行的每个查询的记录。当数据库服务器上有大量很少运行的查询时，或者有特别的用户查询运行的时候，这个工具很有用。使用Profiler还能捕获到指定时间段内的工作负荷，然后可以在恢复的数据库系统中进行重现。

下面就是一个使用Profiler的界面：

![image2.jpg][]

在使用Profiler的时候，在哪里启动Profiler以及将跟踪的数据保存在何处，是一个特别需要考虑的问题。下面，我们就用来对比地看看各种不同的情况。

<table style="border-collapse:collapse; border-spacing:0px; margin:10px 0px; border:1px solid rgb(224,224,224); padding:0px; color:rgb(0,0,0); font-family:Arial,sans-serif; font-size:14px; line-height:21px; width:750px; height:270px"> 
 <tbody style="margin:0px; border:0px; padding:0px"> 
 <tr style="margin:0px; border:0px; padding:0px"> 
 <td style="margin:0px; padding:0px; border:1px solid rgb(224,224,224)"> 启动Profiler的位置 </td> 
 <td style="margin:0px; padding:0px; border:1px solid rgb(224,224,224)"> 跟踪文件 </td> 
 <td style="margin:0px; padding:0px; border:1px solid rgb(224,224,224)"> 说明 </td> 
 </tr> 
 <tr style="margin:0px; border:0px; padding:0px"> 
 <td style="margin:0px; padding:0px; border:1px solid rgb(224,224,224)"> 在正在监视的数据库服务器上启动Profiler的跟踪 </td> 
 <td style="margin:0px; padding:0px; border:1px solid rgb(224,224,224)"> 将跟踪的数据文件保存到服务器的文件系统或共享目录中 </td> 
 <td style="margin:0px; padding:0px; border:1px solid rgb(224,224,224)"> 优点：这种方式适用于非生产环境和低负载服务器上的开发和对数据库应用进行性能诊断和跟踪。 缺点：加大了服务器的I/O读写操作 </td> 
 </tr> 
 <tr style="margin:0px; border:0px; padding:0px"> 
 <td style="margin:0px; padding:0px; border:1px solid rgb(224,224,224)"> 在本机开启Profiler，连接远程的数据库服务器 </td> 
 <td style="margin:0px; padding:0px; border:1px solid rgb(224,224,224)"> 将跟踪的数据文件保存在本地 </td> 
 <td style="margin:0px; padding:0px; border:1px solid rgb(224,224,224)"> 优点：减小了数据库服务器的I/O操作。 缺点：加大了网络传输的压力，占用数据库服务器的网络资源和CPU资源。因为需要将大量的跟踪数据传送到本机，这样占用了网络资源；而数据在传输的时候，需要CPU将之序列化，加大了CPU的操作。 </td> 
 </tr> 
 </tbody> 
</table>

数据库引擎优化顾问（DTA）

DTA一般需要和Sql Server Profiler结合在一起使用。DTA主要是对Profiler中收集到的数据进行纯数据的综合分析，所以它分析结果的准确性非常依赖于Profiler收集的数据量的多少。Profiler收集的数据越多，那么DTA分析的就越准确，但是这样也对数据库服务器的压力越大，反之。所以，一般不建议在大负载或者生产环境下的数据库服务器上面采用。

如果有需要在大负载，或者生产环境下对数据库的性能进行分析与调优，那如何处理？

这就是我们本次系列文章要讨论的话题。

## 什么是DMV ##

使用过SQL Server的朋友，对DMV（Dynamic Management Views，动态管理视图）或多或少都有一些了解或者耳闻。其实DMV就是SQL Server内核的元数据，通过对内部的元数据的分析，我们快速而准确获取很多与SQL Server内部相关的信息，从而进行性能分析。

当查询在SQL Server中运行时，SQL Server会自动的将此次活动的相关信息记录下来，并且保存在内存之中，这些活动信息，就称之为：DMV。

不同类型的DMV信息，有不同的用途，例如，可以相关的DMV来对性能进行诊断，从而提升性能，或对数据库的运行进行监控，或解决故障等。

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中保存了什么信息）：

1.  查询的执行计划（即描述了一个查询是如何被执行的）
2.  什么索引被使用
3.  什么索引本来应该被用到，但是又没有使用。（因为此时存在缺失索引的性能问题）
4.  I/O的状态（包含逻辑I/O操作和物理的I/O操作）
5.  查询执行消耗的时间
6.  查询等待其他资源消耗的时间
7.  查询在等待什么资源

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

一般而言，在使用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 语句：

![image3.jpg][]

此时，运行的结果如下：

![image4.jpg][]

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

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

1.  查询DMV时，应该尽可能的将对数据库的影响降到最小。所以，我们在查询的最上面，加上了：SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED。因为每次在运行查询的时候，或多或少的会对数据库产生不同程度的锁定，并且锁定的级别各不一样。通过上面的设置，就告诉SQL Server，接下来的执行的查询将锁定的级别定为：Read Uncommitted。从而将影响减小到最小。
2.  每次进行性能问题诊断的时候，首先要解决最严重的性能问题。所以，我们此处只是找出前20个运行最慢的查询语句。
3.  虽然原生的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查询到。

到这里，不知道朋友们是否有点“跃跃欲试”的冲动，我们在后续，会为朋友们一一奉上。敬请关注。

## 关于作者 ##

汪洋，现任惠普架构师、信息分析师《NET应用架构设计：模式、原则与实践》作者。上海益思研发管理咨询有限公司首席软件架构专家，软件咨询组副组长

[image1.jpg]: /images/20220828/6ca2202c1c36444db0f588b80943e2d2.png
[_2013_11_23]: http://www.infoq.com/cn/vendorcontent/show.action?vcr=2498&utm_source=infoq&utm_medium=VCR&utm_campaign=vcr_articles_click
[2013_top100]: http://www.infoq.com/cn/vendorcontent/show.action?vcr=2433&utm_source=infoq&utm_medium=VCR&utm_campaign=vcr_articles_click
[2013_10_26_QClub]: http://www.infoq.com/cn/vendorcontent/show.action?vcr=2487&utm_source=infoq&utm_medium=VCR&utm_campaign=vcr_articles_click
[QCon_OSGi_GitHub_Scrum_10_29-31]: http://www.infoq.com/cn/vendorcontent/show.action?vcr=2451&utm_source=infoq&utm_medium=VCR&utm_campaign=vcr_articles_click
[GitHub_Drinkup_QCon_2013]: http://www.infoq.com/cn/vendorcontent/show.action?vcr=2484&utm_source=infoq&utm_medium=VCR&utm_campaign=vcr_articles_click
[image2.jpg]: /images/20220828/d29de537b4cc4f9b83d7174aec8b33f3.png
[image3.jpg]: /images/20220828/5e644259904d44b6a7c248249c75906a.png
[image4.jpg]: /images/20220828/8b91f932ef8a4120bf7ffb5b064075d0.png