July 29, 2011 at 1:00 pm
Hi..
we have 4 instances running on our sql server 2005 enterprise edition. from past couple of weeks we are getting alerts that CPU Consumption on this box is 60%.
how do i query/check each sql instance is consuming CPU and how much?
Thanks in advance.
July 29, 2011 at 1:04 pm
This should get you started :
August 1, 2011 at 8:24 am
recently i had a server with a CPU issue. get SSMS 2008 R2 and use the activity monitor and check out the most expensive queries it reports back to you
August 2, 2011 at 6:53 am
Here's a nice one I use (can't recall where I found it). It'll give you about 2.5 hours worth of history in one minute increments. Run it on each instance and you can find the problem child. Each instance will report it's own use as SQL CPU and everything else as non-SQL. If you add @@servername to the select and put the results into a table somewhere you can actually graph out the results pretty nicely.
set nocount on
DECLARE @ts_now bigint
set @ts_now = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
selectdistinct b.*
FROMsys.dm_os_sys_info a
inner join(
SELECT TOP(150) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC
) b on 1=1
August 2, 2011 at 8:02 am
WOW that's nice :w00t:.
August 2, 2011 at 8:14 am
Ninja's_RGR'us (8/2/2011)
WOW that's nice :w00t:.
Wish I could claim it as my own. 😎 And wish I could remember where I found it - the guy who did write it deserves the credit. I created an SSIS/SSRS combo that give me some really nice stats. It's all DB driven so when I add a new server I just add it to a table and the stats start collecting. I work mostly on contract and have implemented it in a few places now. It gives me the big picture on everything from wait stats to disk space (down to file size over time), backups, and Agent job failures.
Ah. Glen Berry[/url]. Of course! I used a bunch of his queries in my performance monitoring tool.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply