SQL Server: query for getting TOP X groups of records which have highest subsequent values

Question

I'm querying each SQL Server DB CPU usage (based on query from here -- Get CPU utilization by database (Query 29) (CPU Usage by Database)) every 60 seconds and storing this information in dedicated table for that purpose with columns:

id, dbname, date, usage

Now I'm interested in querying this DB for databases which are using most CPU usage but only if DB usage X subsequent reads where higher than Y (so I'm not interested in DB which had CPU usage spikes).

Example:

DB1:

time1 - 0  
time2 - 35  
time3 - 0  
time4 - 1 

DB2:

time1 - 9  
time2 - 8  
time3 - 7  
time4 - 11

In this example I'm not interested in DB1 because it had only one CPU spike and it's average CPU usage is low. DB2 on the other hand has average higher CPU usage in subsequent reads and this type of DB I'm trying to find in my usage table.

Thank you

EDIT: My mistake. Version 2012

Answer 1

If you run this:

select dbname, AVG(usage), STDEV(usage) from <your table> group by dbname

You'll notice that while the average is the same for both databases, the one you'd be interested is the one where the standard deviation is lower. So what you'd want to do is then filter this by a certain threshold average usage or higher and a certain standard deviation or lower.