PeterHarding: Created page with ' 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...'

2011-11-07T22:01:23Z

Created page with ' 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...'

New page

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

<pre>
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
</pre>

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

<pre>
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
</pre>

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:

<pre>
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
</pre>

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.

[[Category:Performance]]

[[Category:SqlServer]]

Extacting Time Series with MSSQL - Revision history

PeterHarding: Created page with ' 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...'