SQL Server 2008性能数据收集（Data Collector)的一些扩展话题

发牌SO

　　Data Collector是SQL Server 2008 新增的一个特性，位列管理员需知的top 10列表中。该功能在SQL Server 2008 R2中没有太大的变化
什么是Data Collector
　　关于这个主题，请直接参考微软官方的说明 http://msdn.microsoft.com/zh-CN/library/bb677248.aspx
　　关于如何配置Data Collector的详细步骤，可以参考  http://www.qudong.com/soft/program/Sql%20Server/jichujiaocheng/20090106/28656.html
　　
　　本文主要解释几个与该功能有关的扩展话题，也是我曾经被几次问到的
　　
能不能收集多个实例的数据
　　很多管理员都关心这个话题，因为DBA需要管理多个实例，那么是不是需要在多个实例上面都去配置那个数据仓库呢？
　　应该不是这样的。数据收集器功能的架构是下面这样

　　
　　也就是说，可以只有一个数据仓库（MDW:Management Data Warehouse)，然后在多个Target instance上面，配置收集，并且将其结果发送到这个中心的MDW中来。DBA们可以通过客户端机器，远程控制MDW，并且查看报表。
　　


对性能的影响是怎么样的
　　既然数据收集是在每个需要收集的实例上面直接运行的，那么就有DBA问到，这样的话会不会对这个实例产生不利的影响呢？这个说法是这样，肯定是有影响的，因为性能收集说到底是一种查询，包括对DMV的查询，或者对性能计数器的查询。而它查询是定期运行的。例如Server Activity的话，默认都是60秒收集一次。据一般的估计，如果只是使用了默认的三个系统收集组，而且没有进行修改所有的默认收集或者上传的时间，那么配置了性能收集，对当前实例的影响主要体现在会加重CPU的一点点负担，具体大约是5%左右。数据的体积大约为300MB左右/天。

如何自定义数据集(Collection Set)
　　系统默认自带了3个（SQL SERVER 2008)或者4个（SQL Server 2008 R2)数据集

　　
　　但是，如果我们需要自定义数据集，应该怎么做呢？下面有一个范例脚本
　　请注意，这个脚本是在msdb中进行工作
　　
Use msdb
go
Declare @collection_set_id_1 int
Declare @collection_set_uid_2 uniqueidentifier
EXEC [dbo].[sp_syscollector_create_collection_set]
@name=N'Disk Performance and SQL CPU',
@collection_mode=1,
@description=N'Collects logical disk performance counters and SQL Process CPU',
@target=N'',
@logging_level=0,
@days_until_expiration=7,
@proxy_name=N'',
@schedule_name=N'CollectorSchedule_Every_5min',
@collection_set_id=@collection_set_id_1 OUTPUT,
@collection_set_uid=@collection_set_uid_2 OUTPUT
Select collection_set_id_1=@collection_set_id_1, collection_set_uid_2=@collection_set_uid_2
Declare @collector_type_uid_3 uniqueidentifier
Select @collector_type_uid_3 = collector_type_uid From [dbo].[syscollector_collector_types] Where name = N'Performance Counters Collector Type';
Declare @collection_item_id_4 int
EXEC [dbo].[sp_syscollector_create_collection_item]
@name=N'Logical Disk Collection and SQL Server CPU',
@parameters=N'








',
@collection_item_id=@collection_item_id_4 OUTPUT,
@frequency=5,
@collection_set_id=@collection_set_id_1,
@collector_type_uid=@collector_type_uid_3
Select @collection_item_id_4
go

　　执行完之后，就有下面这样一个新的Collection Set出来

　　
　　然后可以启用它，并且收集，上传
EXEC sp_syscollector_start_collection_set @collection_set_id =
-- replace  with value from above

　　最后，运行下面的脚本可以获得结果（注意，这个脚本不是在msdb中运行，而是在数据仓库中）
select spci.path as 'Counter Path', spci.object_name as 'Object Name',
spci.counter_name as 'counter Name', spci.instance_name,
spcv.formatted_value as 'Formatted Value',
spcv.collection_time as 'Collection Time',
csii.instance_name as 'SQL Server Instance'
from snapshots.performance_counter_values spcv,
snapshots.performance_counter_instances spci,
msdb.dbo.syscollector_collection_sets_internal scsi,
core.source_info_internal csii,
core.snapshots_internal csi
where spcv.performance_counter_instance_id = spci.performance_counter_id and
scsi.collection_set_uid=csii.collection_set_uid and
csii.source_id = csi.source_id and csi.snapshot_id=spcv.snapshot_id and
scsi.name = 'Disk Performance and SQL CPU'
order by spcv.collection_time desc

　　
　　结果大致如下

　　
　　希望对于大家有所帮助