I’ve read many posts on the tricky subject of CPU, so apologies if this seems like a re-hash. I’m not looking to capture CPU time, though if there’s a way to convert this to a percentage, I’d be interested to know. I’m hoping if I lay out my thought process some bright spark out there will help me connect the dots.

I have some software monitoring CPU at a host level. The host machine has two SQL Server 2008 R2 instances, each with hundreds of databases. I’d like to create a process so that I know which databases are utilising the CPU at any given time. I’d then like to implement Resource Governor to have greater control over the CPU utilisation of our Reporting databases on these instances, which unfortunately sit side-by-side with other Production databases. This post is not about Resource Governor directly and I mention it only as explanation as to what has driven my thought process.

Using sys.dm_exec_query_stats, I can work out the percentage of CPU utilisation of each database…compared to all other databases. I can persist this periodically, but this does not give me the percentage at the instance/server level.

As we all know, Windows Task Manager gives a nice, understandable percentage value for CPU utilisation for each SQL Server instance on the server. I thought that if I could capture this info using T-SQL or Powershell, I could persist the data and combine it with the info captured from sys.dm_exec_query_stats and extrapolate the correct percentage each database uses at the instance/server level. But how can I access instance level CPU as a percentage from SQL Server and store the values? Does anyone have a (most likely Powershell) script?

Another way I thought to capture CPU utilisation at an instance level would be through the performance counters ‘SQLServer:Workload Group Stats’ and ‘SQLServer:Resource Pool Stats’. Given I currently do not have Resource Governor implemented, these should show CPU utilisation for the instance, as everything will pass through the ‘default’ group. But I’m finding it hard to trust the ‘CPU usage %’ result. What exactly is this a percentage of, if there are multiple instances on a host with multiple processors? This method would suit me well due to the fact, as previously mentioned, I aim to implement Resource Governor and this would help me record a baseline to inform my decisions on MIN/MAX values for my pools.

Finally, there is a fairly well documented query that looks at RING_BUFFER_SCHEDULER_MONITOR to provide the SQL ProcessUtilization, but again, what is this a percentage of?

I’m sure plenty of people must have faced similar situations and if anyone has any ideas or advice, I’d be very grateful