通过SQL Server数据仓库查看数据收集组.doc

通过SQL Server数据仓库查看数据收集组发布日期：10-01-11 10:53:46 点击次数：4 在此实验中，您将查看系统支持的数据收集类型和收集计划，并配置其属性。 1.通过管理数据仓库向导查看系统收集组及其状态管理数据仓库配置完成后，向导不仅仅会创建数据库(即ManagementDW)，同时还将自动启动SQL Server Agent 作业来收集并更新系统数据收集组(1)在CHICAGOSQLDEV01 实例中返回Object Explorer 并依次展开Management, Data Collection, System Data Collection Sets. 此时可以看到三种系统数据收集组：Disk Usage, Query Statistics, 以及 Server Activity.(2)右键点击Disk Usage然后点击Properties. Disk Usage统计数据的完整属性将通过三个选项卡显示，即General, Uploads 以及Description.点击Description选项卡可以看到: Collects data about disk and log usage for all databases. 此描述信息并不是很详细，但可以让我们快速了解到磁盘使用的统计信息;这些信息针对实例中的所有数据库的数据文件和日志文件。(3)点击Uploads 选项卡可以看到所有信息均为灰色，因为这些特殊的数据集都采用非缓存模式收集。在非缓存模式中数据的收集和上传通过相同的代理作业进行处理。因此Uploads选项卡中的信息对于此收集组来说没有实际意义。非缓存模式对于相对负载较轻或数据集收集操作不是很频繁的情况下很适用(4)最后，点击General 选项卡，可以看到该数据收集的特定属性：Data collection and upload在这里可以将Data collection and upload 更改为缓存模式。但由于我们实验中的数据收集操作并不是很频繁，因此不需要进行缓存。如果数据收集操作很复杂且很频繁，则建议选择缓存模式。理想状态下，缓存将只对进行收集的本地服务器性能产生影响，然后再将收集上来的数据慢慢上传到管理数据仓库。例如，针对特定数值的收集频率可能为30-60秒，但上传频率可以设置为30或60分钟。Schedule:点击 Pick 可以看到可用的计划。在这里可以看到我们选择的计划是occur every day every 6 hour(s) between 12:00:00 AM and 11:59:59 PM 并且使用该计划的作业数为1.如果设置多台服务器采用相同的计划进行数据收集并将数据上传到相同的管理数据仓库，则建议将作业的启动时间岔开，这样可以更好的平衡管理数据仓库的负载。Collection items这里可以看到两个项目：Disk Usage Data Files 和Disk Usage Log Files. 每一个收集器的类型均为 “Generic T-SQL Query Collector Type” ，因此在Input parameters 中的T-SQL脚本将用于执行这些收集项目Disk Usage Data Files选择Disk Usage Data Files 行，然后在Input parameters 文本框中将显示下列代码：-disk_usageDECLARE dbsize bigintDECLARE logsize bigintDECLARE ftsize bigintDECLARE reservedpages bigintDECLARE pages bigintDECLARE usedpages bigintSELECT dbsize = SUM(convert(bigint,casewhen type = 0 then size else 0 end),logsize = SUM(convert(bigint,casewhen type = 1 then size else 0 end),ftsize = SUM(convert(bigint,casewhen type = 4 then size else 0 end)FROM sys.database_filesSELECT reservedpages = SUM(a.total_pages),usedpages = SUM(a.used_pages),pages = SUM(CASEWHEN ernal_type IN (202,204)THEN 0WHEN a.type != 1THEN a.used_pagesWHEN p.index_id 2THEN a.data_pagesELSE 0END)FROM sys.partitions pJOIN sys.allocation_units aON p.partition_id = a.container_idLEFT JOIN ernal_tables itON p.object_id = it.object_idSELECT dbsize as dbsize,logsize as logsize,ftsize as ftsize,reservedpages as reservedpages,usedpages as usedpages,pages as pages选中这些代码并将其复制到新建的查询窗口中，首先注释掉第一行(disk_usage)，然后在 master 数据库中执行这段代码查询结果如下图所示：请注意上述查询结果只针对一个数据库，而通过SQL Server Agent 作业执行时将收集并上传所有数据库的数据集Disk Usage Log Files选中Disk Usage Log Files 然后查看Input parameters 文本框中的相关代码注意：在此版本中，针对Disk Usage Log Files 的数据收集操作是通过执行DBCC SQLPERF (logspace)语句而实现的Specify the account to run the collection set保留默认设置SQL Server Agent Service Account 即可Specify how long to retain data in the management data warehouseDisk Usage 的数据收集并不会产生大量的数据，默认的保留期限是730 天。(假设系统有10个数据库，则在730天中将会有29200行的数据产生。即每天收集4次，则730天中共插入4 x 10 x 730 = 29200 行)如果数据库数量很多且收集频率很高，则可以将保留期限适当减少(5)连接到CHICAGOCONFIGSERVER 实例中，新建查询窗口并直接查询ManagementDW.snapshots.disk_usage 和ManagementDW.snapshots.log_usage 这两个表：SELECT * FROM ManagementDW.snapshots.disk_usageSELECT * FROM ManagementDW.snapshots.log_usage(6)除了直接查询数据集，还可以在Object Explorer 中通过报表来进行查看，下一个练习当中我们将使用该功能。出于实验目的，在此我们还需要修改一些收集属性，加大进行数据收集的频率，以便更清晰的看到结果。在这里我们将收集频率调整为15分钟。在真正的生产环境中，不建议这样设置，否则将影响msdb 数据库以及管理数据仓库的性能。(7)修改System Data Collection Sets 收集属性的方法有很多。根据缓存模式的区别，每个收集组都有1个或2个作业，例如Server Activity采用缓存模式，有2个作业;而Disk Usage 和Query Statistics 则采用非缓存模式，因此只有1个作业。为了提高收集和上传的频率，我们可以更改作业计划。重要提示：在进行充分测试以前，不要在生产环境中更改收集频率2.为查询统计信息以及磁盘使用更改作业计划，每分钟收集数值(1)在CHICAGOSQLDEV01 实例和 CHICAGOCONFIGSERVER 实例的Object Explorer 中，依次展开SQL Server Agent 节点和Jobs 节点，查看当前的作业(2)每个SQL Server 实例中至少会有一个作业，用于进行系统健康的监控。详细信息，请参考下面链接中的文章：/psssql/archive/2008/07/15/supporting-sql-server-2008-the-system-health-session.aspx(3)其它作业是我们手动创建的(目前在CHICAGOSQLDEV01 实例中有5个用于收集数据的实例，在CHICAGOCONFIGSERVER 实例中有一个作业用于清除历史记录，如下表所示：Data Collection Set Type Cached v. Non-cached Job Collection or Upload (or both) Current Job Name Descriptive Job Name(change to)Disk Usage Non-cached Both collection_set_1_noncached_collect_and_upload DiskUsage_noncachedQuery Statistics Cached Collection collection_set_2_collection QueryStats_collectionQuery Statistics Cached Upload collection_set_2_upload QueryStats_uploadServer Activity Cached Collection collection_set_3_collection ServerActivity_collectionServer Activity Cached Upload collection_set_3_ upload ServiceActivity_upload根据上述表格，将每个作业的名称改为名称，即上表的最后一列 (4)在CHICAGOSQLDEV01 实例中，右键点击SQL Server Agent 然后依次点击New 和Schedule(5)在New Job Schedule 对话框，输入JobSchedule_Every_1min 作为作业名称，然后如下图所示进行设置：(6)确定作业将在午夜每隔1分钟执行一次，然后点击 OK(7)将这个计划添加到下面两个作业中：DiskUsage_noncached 和QueryStats_upload. 右键点击作业名称，然后点击Properties, 在Schedules 选项卡中，点击Pick 并选择JobSchedule_Every_1min 计划。为ServerActivity_Upload 选择CollectorSchedule_Every_5min 计划。重要提示：在生产环境中设置收集频率过高的时候要特别小心3.为数据收集和分析操作而生成相关信息此任务并不复杂，但第一个批处理文件需要执行将近5分钟的时间，第二个批处理文件将打开多个SQLCMD 命令行窗口，直到手动停止。这些脚本在进行分析前都需要时间。因此我们先将它们启动，直到后面的步骤再将其停止。另外，我们不需要关注这些脚本如何运行也不需要等待它们执行完毕。(1)运行批处理文件。打开我的电脑，然后打开下面的文件夹：C:Manageability LabsPerformance Data Collection(2)双击GenerateData.cmd 文件。该脚本将执行将近5分钟的时间直至完成(3)双击GenerateActivity.cmd 文件。该脚本将循环执行，直到我们在后面的练习中关闭该窗口查看Server Activity 和 Query Statistics 这两个系统数据收集组的代码、设计、以及参数(4)返回到CHICAGOSQLDEV01 实例的Object Explorer 并依次展开Management, Data Collection, 以及System Data Collection Sets. 在此可以看到三个系统数据收集组：Disk Usage, Query Statistics, and Server Activity.(5)右键点击Query Statistics 然后点击Properties. 注意该收集组和Disk Usage 数据收集组属性的区别：相同点 两者均为非缓存模式 两者在Uploads 选项卡中均为灰色 两者均通过SQL Server Agent 运行不同点 Disk Usage 数据收集组通过T-SQL 语句实现，在Input Parameters 中可以看到相关语句。而Query Statistics 则通过 内部的 “Query Activity Collector Type” 来处理，且无法直接查看。该数据收集组所收集的内容在Description 选项卡中可以看到：Collects query statistics, sql text and plans of most performance-affecting querie