SQL SERVER CPU Usage 100 % even at no traffic time

  • it is charging few dollars, i have to take approvals from client.

  • purushottam2 (1/28/2013)


    it is charging few dollars, i have to take approvals from client.

    I take it you missed the "Get your free eBook download here." link on the webpage then?

  • anthony.green (1/28/2013)


    purushottam2 (1/28/2013)


    it is charging few dollars, i have to take approvals from client.

    I take it you missed the "Get your free eBook download here." link on the webpage then?

    and even if it wasn't free (which it is), why would you have to get approval from a client to buy a book for your career?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (1/28/2013)


    anthony.green (1/28/2013)


    purushottam2 (1/28/2013)


    it is charging few dollars, i have to take approvals from client.

    I take it you missed the "Get your free eBook download here." link on the webpage then?

    and even if it wasn't free (which it is), why would you have to get approval from a client to buy a book for your career?

    Very true and with something as so useful as what is covered in the book, it is worth the $29.99, I would argue its worth more than that, the amount of times I have forgotten something small and this re-jogs the memory, certainly has saved me a few times.

  • Great advice (ebook)...

    But first, just be absolutely sure your cpu isn't being consumed by the os before deep diving into wait stats, etc. I know you said this was not the case, but how did you check cpu? Here's a direct cut & paste from Glen Berry's diagnostic checks. This one gives you cpu history split out by SQL/non-SQL usage:

    -- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 25)

    -- This version works with SQL Server 2008 and SQL Server 2008 R2 only

    DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

    SELECT TOP(256) 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 WITH (NOLOCK)

    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

    AND record LIKE N'%<SystemHealth>%') AS x

    ) AS y

    ORDER BY record_id DESC OPTION (RECOMPILE);

  • I have checked by Query, And also at time when our IIS server was stop CPU usage was 0 %, if we start IIS server and user accessing application it leads to 100%.

  • Then between the web server and the user app, they're sending enough stuff to SQL to run the CPU up to 100%.

    Check out that book chapter I recommended. It's all about high CPU usage.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • purushottam2 (1/28/2013)


    it is charging few dollars, i have to take approvals from client.

    You're seriously suggesting that it's not worth it for you to invest a few bucks in your own education? Are you making that little from your clients?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • purushottam2 (1/28/2013)


    I have checked by Query, And also at time when our IIS server was stop CPU usage was 0 %, if we start IIS server and user accessing application it leads to 100%.

    What happens if you start the IIS Server but don't let any users in? I have seen bad installations of IIS Server drive SQL Server absolutely nuts and that would be the proof you need.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Once user start accessing application then only it lead to 100% cpu consumption.

  • purushottam2 (1/28/2013)


    Once user start accessing application then only it lead to 100% cpu consumption.

    then sql profiler trace might help you , set the trace and try to catch the queries which are spiking the CPU.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (1/28/2013)


    purushottam2 (1/28/2013)


    Once user start accessing application then only it lead to 100% cpu consumption.

    then sql profiler trace might help you , set the trace and try to catch the queries which are spiking the CPU.

    It might actually be easier than that. From SSMS, open the object explorer {f8 key}. Right click on the server instance name in object explorer and select {Reports}{Standard Reports}{Performance - Top Queries by Total CPU Time}. It takes you right down to the actual query in a stored procedure or ad hoc code and will even help you find very quick queries that are used a whole lot of times per second.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply