Extacting Time Series with MSSQL

Validate the speeds and feeds I have used by examining the rate at which records are written to the database. For example, looking at the Weigh_Event_Log table, I construct the following query

  select  convert(varchar, DateTime, 112), count(1)
    from  dbo.Event_Log
   where  DateTime > getdate() - 360
group by  convert(varchar, DateTime, 112)
order by  convert(varchar, DateTime, 112) desc

Note - see http://msdn.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx for conversion Style Codes

And it produces the following output

Date      # Log Entries
20110921    24026
20110920    32063
20110919    27166
20110918    12946
20110917     5588
20110916    26410
20110915    32061
20110914    31566
20110913    33670
20110912    27451
...
20110725    25688
20110724    11696
20110723     5884

This shows that an upper limit for a day is around 32,000. So pick one of those days - 20110920 - and do a further drill down using the following query:

  select  convert(varchar, DateTime, 100), count(1)
    from  dbo.Event_Log
   where  Date_Time >= '2011-09-20'
     and  Date_Time <  '2011-09-21'
group by  convert(varchar, DateTime, 100)
order by  convert(varchar, DateTime, 100) desc

With some simple data manipulation and a bit of help from Excel I get the following minute by minute distribution graph - which incidentally does indeed support the thesis that the peak rate is a bit over one per second (up to 75 per minute).

In this way create a picture of processing rate of events.