June 14, 2012 at 1:03 pm
Can someone please share with me a query to calculate CPU usage on one particular db? I would like to run this every hour and save the data in a table. I plan to run this on a sql 2005 server with 24 cores. We have MAXDOP set to 1 at server level ,however we have many queries using query hints with maxdop > 1.
June 14, 2012 at 11:03 pm
This may help answer your question:
http://stackoverflow.com/questions/28952/cpu-utilization-by-database
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 15, 2012 at 4:57 am
Brent's suggestion to use the DMO queries is the closest you can get. Just remember that you're querying against the cache. That means that you can miss information if it ages out of cache. There are just lots of reasons why this type of measure is difficult to come by.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2012 at 8:52 am
opc.three (6/14/2012)
This may help answer your question:http://stackoverflow.com/questions/28952/cpu-utilization-by-database
Thanks. Does it matter what is my mAXDOP?
June 15, 2012 at 8:58 am
sqldba_newbie (6/15/2012)
opc.three (6/14/2012)
This may help answer your question:http://stackoverflow.com/questions/28952/cpu-utilization-by-database
Thanks. Does it matter what is my mAXDOP?
No. In Brent's query, what you're calculating is total elapsed time. The results will not tell you what amount of that cpu time emanated from parallel queries.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 27, 2015 at 5:27 am
Orlando Colamatteo (6/14/2012)
This may help answer your question:http://stackoverflow.com/questions/28952/cpu-utilization-by-database
This will get the usage per each sql, what if i just want the CPU usage on the server, any SQL to get that?
November 27, 2015 at 6:44 am
what is your use case?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 27, 2015 at 3:31 pm
i just need to measure the cpu usage across different times during the day to get peak hours and see how much cpu goes up at those times.
November 27, 2015 at 3:41 pm
Use PowerShell and WMI.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 28, 2015 at 12:09 pm
Orlando Colamatteo (11/27/2015)
Use PowerShell and WMI.
Hi Orlando,
Can you please tell me what the following query does?
SELECT
@CPUusage=(cast(CAST (A.cntr_value1 AS NUMERIC) / CAST (B.cntr_value2 AS NUMERIC) as decimal(18,3)))*100 ,@CPUusagebase=cntr_value2
FROM
(
SELECT cntr_value AS cntr_value1
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage %'
) AS A
,
(
SELECT cntr_value AS cntr_value2
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Resource Pool Stats'
and counter_name = 'CPU usage % base'
) AS B
Thanks
Nader
November 28, 2015 at 2:40 pm
Monitoring multiple instances implies you need something that can reach out over a network and compile and display results from multiple servers. PowerShell is a good choice for something like that. I would use WMI to monitor CPU but if those DMVs work for you go for it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 28, 2015 at 11:45 pm
Orlando Colamatteo (11/28/2015)
Monitoring multiple instances implies you need something that can reach out over a network and compile and display results from multiple servers. PowerShell is a good choice for something like that. I would use WMI to monitor CPU but if those DMVs work for you go for it.
I just need to monitor one instance, with regards to the DMVs they work on my server but on client server gives division by zero error although we are both the same SQL version(2012)
November 29, 2015 at 8:14 am
PowerShell might not make sense if you'll only ever need to worry about one instance. If it's just peak usage you want then PerfMon is a good choice. With a few clicks you can setup a PerfMon Log with the CPU counters you want and end up with a delimited log file you can analyze. You can then pull that log into Excel and show it as a chart. If you use the DMV method you have to implement all the polling, collecting and scheduling stuff yourself.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 29, 2015 at 11:53 pm
thank you very much for your help.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply