Firehose mode: Grouping performance monitor time data after pivoting help please

O.K., here is the problem. I am setting up performance monitoring for
various 2000 and 2003 boxes at my enterprise. I have got Performance
Monitor recording the data into a sql2000 database. The problem I've
got is the way that it stores data required me to write TSQL to Pivot
the data because of the columnar way it is represented by Performance
monitor. Performance monitor creates three tables called CounterData,
CounterDetails and DisplayToID. If I do a join between CounterData and
CounterDetails, on the 'CounterID' column, I can extract the
following meaningful data:
CounterDate MachineName CounterValue
2006-06-07 12:26:14.891 <Server1> 99.8566308243728
2006-06-07 12:27:14.860 <Server1> 99.8704663212435
2006-06-07 12:28:14.860 <Server1> 99.8611111111111
2006-06-07 12:29:14.860 <Server1> 99.8506905561777
....
2006-06-08 07:34:21.908 <Server2> 99.7949850181359
2006-06-08 07:35:21.909 <Server2> 99.7628170041963
2006-06-08 07:36:21.909 <Server2> 99.7827540106952
2006-06-08 07:37:21.910 <Server2> 99.7800338409476
....
In order to get this data into a linear graph, I have to Pivot the
MachineName column so that it looks like this: (which I have done).
Note that there are many more servers which explains all the NULLS.
They haven't been included for brevity.
CounterDateTime <Server1> <Server2>
2006-06-08 10:43:22.438 NULL 99.8652291105121
2006-06-08 10:43:22.463 NULL NULL
2006-06-08 10:43:23.330 NULL NULL
2006-06-08 10:43:24.388 99.8756991920447 NULL
2006-06-08 10:43:27.791 NULL NULL
2006-06-08 10:44:17.038 NULL NULL
2006-06-08 10:44:22.438 NULL 99.866577718479
2006-06-08 10:44:22.463 NULL NULL
2006-06-08 10:44:23.330 NULL NULL
2006-06-08 10:44:24.403 99.8441760810284 NULL
2006-06-08 10:44:27.791 NULL NULL
2006-06-08 10:45:17.054 NULL NULL
2006-06-08 10:45:22.469 NULL 99.8621640248105
....
As you can see, the time intervals are all over the place and there is
no guarantee that both servers will take a reading at exactly the same
point in time. What I need to do is divide the times into neat steps
like - say - 5 minute intervals and group all the random times into
the five minute slots in which they belong; taking averages where the
'time entries' are grouped. I have no idea how to do this and am
hoping that there is a guru out there up to the challenge.How to pivot a resultset is a pretty commonly requested thing in T-SQL.
So I don't have to rehash it again (OK, I'm lazy...sometimes) I refer
you to Aaron Bertrand's article on the subject on aspfaq.com:
http://www.aspfaq.com/show.asp?id=2462
*mike hodgson*
http://sqlnerd.blogspot.com
togbabe wrote:

>O.K., here is the problem. I am setting up performance monitoring for
>various 2000 and 2003 boxes at my enterprise. I have got Performance
>Monitor recording the data into a sql2000 database. The problem I've
>got is the way that it stores data required me to write TSQL to Pivot
>the data because of the columnar way it is represented by Performance
>monitor. Performance monitor creates three tables called CounterData,
>CounterDetails and DisplayToID. If I do a join between CounterData and
>CounterDetails, on the 'CounterID' column, I can extract the
>following meaningful data:
>CounterDate MachineName CounterValue
>2006-06-07 12:26:14.891 <Server1> 99.8566308243728
>2006-06-07 12:27:14.860 <Server1> 99.8704663212435
>2006-06-07 12:28:14.860 <Server1> 99.8611111111111
>2006-06-07 12:29:14.860 <Server1> 99.8506905561777
>....
>2006-06-08 07:34:21.908 <Server2> 99.7949850181359
>2006-06-08 07:35:21.909 <Server2> 99.7628170041963
>2006-06-08 07:36:21.909 <Server2> 99.7827540106952
>2006-06-08 07:37:21.910 <Server2> 99.7800338409476
>....
>In order to get this data into a linear graph, I have to Pivot the
>MachineName column so that it looks like this: (which I have done).
>Note that there are many more servers which explains all the NULLS.
>They haven't been included for brevity.
>CounterDateTime <Server1> <Server2>
>2006-06-08 10:43:22.438 NULL 99.8652291105121
>2006-06-08 10:43:22.463 NULL NULL
>2006-06-08 10:43:23.330 NULL NULL
>2006-06-08 10:43:24.388 99.8756991920447 NULL
>2006-06-08 10:43:27.791 NULL NULL
>2006-06-08 10:44:17.038 NULL NULL
>2006-06-08 10:44:22.438 NULL 99.866577718479
>2006-06-08 10:44:22.463 NULL NULL
>2006-06-08 10:44:23.330 NULL NULL
>2006-06-08 10:44:24.403 99.8441760810284 NULL
>2006-06-08 10:44:27.791 NULL NULL
>2006-06-08 10:45:17.054 NULL NULL
>2006-06-08 10:45:22.469 NULL 99.8621640248105
>....
>As you can see, the time intervals are all over the place and there is
>no guarantee that both servers will take a reading at exactly the same
>point in time. What I need to do is divide the times into neat steps
>like - say - 5 minute intervals and group all the random times into
>the five minute slots in which they belong; taking averages where the
>'time entries' are grouped. I have no idea how to do this and am
>hoping that there is a guru out there up to the challenge.
>
>|||Mike, I have already pivotted the result set. That is not what I need
to know. In this post I am asking how to group the random time results
to fixed increments. Say every hour over two w

s or ever minute over
a day etc.
Mike Hodgson wrote:
> How to pivot a resultset is a pretty commonly requested thing in T-SQL.
> So I don't have to rehash it again (OK, I'm lazy...sometimes) I refer
> you to Aaron Bertrand's article on the subject on aspfaq.com:
> http://www.aspfaq.com/show.asp?id=2462
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> togbabe wrote:
>
> --080200050206060200040400
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 3519
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>How to pivot a resultset is a pretty commonly requested thing in
> T-SQL. So I don't have to rehash it again (OK, I'm lazy...sometimes)
I
> refer you to Aaron Bertrand's article on the subject on aspfaq.com: 
> <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2462">ttp://www.aspfaq.com/show.asp?id=2462" target="_blank">h
ttp://www.aspfaq.com/show.asp?id=2462</a> 
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> -- 
> mike
> hodgson 
> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a>
> 
> </div>
> 
> 
> togbabe wrote:
> <blockquote
> cite="mid1150258807.128411.20110@.h76g2000cwa.googlegroups.com"
> type="cite">
> <pre wrap="">O.K., here is the problem. I am setting up performance mon
itoring for
> various 2000 and 2003 boxes at my enterprise. I have got Performance
> Monitor recording the data into a sql2000 database. The problem I've
> got is the way that it stores data required me to write TSQL to Pivot
> the data because of the columnar way it is represented by Performance
> monitor. Performance monitor creates three tables called CounterData,
> CounterDetails and DisplayToID. If I do a join between CounterData and
> CounterDetails, on the 'CounterID' column, I can extract the
> following meaningful data:
> CounterDate MachineName CounterValue
> 2006-06-07 12:26:14.891 <Server1> 99.8566308243728
> 2006-06-07 12:27:14.860 <Server1> 99.8704663212435
> 2006-06-07 12:28:14.860 <Server1> 99.8611111111111
> 2006-06-07 12:29:14.860 <Server1> 99.8506905561777
> ....
> 2006-06-08 07:34:21.908 <Server2> 99.7949850181359
> 2006-06-08 07:35:21.909 <Server2> 99.7628170041963
> 2006-06-08 07:36:21.909 <Server2> 99.7827540106952
> 2006-06-08 07:37:21.910 <Server2> 99.7800338409476
> ....
> In order to get this data into a linear graph, I have to Pivot the
> MachineName column so that it looks like this: (which I have done).
> Note that there are many more servers which explains all the NULLS.
> They haven't been included for brevity.
> CounterDateTime <Server1> <Server2>
> 2006-06-08 10:43:22.438 NULL 99.8652291105121
> 2006-06-08 10:43:22.463 NULL NULL
> 2006-06-08 10:43:23.330 NULL NULL
> 2006-06-08 10:43:24.388 99.8756991920447 NULL
> 2006-06-08 10:43:27.791 NULL NULL
> 2006-06-08 10:44:17.038 NULL NULL
> 2006-06-08 10:44:22.438 NULL 99.866577718479
> 2006-06-08 10:44:22.463 NULL NULL
> 2006-06-08 10:44:23.330 NULL NULL
> 2006-06-08 10:44:24.403 99.8441760810284 NULL
> 2006-06-08 10:44:27.791 NULL NULL
> 2006-06-08 10:45:17.054 NULL NULL
> 2006-06-08 10:45:22.469 NULL 99.8621640248105
> ....
> As you can see, the time intervals are all over the place and there is
> no guarantee that both servers will take a reading at exactly the same
> point in time. What I need to do is divide the times into neat steps
> like - say - 5 minute intervals and group all the random times into
> the five minute slots in which they belong; taking averages where the
> 'time entries' are grouped. I have no idea how to do this and am
> hoping that there is a guru out there up to the challenge.
> </pre>
> </blockquote>
> </body>
> </html>
> --080200050206060200040400--|||Oops, sorry (that'll teach me not to read the question properly). OK,
then you just need the grouping column to be stripped of the data you're
not interested in (hours, minutes, seconds, etc.). For example, if you
want hourly averages you can say (untested):
SELECT
DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0) AS HourlyInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0)
If you wanted the intervals to be every minute just change the "hh" in
the datetime functions to "mi" (untested):
SELECT
DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0) AS MinuteInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0)
If you use too small an interval eventually the DATEDIFF() function will
fail with an overflow (it'll try to return a number too big for an int)
so you might want to pick some arbitrary base date instead of 0, like
(untested):
SELECT
DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
AS SecondInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY
DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
togbabe wrote:

>Mike, I have already pivotted the result set. That is not what I need
>to know. In this post I am asking how to group the random time results
>to fixed increments. Say every hour over two w

s or ever minute over
>a day etc.
>Mike Hodgson wrote:
>
>
>|||On Wed, 14 Jun 2006 15:18:20 +1000, Mike Hodgson wrote:
(snip)
>If you wanted the intervals to be every minute just change the "hh" in
>the datetime functions to "mi" (untested):
> SELECT
> DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0) AS MinuteInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0)
Hi Mike,
And to get the 5-minute intervals "togbabe" originally requested, throw
some integer division into the equation:
SELECT
DATEADD(mi,
5 * ((DATEDIFF(mi,0,CounterDateTime) / 5),
0) AS FiveMinuteInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEDIFF(mi,0,CounterDateTime) / 5

>If you use too small an interval eventually the DATEDIFF() function will
>fail with an overflow (it'll try to return a number too big for an int)
>so you might want to pick some arbitrary base date instead of 0, like
>(untested):
> SELECT
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
> AS SecondInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
Recommend format for dates is yyyymmdd, without the dashes. With the
dashes added, it is no longer guaranteed to be unambiguous under all
possible locale settings. (Not that it matters much when both date and
month are 1 anyway <g> ).
Hugo Kornelis, SQL Server MVP|||OK, so I still didn't read the original question
properly...<blush>...well, I'm a busy man. Thanks for the addition Hugo
(and thanks for the comment on my blog, I'll update it as soon as I get
a minute - it was written some time ago and I've learnt a few tricks
since then).
*mike hodgson*
http://sqlnerd.blogspot.com
Hugo Kornelis wrote:

>On Wed, 14 Jun 2006 15:18:20 +1000, Mike Hodgson wrote:
>(snip)
>
>Hi Mike,
>And to get the 5-minute intervals "togbabe" originally requested, throw
>some integer division into the equation:
> SELECT
> DATEADD(mi,
> 5 * ((DATEDIFF(mi,0,CounterDateTime) / 5),
> 0) AS FiveMinuteInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY DATEDIFF(mi,0,CounterDateTime) / 5
>
>
>Recommend format for dates is yyyymmdd, without the dashes. With the
>dashes added, it is no longer guaranteed to be unambiguous under all
>possible locale settings. (Not that it matters much when both date and
>month are 1 anyway <g> ).
>
>|||Thanks mate. This is all good. I have implemented this and it is
working as a general mechanism for displaying my Performance Monitor
data. This is much better than using the performance monitor because
you can take readings for every minute over long periods of time and
change the granularity of the data over longer or shorter periods by
simply changing the 'datepart' argument. You can also archive
extracted data for 'long term trend analysis. Now, this is great and
thanks for that. One thing though, the date functions only allow
intervals of seconds, minutes, hours and days etc. Is there any way
anybody can think of of aggregating across time intervals of 10 minutes
or - say - 5 seconds etc. I believe that this can't be done with
the 'date functions.'
Thanks Mike for you solution.
Mike Hodgson wrote:
> Oops, sorry (that'll teach me not to read the question properly). OK,
> then you just need the grouping column to be stripped of the data you're
> not interested in (hours, minutes, seconds, etc.). For example, if you
> want hourly averages you can say (untested):
> SELECT
> DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0) AS HourlyInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0)
> If you wanted the intervals to be every minute just change the "hh" in
> the datetime functions to "mi" (untested):
> SELECT
> DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0) AS MinuteInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0)
> If you use too small an interval eventually the DATEDIFF() function will
> fail with an overflow (it'll try to return a number too big for an int)
> so you might want to pick some arbitrary base date instead of 0, like
> (untested):
> SELECT
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
> AS SecondInterval,
> AVG(Server1) AS Server1Avg,
> AVG(Server2) as Server2Avg
> FROM MyTable
> GROUP BY
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
>
> Hope this helps.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> togbabe wrote:
>
> --040707020103090502010403
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 10360
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Oops, sorry (that'll teach me not to read the question properly).
;
> OK, then you just need the grouping column to be stripped of the data
> you're not interested in (hours, minutes, seconds, etc.). For exampl
e,
> if you want hourly averages you can say (untested): 
> </tt>
> <blockquote><tt>SELECT</tt> 
> <tt> DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0) AS
> HourlyInterval,</tt> 
> <tt> AVG(Server1) AS Server1Avg,</tt> 
> <tt> AVG(Server2) as Server2Avg</tt> 
> <tt>FROM MyTable</tt> 
> <tt>GROUP BY DATEADD(hh,DATEDIFF(hh,0,CounterDateTime
),0)</tt> 
> </blockquote>
> <tt>If you wanted the intervals to be every minute just change the "hh"
> in the datetime functions to "mi" (untested): 
> </tt>
> <blockquote><tt>SELECT</tt> 
> <tt> DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0) AS
> MinuteInterval,</tt> 
> <tt> AVG(Server1) AS Server1Avg,</tt> 
> <tt> AVG(Server2) as Server2Avg</tt> 
> <tt>FROM MyTable</tt> 
> <tt>GROUP BY DATEADD(mi,DATEDIFF(mi,0,CounterDateTime
),0)</tt> 
> </blockquote>
> <tt>If you use too small an interval eventually the DATEDIFF() function
> will fail with an overflow (it'll try to return a number too big for an
> int) so you might want to pick some arbitrary base date instead of 0,
> like (untested): 
> </tt>
> <blockquote><tt>SELECT</tt> 
> <tt>
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01') AS
> SecondInterval,</tt> 
> <tt> AVG(Server1) AS Server1Avg,</tt> 
> <tt> AVG(Server2) as Server2Avg</tt> 
> <tt>FROM MyTable</tt> 
> <tt>GROUP BY
> DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')</tt> </blockquote>
> <tt> 
> Hope this helps. 
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> -- 
> mike
> hodgson 
> <a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlner
d.blogspot.com</a>
> 
> </div>
> 
> 
> togbabe wrote:
> <blockquote
> cite="mid1150260526.579264.154670@.u72g2000cwu.googlegroups.com"
> type="cite">
> <pre wrap="">Mike, I have already pivotted the result set. That is not
what I need
> to know. In this post I am asking how to group the random time results
> to fixed increments. Say every hour over two w

