Post reply

High CPU

  • November 15, 2009 at 3:22 pm

    #214091

    my server has very high CPU (between 90 - 100%) when certain processes run. However, if i profile what is going on with the server, SQL seems to be responding to all the sprocs being called in less than second. there is no blocking going on. what else can i look at that would help identify why the CPU is so high?

  • November 15, 2009 at 11:41 pm

    #1079511

    Start with TaskMgr and make sure what's consuming the CPU from the 60,000 ft level, first.

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

  • November 16, 2009 at 2:35 am

    #1079568

    Are those processes related to SQL server or some other processes.

    "Keep Trying"

  • November 16, 2009 at 5:07 am

    #1079604

    yes, task manager reports that sqlservr.exe is using 99% of the CPU.

  • November 16, 2009 at 5:22 am

    #1079607

    post the spec of your server.

    you already seem to know which processes are causing the high cpu, so investigate the SQL behind them, and check out the health of the tables they work against.

    This SQL identifies costliest queries by CPU

    SELECT TOP 10

    [Average CPU used] = total_worker_time / qs.execution_count

    ,[Total CPU used] = total_worker_time

    ,[Execution count] = qs.execution_count

    ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

    (CASE WHEN qs.statement_end_offset = -1

    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

    ELSE qs.statement_end_offset END -

    qs.statement_start_offset)/2)

    ,[Parent Query] = qt.text

    ,DatabaseName = DB_NAME(qt.dbid)

    FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

    ORDER BY [Average CPU used] DESC;

    ---------------------------------------------------------------------

  • November 16, 2009 at 10:44 am

    #1079797

    bell282 (11/15/2009)

    my server has very high CPU (between 90 - 100%) when certain processes run. However, if i profile what is going on with the server, SQL seems to be responding to all the sprocs being called in less than second. there is no blocking going on. what else can i look at that would help identify why the CPU is so high?

    sql server is capable of handling LOTS of requests in very quick succession. You are not seeing blocking, and no one is complaining of poor performance. Not sure what the problem is here. It could just be a busy server, right? now, I WILL note that if you are like every single entity I have come across there is probably a LOT that can be done with your server/schema/configuration/application/database to make things MORE efficient!! 😎

    some common culprits of higher-than-necessary CPU: extended sprocs, CLR, cursors, UDFs, table variables, out-of-date statistics, missing indexes

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • November 16, 2009 at 5:10 pm

    #1079977

    ... and queries that try to do it all in a single statement... 🙂

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

  • November 16, 2009 at 5:21 pm

    #1079978

    In an imaginary system with zero I/O latency (to disk or memory!) the ultimate capacity would probably be limited by CPU. Maybe this system has reached that state :laugh:

    Seriously, though: I would add inappropriate parallelism to the list of potential culprits, together with forced loop joins (often seen together).

    George's query is an excellent place to start. I would also check the signal_wait_time_ms column of sys.dm_os_wait_stats just to see whether runnable tasks are in fact already waiting for a scheduler.

    Other fancy stuff can be done with ring buffers if you find that sort of thing interesting (I do): http://blogs.msdn.com/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 18, 2009 at 9:13 am

    #1080983

    well, made some progress.. We have a table with about 300,000 records (and one of the columns is an image)... If we delete all the records out and re-run the process it completes in about 2 minutes (previously about 30 minutes)..

    If i trace what is going on i see all the sprocs returning immed. (within .1 secodns!) and i don't see much network activity..

    Any ideas why this table would cause any issues?

  • November 23, 2009 at 2:38 am

    #1082846

    bell282 (11/18/2009)

    (and one of the columns is an image)... If we delete all the records out and re-run the process it completes in about 2 minutes (previously about 30 minutes)..

    Any ideas why this table would cause any issues?

    find the query that is used on this (and perhaps other) table(s) and do a "estimated execution plan" on it. check for things like table scan. if sql is doing a table scan and your image looks like a "normal" coloumn to sql server it has to read too much data to satisfy your query.

  • November 23, 2009 at 8:03 am

    #1082948

    bell282 (11/18/2009)

    well, made some progress.. We have a table with about 300,000 records (and one of the columns is an image)... If we delete all the records out and re-run the process it completes in about 2 minutes (previously about 30 minutes)..

    If i trace what is going on i see all the sprocs returning immed. (within .1 secodns!) and i don't see much network activity..

    Any ideas why this table would cause any issues?

    Has this been resolved now? You posted this follow-up on 11/18, 3 days after the original post. Just wondering if you have solved your high-CPU concern...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 11 posts - 1 through 10 (of 10 total)

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