Selecting data over a time period and calculating avg, min and max values of this data

Michel Bruggeman 2013-06-13 11:51:21

id_num instance_id Counter Value CaptureDate
1 6C7243DC-5DBA-4730-83F2-8253C1A51254 MSSQL$TEST:Access Methods:Forwarded Records/sec: 0.00 2013-05-23 15:14:43.693
13 6C7243DC-5DBA-4730-83F2-8253C1A51254 MSSQL$TEST:Access Methods:Full Scans/sec: 38.60 2013-05-23 15:14:43.693
25 6C7243DC-5DBA-4730-83F2-8253C1A51254 MSSQL$TEST:Access Methods:Index Searches/sec: 3894.60 2013-05-23 15:14:43.693
37 6C7243DC-5DBA-4730-83F2-8253C1A51254 MSSQL$TEST:Access Methods:Page Splits/sec: 0.50 2013-05-23 15:14:43.693
49 6C7243DC-5DBA-4730-83F2-8253C1A51254 MSSQL$TEST:Access Methods:Table Lock Escalations/sec: 0.00 2013-05-23 15:14:43.693
61 6C7243DC-5DBA-4730-83F2-8253C1A51254 MSSQL$TEST:Access Methods:Workfiles Created/sec: 123.20 2013-05-23 15:14:43.693
73 6C7243DC-5DBA-4730-83F2-8253C1A51254 MSSQL$TEST:Access Methods:Worktables Created/sec: 33.00 2013-05-23 15:14:43.693
85 6C7243DC-5DBA-4730-83F2-8253C1A51254 MSSQL$TEST:Access Methods:Worktables From Cache Ratio: 2316.00 2013-05-23 15:14:43.693
97 6C7243DC-5DBA-4730-83F2-8253C1A51254 MSSQL$TEST:Buffer Manager:Buffer cache hit ratio: 4416.00 2013-05-23 15:14:43.693
109 6C7243DC-5DBA-4730-83F2-8253C1A51254 MSSQL$TEST:Buffer Manager:Checkpoint pages/sec: 0.00 2013-05-23 15:14:43.693

Michel Bruggeman 2013-06-13 12:24:34
/==============Original Query ========================/

DECLARE @PERF TABLE
(
[Counter] nvarchar,
[AVG_Value] [decimal](38, 2),
[MIN_Value] [decimal](38, 2),
[MAX_Value] [decimal](38, 2),
[Count_Value] int

)

DECLARE @counter_name varchar

DECLARE counters_cursor CURSOR FOR
SELECT DISTINCT [Counter]
FROM [CENMON20].[dbo].[bl_PerfMonData] a
LEFT OUTER JOIN [dbo].[vw_Server_Instance] b ON a.instance_id = b.instance_id
WHERE CaptureDate BETWEEN(SELECT dbo.F_START_OF_WEEK(GETDATE()-7,default))AND
(SELECT dbo.F_START_OF_WEEK(GETDATE(),default))
ORDER BY Counter
— 497 rows
OPEN counters_cursor
FETCH NEXT FROM counters_cursor INTO @counter_name
WHILE @@FETCH_STATUS = 0
BEGIN
— do some stuff
INSERT INTO @PERF
( Counter ,
AVG_Value ,
MIN_Value ,
MAX_Value ,
[Count_Value]
)
SELECT @counter_name AS CounterName,
AVG(Value) as Average,
MIN(Value) as Minimum,
MAX(Value) as Maximum,
COUNT(Value)AS Count_Value FROM
(
SELECT SUBSTRING([Counter], CHARINDEX(':', [Counter]) + 1, ISNULL(NULLIF(CHARINDEX(';', [Counter]), 0), LEN([Counter]) + 1) – CHARINDEX(':', [Counter]) – 1) AS Counter,
Value
FROM [dbo].[bl_PerfMonData]
WHERE Counter LIKE '%'+@counter_name
AND CaptureDate BETWEEN(SELECT dbo.F_START_OF_WEEK(GETDATE()-7,default))AND
(SELECT dbo.F_START_OF_WEEK(GETDATE(),default))
GROUP BY Counter,Value
) AS Value

FETCH NEXT FROM counters_cursor INTO @counter_name
END

CLOSE counters_cursor
DEALLOCATE counters_cursor

SELECT * FROM @PERF

Aaron Bertrand 2013-06-14 14:42:21
Hi Michel, have you considered writing this as something other than a cursor? The plans that are generated within each iteration of the cursor do not seem all that bad on their own, but when your cursor iterates hundreds of times… Also if you can't get rid of the cursor, I would consider using a #temp table instead of a @table variable. The latter are really only good up to a certain size, and I think you're exceeding that threshold by quite a bit. Also the cursor should use LOCAL FAST_FORWARD (see this blog post). Also can you describe what you are doing within the comment — do some stuff?
Michel Bruggeman 2013-06-17 18:31:33
Thanks for you're reply.

— do some stuff is meaningless, I've also partioned the table on a weekly basis. But it runs to slow.

List of the changed script.

CREATE TABLE #PERF
(
[Counter] nvarchar,
[AVG_Value] [decimal](38, 2),
[MIN_Value] [decimal](38, 2),
[MAX_Value] [decimal](38, 2),
[Count_Value] int

)
ON [PRIMARY]

DECLARE @counter_name varchar

DECLARE counters_cursor CURSOR LOCAL FAST_FORWARD
FOR

SELECT DISTINCT [Counter]
FROM [CENMON20].[dbo].[bl_PerfMonData] a
LEFT OUTER JOIN [dbo].[vw_Server_Instance] b ON a.instance_id = b.instance_id
WHERE CaptureDate BETWEEN(SELECT dbo.F_START_OF_WEEK(GETDATE()-14,default))AND
(SELECT dbo.F_START_OF_WEEK(GETDATE()-7,default))
ORDER BY Counter

OPEN counters_cursor
FETCH NEXT FROM counters_cursor INTO @counter_name
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #PERF
( Counter ,
AVG_Value ,
MIN_Value ,
MAX_Value ,
[Count_Value]
)
SELECT @counter_name AS CounterName,
AVG(Value) as Average,
MIN(Value) as Minimum,
MAX(Value) as Maximum,
COUNT(Value)AS Count_Value FROM
(
SELECT SUBSTRING([Counter], CHARINDEX(':', [Counter]) + 1, ISNULL(NULLIF(CHARINDEX(';', [Counter]), 0), LEN([Counter]) + 1) – CHARINDEX(':', [Counter]) – 1) AS Counter,
Value
FROM [dbo].[bl_PerfMonData]
WHERE Counter LIKE '%'+@counter_name
AND CaptureDate BETWEEN(SELECT dbo.F_START_OF_WEEK(GETDATE()-14,default))AND
(SELECT dbo.F_START_OF_WEEK(GETDATE()-7,default))
GROUP BY Counter,Value
) AS Value

FETCH NEXT FROM counters_cursor INTO @counter_name
END

CLOSE counters_cursor
DEALLOCATE counters_cursor

SELECT * FROM #PERF

Michel Bruggeman 2013-06-18 17:53:53
I've made is simple, thanks Jonathan -) :

select Counter, avg(value) AS AVG_VALUE, min(value) AS MIN_VALUE, max(value)AS MAX_VALUE
from [dbo].[bl_PerfMonData]
where CaptureDate between 'date1' AND 'date2'
group by Counter

Aaron Bertrand 2013-06-18 18:27:09
That definitely seems much simpler. 🙂