Post reply

CPU Utilization is 100%

  • October 21, 2011 at 5:37 am

    #259144

    HI Team,

    I am working with SQl server 2005 standard edition 64 bit sp4 in windows server 2008 R2 64 bit [8GB RAM] .

    I installed the sql server and i restored all databases with production backup .

    I checked with SQL server the AWE is not enabled in SQl server .

    I am running a perfmon its showing the below values ::

    Available MB Bytes : 190 [Constantly ]

    Target serevr memory : arround 2GB

    Total Server memory : arround 1.9 GB

    when every the application is connecting to DATABASE , the cpu utilization is goes to 100 %.

    I changed the SQL server memory configuration to

    MAx memory : 6GB

    Min meory is : 0

    1. IS it compulsory to enable the AWE ?

    2. if any changes i need to do from DB side ??

    3. If i need to add extra 8 GB Ram ??

    4. Any suggestion from u r side ??

    Thanks,

    Lavanya

  • October 21, 2011 at 5:48 am

    #1397878

    Hi

    I think for 64 bit no need to enable AWE.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • October 21, 2011 at 5:50 am

    #1397882

    Is auto_close or auto_shrink on?

  • October 21, 2011 at 5:53 am

    #1397886

    How big the DB?

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • October 21, 2011 at 5:55 am

    #1397888

    Have you seen the CUP 100% always?

    How about fragmentation?

    There is lots of thing out there the CPU went high % run a server side trace and find the things. Also check the waitstats.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • October 21, 2011 at 6:11 am

    #1397903

    Optimise your queries, tune your indexes. High CPU utilisation is most commonly from badly performing code.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    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

  • October 21, 2011 at 6:40 am

    #1397929

    HI ,

    The Auto_close and auto_shrink is false ..

    The MAx db size is 30 GB .

    Thanks

    Lavanya

  • October 21, 2011 at 6:43 am

    #1397931

    HI ,

    How to find memory bottle neck [ extra RAM required ] using perfmon or any other way??

  • October 21, 2011 at 6:48 am

    #1397932

    Lavanyasri (10/21/2011)

    HI ,

    How to find memory bottle neck [ extra RAM required ] using perfmon or any other way??

    Start with Gails article. That was my next step if those options were off.

    Most likely something autoruns on your app startup and it overloads the server.

  • October 21, 2011 at 7:21 am

    #1397951

    Lavanyasri (10/21/2011)

    HI ,

    How to find memory bottle neck [ extra RAM required ] using perfmon or any other way??

    You have asked something related about memory as your first post itself. Is there any specific reason?

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • October 24, 2011 at 4:40 am

    #1398585

    HI Muthukkumaran K

    Thanks for the Quick response .

    You have asked something related about memory as your first post itself. Is there any specific reason?

    Yes , I created a new DEV environment for my production server. [Exact same as a production] Including all databases and jobs etc.

    In production we have 16 Gb Ram but in Dev its only 8 Gb RAM , Remaing all are same [windows,SQl server editions] .

    In Dev its showing only 190 Mb Bytes are available for Available Mbbytes counter [perfmon] .

    Thanks ,

    Lavanya

  • October 24, 2011 at 4:45 am

    #1398593

    Is lock pages in memory on? That will use everything available right on startup (up to the limit you set for the server).

  • October 24, 2011 at 4:50 am

    #1398595

    Ninja's_RGR'us (10/24/2011)

    Is lock pages in memory on? That will use everything available right on startup (up to the limit you set for the server).

    No matter the locked pages setting, SQL will not automatically allocate min server memory or max server memory. It's a ramp-up.

    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

  • October 25, 2011 at 7:08 am

    #1399086

    You can use dbcc memorystatus (officially documented online) to get detailed information about memory use in SQL Server.

    It is quite exceptional for SQL Server to be CPU bound instead of IO bound. In addition to other suggestions, do you have UDFs in your code? They have been responsible for most of the VERY few times in 15 years of dealing with SQL Server that I have come across a client with a CPU bound machine (that wasn't truly underpowered). Lots of cursors could cause the same thing.

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

  • October 26, 2011 at 3:35 am

    #1399462

    HI Kevin,

    Thanks for the update .

    I think u r right , it might a problem with Cursors. I ran a profiler with rpc completed ,sp:stmt completed ,SQL :stmt completed .

    I found the below queries are running continuously .

    exec sp_cursorunprepare 187379994990 --rpc completed

    FETCH API_CURSOR00000000000000E1 --sp:stmt completed

    sp_cursorclose 180156333 --SQL :stmt completed

    exec sp_cursorfetch 180166335,1,0,138 --rpc : completed

    Basd on this how can i provide suggestion from my side to application team on code changes .

    Thanks,

    Lavanya

Viewing 15 posts - 1 through 15 (of 17 total)

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