配置数据收集器:
1.创建登录名并映射角色
2.配置管理数据仓库
3.创建收集组、收集项----MSDB数据存储 sp_syscollector_create...
4.自动配置相关job
具体步骤:
创建好之后的图:
配置数据收集:
刷新如下图所示:
创建的方法:
use msdb
go
declare @collectionsetid int --有返回值
exec sp_syscollector_create_collection_set @name='性能收集集合',
--收集的模式 缓冲模式
@collection_mode=0,
--无效的日期
@days_until_expiration=7,
--计划的时间
@schedule_name='CollectorSchedule_Every_15min',
@description='主要收集动态管理视图性能数据与计数器的相关性能数据',
--收集集的id
@collection_set_id =@collectionsetid output
declare @typeuid uniqueidentifier
declare @collectionitemid int
--赋值 计数器的定义
set @typeuid=(select collector_type_uid from syscollector_collector_types where name=
'Performance Counters Collector Type')
--定义数据收集项 一:数据收集集合的id 二:收集集合类型的uid
exec sp_syscollector_create_collection_item @collection_set_id=@collectionsetid,
@collector_type_uid=@typeuid,@name='收集内存与CPU计数器信息',
--指定频率
@frequency=5,
--收集项的id
@collection_item_id =@collectionitemid output,
--收集的内容
@parameters =N'
<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">
<PerformanceCounters Objects="Processor" Counters="% Processor Time" />
<PerformanceCounters Objects="Memory" Counters="Pages/sec" />
</ns:PerformanceCountersCollector>
'
--动态管理视图的收集
set @typeuid=(select collector_type_uid from syscollector_collector_types where name=
'Generic T-SQL Query Collector Type')
exec sp_syscollector_create_collection_item @collection_set_id=@collectionsetid,
@collector_type_uid=@typeuid,@name='通过动态管理视图收集CPU信息',
@frequency=5,@collection_item_id =@collectionitemid output,
@parameters =N'
<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
<Query>
<Value>
select top 10 execution_count,total_worker_time,convert(varchar(2000),t.text) as sqltext
,total_worker_time/execution_count
as avgcpu from sys.dm_exec_query_stats as s
cross apply sys.dm_exec_sql_text(s.sql_handle) as t
order by avgcpu desc
</Value>
<OutputTable>
HRPerformanceTB
</OutputTable>
</Query>
<Databases UseSystemDatabases="false" UseUserDatabases="true">
<Database>SunliyuanDW</Database>
</Databases>
</ns:TSQLQueryCollector>
'
具体用法参考:
https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=ZH-CN&k=k(SP_SYSCOLLECTOR_CREATE_COLLECTION_SET_TSQL);k(SQL12.SWB.TSQLRESULTS.F1);k(SQL12.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true
--查询系统视图
select * from syscollector_collector_types
计数器遵循的结构:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="DataCollectorType">
<xs:element name="PerformanceCountersCollector">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="PerformanceCounters">
<xs:complexType>
<xs:attribute name="Objects" type="xs:string" use="required" />
<xs:attribute name="Counters" type="xs:string" use="required" />
<xs:attribute name="Instances" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="StoreLocalizedCounterNames" type="xs:boolean" use="optional" default="false" />
</xs:complexType>
</xs:element>
</xs:schema>
T-Sql中的写法:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="DataCollectorType">
<xs:element name="TSQLQueryCollector">
<xs:complexType>
<xs:sequence>
<xs:element name="Query" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="Value" type="xs:string" />
<xs:element name="OutputTable" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Databases" minOccurs="0" maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Database" minOccurs="0" maxOccurs="unbounded" type="xs:string" />
</xs:sequence>
<xs:attribute name="UseSystemDatabases" type="xs:boolean" use="optional" />
<xs:attribute name="UseUserDatabases" type="xs:boolean" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
性能的数据仓库:性能计数器信息
点击:
执行完后右键查看属性:
说明创建成功
在缓存文件夹中产生性能的数据:
对应的性能表中也有数据:
成功后的日志:
新建表中会产生数据:
会自动的生成作业计划:
遇到的错误:
解决方案:(启动这个服务)
存在无名的数据列:convert 后记得 起别名:sqltext