Post reply

How to see CPU Consumption by each sql instance?

  • July 29, 2011 at 1:00 pm

    #254494

    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.

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • July 29, 2011 at 1:04 pm

    #1361050

    This should get you started :

    http://msdn.microsoft.com/en-us/library/ms187743.aspx

  • August 1, 2011 at 8:24 am

    #1361574

    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

    #1362112

    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);

    select distinct b.*

    FROM sys.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

    #1362173

    WOW that's nice :w00t:.

  • August 2, 2011 at 8:14 am

    #1362187

    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