1/*===============================================================================
2 Date: 2012.06.01
3 Description: Performance counter check
4 SQL Server version: 2005 or newer
5 Author: Berke János - IamBerke.com
6---------------------------------------------------------------------------------
7 (cc) 2012, IamBerke.com
8
9You may alter this code for your own *non-commercial* purposes.
10You may republish altered code as long as you give due credit.
11THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
12EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES
13OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
14================================================================================*/
15
16SET NOCOUNT ON;
17
18DECLARE @SampleInterval int = 1 --Sampling interval in seconds
19DECLARE @SampleDuration int = 60 --Sampling duration in seconds
20
21DECLARE @minutes int = (SELECT @SampleInterval % 3600);
22DECLARE @delay char(8) = (SELECT RIGHT('0' + CAST((@SampleInterval / 3600) AS varchar(2)),2) + ':' + RIGHT('0' + CAST((@minutes / 60) AS varchar(2)),2) + ':' + RIGHT('0' + CAST((@minutes % 60) AS varchar(2)),2));
23
24
25IF OBJECT_ID('tempdb..#PerfCounters') IS NOT NULL
26 DROP TABLE #PerfCounters;
27
28CREATE TABLE #PerfCounters
29(
30 [id] int,
31 [object_name] nchar(128),
32 [counter_name] nchar(128),
33 [instance_name] nchar(128),
34 [cntr_value] bigint,
35 [cntr_type] int,
36 [date] datetime
37)
38
39DECLARE @i int = 1;
40DECLARE @date datetime;
41DECLARE @msg varchar(100);
42WHILE @SampleDuration >= @i
43 BEGIN
44 SET @date = GETDATE();
45 SET @msg = (SELECT 'Sampling ' + CAST(@i as varchar(8)) + ' of ' + CAST(@SampleDuration AS varchar(8)) + ' started.')
46 RAISERROR(@msg, 10,1) WITH NOWAIT;
47
48 INSERT INTO #PerfCounters ([id], [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type], [date])
49 SELECT
50 @i,
51 [object_name],
52 [counter_name],
53 [instance_name],
54 [cntr_value],
55 [cntr_type],
56 @date
57 FROM
58 sys.dm_os_performance_counters
59
60 SET @i += @SampleInterval;
61 WAITFOR DELAY @delay;
62 END
63;WITH A AS (
64SELECT
65 A.[id],
66 A.[object_name],
67 A.[counter_name],
68 A.[instance_name],
69 A.[cntr_value],
70 A.[cntr_type],
71 A.[date]
72FROM
73 #PerfCounters A
74WHERE
75 A.cntr_type = 1073939712)
76, B AS(
77SELECT
78 A.[id],
79 A.[object_name],
80 A.[counter_name],
81 A.[instance_name],
82 A.[cntr_value],
83 A.[cntr_type],
84 A.[date]
85FROM
86 #PerfCounters A
87WHERE
88 A.cntr_type = 537003264
89)
90
91SELECT
92 A.[object_name],
93 A.[counter_name],
94 A.[instance_name],
95 CAST(B.[cntr_value] - A.[cntr_value] AS decimal(20,8)) AS [cntr_value] ,
96 A.[cntr_type],
97 B.[date]
98FROM
99 #PerfCounters A
100JOIN
101 #PerfCounters B ON A.ID = B.[ID] - 1 AND A.[counter_name] = B.[counter_name] AND A.[instance_name] = B.[instance_name]
102WHERE
103 A.cntr_type = 272696576
104UNION ALL
105SELECT
106 A.[object_name],
107 A.[counter_name],
108 A.[instance_name],
109 CAST(A.[cntr_value] AS decimal(20,8)),
110 A.[cntr_type],
111 A.[date]
112FROM
113 #PerfCounters A
114WHERE
115 A.cntr_type = 65792
116UNION ALL
117SELECT
118 B.[object_name],
119 B.[counter_name],
120 B.[instance_name],
121 CAST(B.[cntr_value] AS decimal(20,8)) / CAST(A.[cntr_value] as decimal(20,8)) AS [cntr_value],
122 B.[cntr_type],
123 B.[date]
124FROM
125 A
126JOIN
127 B ON A.ID = B.[ID] AND RTRIM(A.[counter_name]) = RTRIM(B.[counter_name]) + ' base' AND A.[instance_name] = B.[instance_name]
128WHERE
129 A.[cntr_value] <> 0; --removes not used features
130
131
132--select distinct object_name, counter_name from sys.dm_os_performance_counters order by object_name, counter_name asc