MSDN:
A Data Collector Set is the building block of performance monitoring and reporting in Windows Performance Monitor. It organizes multiple data collection points into a single component that can be used to review or log performance. A Data Collector Set can be created and then recorded individually, grouped with other Data Collector Set and incorporated into logs, viewed in Performance Monitor, configured to generate alerts when thresholds are reached, or used by other non-Microsoft applications. It can be associated with rules of scheduling for data collection at specific times. Windows Management Interface (WMI) tasks can be configured to run upon the completion of Data Collector Set collection.
Data Collector Sets can contain the following types of data collectors:
Performance counters
Event trace data
System configuration information (registry key values)
Solution:
Create Data Collector: (see Image 1 & 2)
Create Stored Procedure: (Pivot Data)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[spaPerfMonDataCollectorAll_PivotPerCounter_ByDays]
@TimeFrame char(24)
as
/*
exec dbo.spaPerfMonDataCollectorAll_PivotPerCounter_ByDays '2015-06-01'
*/
select
[CounterDateTime] as 'Time',
[% Processor Time] as '%ProcessorTime',
[User Connections] as 'UserConnections',
[Batch Requests/sec] as 'Batches Requests/sec',
[Page life expectancy] as 'PLE(secs)',
[Avg. Disk sec/Transfer] as 'Avg. Disk sec/Transfer',
[SQL Cache Memory (KB)] as 'SQLCacheMemory(KB)',
[Optimizer Memory (KB)] as 'SQLOptimizerMemory(KB)',
[Cache Pages] as 'PlanCachedPages(#8KPages)',
[Page lookups/sec] as 'Page lookups/sec',
[Readahead pages/sec] as 'Readahead pages/sec',
[Memory grant queue waits] as 'MemoryGrantQueueAvgWaitTime(ms)',
[SQL Attention rate] as 'SQL Attention rate',
[Lock Waits/sec] as 'Lock Waits/sec',
[Average Wait Time (ms)] as 'Lock Average Wait Time (ms)',
[Available MBytes] as 'Available MBytes',
[Pages/sec] as 'Pages/sec',
[Checkpoint pages/sec] as 'Checkpoint pages/sec',
[Database pages] as 'Database pages',
[Bytes Received/sec] as 'BytesReceived /sec',
[Bytes Sent/sec] as ' BytesSent/sec',
[Packets Received/sec] as 'PacketsReceived/sec',
[Packets Sent/sec] as 'PacketsSent/sec',
[Packets Outbound Errors] as 'Packets Outbound Errors',
[Packets Received Errors] as 'Packets Received Errors'
FROM
(
select t2.CounterDateTime, t1.CounterName, t2.CounterValue
from dbo.CounterDetails t1
inner join dbo.CounterData t2 on t1.CounterID = t2.CounterID
where t2.CounterDateTime > @TimeFrame
) t3
PIVOT
(MAX(t3.CounterValue) for t3.CounterName in (
[% Processor Time],
[User Connections],
[Batch Requests/sec],
[Page life expectancy],
[Avg. Disk sec/Transfer],
[SQL Cache Memory (KB)],
[Optimizer Memory (KB)],
[Cache Pages],
[Page lookups/sec],
[Readahead pages/sec],
[Memory grant queue waits],
[SQL Attention rate],
[Lock Waits/sec],
[Average Wait Time (ms)],
[Available MBytes],
[Pages/sec],
[Checkpoint pages/sec],
[Database pages],
[Bytes Received/sec],
[Bytes Sent/sec],
[Packets Received/sec],
[Packets Sent/sec],
[Packets Outbound Errors],
[Packets Received Errors]
)) Q
order by CounterDateTime DESC
Image 1:
Image 2:
Image 3:
|
|
|
|
|
|