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
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!