Post reply

100 % cpu

  • August 30, 2010 at 4:55 am

    #225334

    Hi All,

    in one of my BCP boxes , i often get 100 % CPU utilization ticket, i check all the job's running and set a trace flag also but i not able to find perfect cause, can anyone guide me in finding the actual cause for this, and different ways i can handle thsi, thanks in advance.

  • August 30, 2010 at 5:10 am

    #1213663

    If I guess correctly you are facing this problem after perfroming the BCP. I suggest you rebuild the indexes of respective table after doing BCP.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • August 30, 2010 at 7:47 am

    #1213722

    It may or may not be indexes, who knows based on the information at hand. What I'd suggest is taking a look at sys.dm_exec_requests and see if there are processes that are waiting or blocked. If they are waiting on a resource, drill down and determine exactly what's causing the slow down. You can also look at sys.dm_os_wait_stats to get cumulative wait times to determine what things are causing slow downs on the system over all.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • August 30, 2010 at 8:44 am

    #1213769

    Check Whether the Server is experiencing any excessive Comilations and recompilations\sec and also see whether you are getting any CX_PACKET waits which indicates parallelism. Also check for any memory pressures and paging activity which may also result in CPU presure.

    Thank You,

    Best Regards,

    SQLBuddy

  • August 30, 2010 at 12:26 pm

    #1213888

    Someone just touched on it...check your max degree of parallelism, and then check how many processes are waiting on CXPACKET. If you have a ton of cxpacket waits, lower your MAXDOP.

  • September 1, 2010 at 3:00 am

    #1214791

    Among other things mentioned in this post this may help: http://www.sql-server-performance.com/tips/performance_monitor_cpu_counter_p1.aspx

    Cheers,

    Carlton.

Viewing 6 posts - 1 through 5 (of 5 total)

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