s or ever minute over
> a day etc.
> Mike Hodgson wrote:
> </pre>
> <blockquote type="cite">
> <pre wrap="">How to pivot a resultset is a pretty commonly requested t
hing in T-SQL.
> So I don't have to rehash it again (OK, I'm lazy...sometimes) I refer
> you to Aaron Bertrand's article on the subject on aspfaq.com:
> <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2462">ttp://www.aspfaq.com/show.asp?id=2462" target="_blank">h
ttp://www.aspfaq.com/show.asp?id=2462</a>
> --
> *mike hodgson*
> <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com">http://sqlne
rd.blogspot.com</a>
>
> togbabe wrote:
> </pre>
> <blockquote type="cite">
> <pre wrap="">O.K., here is the problem. I am setting up performance
monitoring for
> various 2000 and 2003 boxes at my enterprise. I have got Performance
> Monitor recording the data into a sql2000 database. The problem I've
> got is the way that it stores data required me to write TSQL to Pivot
> the data because of the columnar way it is represented by Performance
> monitor. Performance monitor creates three tables called CounterData,
> CounterDetails and DisplayToID. If I do a join between CounterData and
> CounterDetails, on the 'CounterID' column, I can extract the
> following meaningful data:
> CounterDate MachineName CounterValue
> 2006-06-07 12:26:14.891 <Server1> 99.8566308243728
> 2006-06-07 12:27:14.860 <Server1> 99.8704663212435
> 2006-06-07 12:28:14.860 <Server1> 99.8611111111111
> 2006-06-07 12:29:14.860 <Server1> 99.8506905561777
> ....
> 2006-06-08 07:34:21.908 <Server2> 99.7949850181359
> 2006-06-08 07:35:21.909 <Server2> 99.7628170041963
> 2006-06-08 07:36:21.909 <Server2> 99.7827540106952
> 2006-06-08 07:37:21.910 <Server2> 99.7800338409476
> ....
> In order to get this data into a linear graph, I have to Pivot the
> MachineName column so that it looks like this: (which I have done).
> Note that there are many more servers which explains all the NULLS.
> They haven't been included for brevity.
> CounterDateTime <Server1> <Server2>
> 2006-06-08 10:43:22.438 NULL 99.8652291105121
> 2006-06-08 10:43:22.463 NULL NULL
> 2006-06-08 10:43:23.330 NULL NULL
> 2006-06-08 10:43:24.388 99.8756991920447 NULL
> 2006-06-08 10:43:27.791 NULL NULL
> 2006-06-08 10:44:17.038 NULL NULL
> 2006-06-08 10:44:22.438 NULL 99.866577718479
> 2006-06-08 10:44:22.463 NULL NULL
> 2006-06-08 10:44:23.330 NULL NULL
> 2006-06-08 10:44:24.403 99.8441760810284 NULL
> 2006-06-08 10:44:27.791 NULL NULL
> 2006-06-08 10:45:17.054 NULL NULL
> 2006-06-08 10:45:22.469 NULL 99.8621640248105
> ....
> As you can see, the time intervals are all over the place and there is
> no guarantee that both servers will take a reading at exactly the same
> point in time. What I need to do is divide the times into neat steps
> like - say - 5 minute intervals and group all the random times into
> the five minute slots in which they belong; taking averages where the
> 'time entries' are grouped. I have no idea how to do this and am
> hoping that there is a guru out there up to the challenge.
>
> </pre>
> </blockquote>
> <pre wrap="">--080200050206060200040400
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 3519
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type
">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>How to pivot a resultset is a pretty commonly requested thing in
> T-SQL. So I don't have to rehash it again (OK, I'm lazy...someti
mes) I
> refer you to Aaron Bertrand's article on the subject on aspfaq.com:<br&
gt;
> <a class="moz-txt-link-freetext" href=<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=ht
tp://www.aspfaq.com/show.asp?id=2462">"http://www.aspfaq.com/show.asp?id=2462"</a>&g
t;<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2462">htt
p:/
/www.aspfaq.com/show.asp?id=2462</a></a> 
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> --<
;br>
> mike
> hodgson <a href=<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=http://s
qlnerd.blogspot.com">"http://sqlnerd.blogspot.com"</a>><a class="moz-txt-link-freetext" href
="http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a><
;/a>
> 
> </div>
> 
> 
> togbabe wrote:
> <blockquote
> cite=<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mid1150258807.128411.2
0110@.h76g2000cwa.googlegroups.com">"mid1150258807.128411.20110@.h76g2000cwa.g
ooglegroups.com"</a>
> type="cite">
> <pre wrap="">O.K., here is the problem. I am setting up performan
ce monitoring for
> various 2000 and 2003 boxes at my enterprise. I have got Performance
> Monitor recording the data into a sql2000 database. The problem I've
> got is the way that it stores data required me to write TSQL to Pivot
> the data because of the columnar way it is represented by Performance
> monitor. Performance monitor creates three tables called CounterData,
> CounterDetails and DisplayToID. If I do a join between CounterData and
> CounterDetails, on the 'CounterID' column, I can extract the
> following meaningful data:
> CounterDate MachineName CounterValue
> 2006-06-07 12:26:14.891 <Server1> 99.8566308243728
> 2006-06-07 12:27:14.860 <Server1> 99.8704663212435
> 2006-06-07 12:28:14.860 <Server1> 99.8611111111111
> 2006-06-07 12:29:14.860 <Server1> 99.8506905561777
> ....
> 2006-06-08 07:34:21.908 <Server2> 99.7949850181359
> 2006-06-08 07:35:21.909 <Server2> 99.7628170041963
> 2006-06-08 07:36:21.909 <Server2> 99.7827540106952
> 2006-06-08 07:37:21.910 <Server2> 99.7800338409476
> ....
> In order to get this data into a linear graph, I have to Pivot the
> MachineName column so that it looks like this: (which I have done).
> Note that there are many more servers which explains all the NULLS.
> They haven't been included for brevity.
> CounterDateTime <Server1> <Server2>
> 2006-06-08 10:43:22.438 NULL 99.8652291105121
> 2006-06-08 10:43:22.463 NULL NULL
> 2006-06-08 10:43:23.330 NULL NULL
> 2006-06-08 10:43:24.388 99.8756991920447 NULL
> 2006-06-08 10:43:27.791 NULL NULL
> 2006-06-08 10:44:17.038 NULL NULL
> 2006-06-08 10:44:22.438 NULL 99.866577718479
> 2006-06-08 10:44:22.463 NULL NULL
> 2006-06-08 10:44:23.330 NULL NULL
> 2006-06-08 10:44:24.403 99.8441760810284 NULL
> 2006-06-08 10:44:27.791 NULL NULL
> 2006-06-08 10:45:17.054 NULL NULL
> 2006-06-08 10:45:22.469 NULL 99.8621640248105
> ....
> As you can see, the time intervals are all over the place and there is
> no guarantee that both servers will take a reading at exactly the same
> point in time. What I need to do is divide the times into neat steps
> like - say - 5 minute intervals and group all the random times into
> the five minute slots in which they belong; taking averages where the
> 'time entries' are grouped. I have no idea how to do this and am
> hoping that there is a guru out there up to the challenge.
> </pre>
> </blockquote>
> </body>
> </html>
> --080200050206060200040400--
> </pre>
> </blockquote>
> <pre wrap=""><!-->
> </pre>
> </blockquote>
> </body>
> </html>
> --040707020103090502010403--|||Yep - Hugo answered that in his post in this thread:
<quote>
And to get the 5-minute intervals "togbabe" originally requested, throw
some integer division into the equation:
SELECT
DATEADD(mi,
5 * ((DATEDIFF(mi,0,CounterDateTime) / 5),
0) AS FiveMinuteInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEDIFF(mi,0,CounterDateTime) / 5
</quote>
*mike hodgson*
http://sqlnerd.blogspot.com
togbabe wrote:

>Thanks mate. This is all good. I have implemented this and it is
>working as a general mechanism for displaying my Performance Monitor
>data. This is much better than using the performance monitor because
>you can take readings for every minute over long periods of time and
>change the granularity of the data over longer or shorter periods by
>simply changing the 'datepart' argument. You can also archive
>extracted data for 'long term trend analysis. Now, this is great and
>thanks for that. One thing though, the date functions only allow
>intervals of seconds, minutes, hours and days etc. Is there any way
>anybody can think of of aggregating across time intervals of 10 minutes
>or - say - 5 seconds etc. I believe that this can't be done with
>the 'date functions.'
>Thanks Mike for you solution.
>Mike Hodgson wrote:
>
/www.aspfaq.com/show.asp?id=2462</a></a> 
;/a>
>
>

Wednesday, March 28, 2012

Grouping performance monitor time data after pivoting help please

No comments:

Post a Comment

Firehose mode

Blog Archive

About Me