Querying the RedGateMonitor database for CPU utlization

pdxdba

I am relatively new to Monitor having used it for only a couple months. I have an IT executive who is looking for historical data, mainly the number of logins to our site versus our SQL Server CPU utilization (8 CPUs), bucketed by different timeframes. (per minute, 15 minutes ...)

So, of course I have the login data and am needing to get the CPU utilization from the RedGateMonitor database.

I've tried this query, but when I look at the server's CPU or compare the results from the Analysis tab, they do not match up.

SELECT
[Cluster_Machine_Processors_CumulativeIdleTime] CurrTime
,LAG([Cluster_Machine_Processors_CumulativeIdleTime]) OVER (ORDER BY [CollectionDate_DateTime]) PrevTime -- compare time from previous reading to the next
,[CollectionDate_DateTime]
into #CPUStuff
FROM [RedGateMonitor].[data].[Cluster_Machine_Processors_UnstableSamples_View]
WHERE [Cluster_Machine_Name] = 'MySQLServer'
AND [CollectionDate_DateTime] > '08/01/2016'

select [CollectionDate_DateTime]
, CurrTime
, PrevTime
, 1.0-(CurrTime - PrevTime) / 1000000000.0
from #CPUStuff

drop table #CPUStuff

Can anyone point me in the right direction?

Thanks,

Alex B

Hi pdxdba,

Is there a reason you are not getting all of these metrics on the Analysis graph within SQL Monitor and then sending the url to your IT exec for the specified timeframes?

If you go to the Analysis tab, then choose "User connections" under SQL Server metrics in the first option box for the appropriate machine/instance and then click the blue Add another metric button and choose "SQL Server: processor time" again under the SQL Server metrics heading in the first option box (ensuring "(As above)" is selected so you have the same server/instance for these metrics). This will display both of the metrics you are looking for I believe and then you can just specify the time frames you need using the "Time range" control at the top. You can then copy the URL for each time frame and send that and the executive will be able to see the graphs just as you have set them up. They will need to be able to log into SQL Monitor to view them, but this seems an easier way to do it unless I'm missing something.

Unfortunately we don't support direct querying of the database (and I haven't looked into trying this previously to know what's going on).

I hope this helps!

Kind regards,
Alex