初探SQL Server 2008的性能数据收集器

• 数据
• 收集
• 性能
• Server
• SQL
• 可以
• 报表
• 一个
• 存

SQL Server中有不少工具可以帮助DBA们对性能问题进行识别和排障，诸如事件探查器、系统监视器、数据库引擎调优顾问、Management Studio和T-SQL命令等等。上述工具中有的可以记录数据库的历史日志，有的则不能。即便你所使用的工具能够记录日志，它捕获和分析日志数据的能力也通常比较难用。除非你手动编写自己的日志解决方案，有的工具甚至不允许记录任何类型的日志，诸如监视动态管理视图(DMV)。不同的工具和数据收集方法的杂乱无章，使得DBA们很难轻松的识别并修复不同的性能问题。

一种完美的情况是，SQL Server应该包含这样一个工具，它不仅仅能够自动的收集所有重要性能数据，供DBA们识别和修复性能问题;它还应将这些数据以一种单一的格式进行存储，并且存储在同一个地方，而且还包含高级报表功能，以允许DBA们能够轻松的理解这些数据，从而可以确定相应的解决方案。

尽管SQL Server 2008不包含完美的性能收集和分析工具，它却提供了一个叫做性能数据收集器的新功能，可以说它已经朝着理想的工具迈出了第一步。

简而言之，性能数据收集器在以下几方面对DBA们带来了帮助：

·作为一个中心数据库(Central Data Repository)使用

性能数据收集器的一部分是管理数据仓库(Management Data Warehouse，MDW)。它在一个中心点上存储所有收集来的数据。它可以存储来自于单个SQL Server数据库实例的数据，也可以存储来自多个实例的数据。尽管SQL Server 2008中的管理数据仓库功能的重点是存储性能相关的数据，不过在SQL Server将来的版本中，它将可以存储你希望从SQL Server中收集的任何数据，诸如扩展事件(Extended Events)、审计数据等等。MDW是可扩展的，因此你可以根据需要，在MDW中存储自己的数据。

·收集选择的SQL Server性能数据

MDW用来存储性能数据，而真正的数据收集工作由数据收集组(Data Collection Sets)来执行。SQL Server 2008具有三个内置的数据收集组：一个用于收集磁盘使用(Disk Usage)信息;另一个用于收集查询统计数据(Query Statistics);第三个用于收集大量的服务器行为(Server Activities)数据。据微软表示，这三个数据收集组可以收集那些识别和排障多数常见SQL Server性能问题所需的重要数据。如果你认为这些数据还不够用，你可以创建自己的定制数据收集组。

·显示性能报表

存储在数据仓库内的数据如果不被使用，就毫无价值。SQL Server 2008包含了三个内置报表，包括磁盘使用摘要(Disk Usage Summary)、查询统计历史(Query Statistics History)和服务器行为历史(Server Activity History)。这些报表都可以让你检查单个SQL Server实例的历史记录，另外，在每一个报表中，你都可以追溯到子报表中，让你更详细的查看服务器信息。如果你认为这些内置报表不够完整，或者你希望获得关于特定数据的报表，或者产生包含来自多个SQL Server实例信息的报表，你需要使用SQL Server的Business Intelligence Development Studio或任何其它的报表服务工具，来创建自己的定制报表。

无论是SQL Server 2008标准版还是企业版，都完全支持性能数据收集器功能。它只适用于SQL Server 2008实例，不能兼容此前版本的SQL Server。

如何配置性能数据收集器

在你安装了SQL Server 2008后，性能数据收集器并没有默认配置好。你可以通过以下两步来对其配置，并将其启用。

1.第一步是使用“配置管理数据仓库(Configuration Management Data Warehouse)”向导来创建管理数据仓库数据库，选择“创建或升级一个管理数据仓库(Create or Upgrade a Data Management Warehouse)”选项。尽管这个数据库可以被存储在任何SQL Server上，你还是更应该为其专门创建一个数据库实例。这样，有助于帮助你降低性能数据收集器对你的生产服务器的影响。一个中心MDW可以为多个 SQL Server实例存储数据。

2.第二步是启用性能数据收集器。为了实现这个目的，你需要再次启动“配置管理数据仓库(Configuration Management Data Warehouse)”向导，不过这次你要选择“建立数据收集(Setup Data Collection)”选项，然后它会让你选择希望将性能数据存储在哪一个服务器和MDW数据库上。一旦该向导完成后，性能数据收集器就会被启用，立即开始数据收集。

图1 配置管理数据仓库向导#p#副标题#e#

如果你希望在多个SQL Server 2008实例上启用性能数据收集器，则必须在每个你希望监控的实例上运行一次该向导，并且每次都把中心MDW数据库服务器地址指向你的服务器。

性能数据收集器工作原理

当性能数据收集器初次建立时，它会完成许多幕后工作。例如，它会创建一个SSIS包，用于收集数据并传送到管理数据仓库中。它还将创建一系列计划任务，用户按设定好的规则来执行任务。另外，它还会在MSDB数据库增加新表，以存储日志和其它配置信息。

掌握性能数据收集器工作原理的最简单方法是，理解数据从一开始产生到最后存储在MDW中的整个过程。在本文中限于篇幅，我们以服务器行为数据收集器(Server Activity Data Collector)为例进行介绍。

服务器行为数据收集组主要用来收集两种不同种类的数据：DMV快照和性能计数器，其中后者对于监控SQL Server的整体性能非常有帮助。从服务器行为属性窗口中可以一目了然的看到这两种数据类型。

