SQL: calculating system load statistics
I have a table like this that stores messages coming in through the system:
Message
-------
ID (bigint)
CreateDate (datetime)
Data (varchar(255))
I was asked to calculate the messages stored per second at maximum load. The only data I really have to work with is CreateDate. The load on the system is not constant, there are times when we receive a ton of traffic and a time when we receive little traffic. I think there are two parts to this problem: 1. Determine the time ranges that are considered peak load, 2. Calculate the average messages per second over these times.
Is this the correct approach? Are there things in SQL that can help with this? Any advice would be greatly appreciated.
a source to share
I don't think you need to know peak hours; you can generate them with SQL by wrapping a full query and selecting the top 20 records, for example:
select top 20 *
from (
[...load query here...]
) qry
order by LoadPerSecond desc
This answer had a good lesson about averages. You can calculate load per second by looking at load per hour and dividing it by 3600.
To get a first look at last week's download, you can try (Sql Server syntax):
select datepart(dy,createdate) as DayOfYear,
hour(createdate) as Hour,
count(*)/3600.0 as LoadPerSecond
from message
where CreateDate > dateadd(week,-7,getdate())
group by datepart(dy,createdate), hour(createdate)
To find the maximum load per minute:
select max(MessagesPerMinute)
from (
select count(*) as MessagesPerMinute
from message
where CreateDate > dateadd(days,-7,getdate())
group by datepart(dy,createdate),hour(createdate),minute(createdate)
)
Grouping by datepart (dy, ...) is an easy way to distinguish between days without worrying about month boundaries. It works until you select more than a year ago, but that would be unusual for performance queries.
a source to share
I agree, you need to figure out what Peak Load is before you can create reports on it.
The first thing I would like to do is figure out how I will determine the maximum load. Ex. I'm going to look at the hourly breakdown.
Then I would make a group with CreateDate, created in seconds (no milliseconds). As part of a group, I would make an avg based on the number of entries.
a source to share
they will run slowly!
this will group your data into "second" buckets and list it from the activity itself at least:
SELECT
CONVERT(char(19),CreateDate,120) AS CreateDateBucket,COUNT(*) AS CountOf
FROM Message
GROUP BY CONVERT(Char(19),CreateDate,120)
ORDER BY 2 Desc
this will group your data into "minute" buckets and list it with the most activity:
SELECT
LEFT(CONVERT(char(19),CreateDate,120),16) AS CreateDateBucket,COUNT(*) AS CountOf
FROM Message
GROUP BY LEFT(CONVERT(char(19),CreateDate,120),16)
ORDER BY 2 Desc
I would take these values and figure out what they want
a source to share