0

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

  • Which version of SQL Server? – Mark Sinkinson Apr 17 '15 at 11:17
  • Hello, sorry I've forgot about that. I've updated my question. Version 2012 – GrZeCh Apr 17 '15 at 11:22
  • Why not just use the AVE() function along with STDEV()? The average on the two datasets would yield the same average but Standard deviation will expose the first data set as a result of a one-off event. – Queue Mann Apr 17 '15 at 12:30
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.

  • Thank you. I'm no DBA so I didn't know this function. – GrZeCh Apr 20 '15 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.