图2 每一个数据收集组集都有自己的属性界面

在图2中，在“收集条目(Collection Items)”下有两种数据类型。第一个是“Server Activity – DMV Snapshots”，每隔60秒对特定DMV进行一次快照。在“输入参数(Input Parameters)”下，你可以查看用来收集数据的T-SQL代码。从图2截屏中你看到的只是所有代码的一部分，不过你可以拖动滑动块来查看其全部代码。

图3 服务器行为—性能计数器收集条目被高亮#p#副标题#e#

图3是选中“Server Activity – Performance Counters”时的截屏。在下面的输入参数屏幕中，你可以看到，某些性能监控计数器被每隔60秒收集一次。和DMV快照一样，这只是其中一个特定计数器的快照。

现在我们对服务器行为数据收集组所收集的数据类型有了简单的了解，那么这个性能数据收集器是如何收集这些信息并将其存储在MDW中的呢?

数据收集实际上就是部分SQL Server代理任务的按计划执行。当运行一个任务时，它会启动一个叫做数据收集器运行组件(dcexec.exe)的程序，用来加载和执行SSIS包。在本文的例子中，SSIS包每隔60秒收集DMV和性能监控器计数器，然后这些信息被存储在被监控SQL Server实例的一个本地文件夹中。值得注意的是，在上图中的“Cached – Collect and Update Data on the Same Schedule option,”选项要被选中。

随后每隔15分钟，将执行另一个SQL Server代理任务和SSIS包，收集存储在本地缓存文件夹中的数据，并将其移动到MDW中。从下图中，你可以看到属性页面的另一部分，在这儿你可以设定多长时间进行一次上传数据。

图4 设定数据上传计划

一旦数据被移动到MDW后，它就以使用三种内置报表之一或任何你创建的定制报表来形成报表。

数据收集器通过两种不同的方式来实现数据被收集和移动到MDW中：缓冲和非缓冲。缓冲方式就是上面我所介绍的方式，一个任务和SSIS包用于收集数据并将其存储在本地缓冲文件中，然后另一个任务和SSI包将数据从本地缓冲文件移动到MDW中。这种方式有助于降低数据收集器的负载，因为它降低了数据在监控实例和MDW之间的移动频率。

非缓冲方式，在磁盘使用数据收集组中使用，其工作方式略有不同。它不使用两步来移动数据到MDW，而是一步完成收集和上传数据工作。这种方式会略微增加负载，但是如果不频繁使用，不会对SQL Server的性能带来太大影响。

数据收集器的另一个功能是老数据会按照一个默认的计划来自动从MDW中清除，或者你也可以在图2和图3中设定数据在MDW中保留的时间。

你或许会问这样一个问题，性能数据收集器会产生多大负载。尽管这可能取决于你的服务器的负载以及你的服务器的硬件，通常来讲，在默认数据集设置下，它会增加4%左右的CPU利用率，每天收集大约250-300MB的数据。如果你创建自己的数据集合集，其负载可能要更大一些。

性能数据收集器的报表功能

性能数据收集器包含三个内置报表，每一个默认数据收集组对应一个报表。在本篇文章中，我们不会对每一个进行详细介绍，只是对它们进行快速的亮点介绍，如果你想深入的了解它们，可以自己去使用研究。

首先，让我们来看一下磁盘使用报表。

图5 磁盘使用数据收集组跟踪报告磁盘空间信息#p#副标题#e#

该报告跟踪你的MDB和LDF文件的磁盘空间使用情况，既提供真实的数据也可显示简单的趋势线。这些信息可以帮助DBA更加主动，防止遭遇空间用尽的情况发生。点击其中的任何数据库，你将看到一个子报表，显示每一个数据库中数据是如何划分空间的。

接下来的报表是查询统计数据历史报表。

图6 使用查询统计数据历史报表帮你找出占用大量SQL Server资源的查询

虽然在上图的例子中你没有看到太多查询行为，不过你所看到的只是使用CPU资源最多的前10个查询。你可以根据CPU使用率、总体I/O、物理读操作和逻辑写等对这些数据进行分类。在你找出了最耗资源的查询后，你可以对它们逐一进行分析，该报表为你提供了关于该查询的足够多的详细信息，包括它的完整代码和图形化执行计划等。

第三个报表是服务器行为历史报表。

图7 服务器行为历史报表包含性能监控器计数器和DMV的等待状态信息

在所有报表中，这个报表提供的数据最多。你不仅能够跟踪基本硬件资源信息，诸如CPU使用率、内存使用率、磁盘I/O使用情况和网络使用情况，你还可以查看最活跃的SQL Server等待状态;以及SQL Server的行为，诸如登录、事务处理、用户连接等等。你可以对该界面上的任何信息进行追溯，以查看更详细具体的信息。

总结

通过本篇文章我们了解到，SQL Server 2008性能数据收集器可以让我们创建一个中心数据库来存储性能数据;它包含三个内置数据收集组来收集和存储数据;为了帮助我们识别和排除SQL Server性能相关的问题，我们可以使用其内置的三个报表来查看收集存储的数据。

我需要重点强调的一件事情是，如果你希望在自己的生产服务器上启用该功能，需要首先进行测试工作。原因有二，一是你必须评估一下它是否能够提供你想要的信息;二是你是否能接受它所带来的负载增加。如果这两方面都没有问题，你就可以开始设置启用这个SQL Server 2008的新功能了。