CPU High utilization -urgent help please

  • Hi All,

    One of our Prod box running SQL2008R2 having CPU high utilization issue

    I already checked Min/Max memory changed it but no effect(Min 20GB Max is 100GB)

    SQL remain on 51GB

    2. I used DBCC DROPCLEANBUFFERS, after couple of minutes cpu is back to 80%

    3. DBCC MEMORYSTATUS is showing me locked allocated page which is taking 47GB memorym, I need to know do I need to disable this page locking, why it is suddenly locked the pages?, the server OS is W28

    How to unlock this paging?

    How to resolve it, kindly help me and tell me how to fix this issue ASAP

    Thanks

    Dave

  • Have you checked for runaway queries with massive paralellism?

  • Below are the settings:

    The Max degree of Parallelism is setup with "1"

    Cost Threshold is "5"

    Query Wait is "-1"

  • DROPCLEANBUFFERS is likely to worsen problems, not fix them, it throws the data cache out forcing SQL to read from the slower disk not memory until the cache has been repopulated. If it reduced CPU it's probably because SQL was instead waiting on IOs and generally not getting as much done as before.

    High CPU - chapter 3 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    p.s. Why is max degree of parallelism set to 1?

    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
  • Thanks Gail.

    I don't know why it was setup as '1' because the person who build the server is gone

    What exactly I suppose to do tonight ?

    Just downloaded the book, Will be reading the chapter 3 right away

    What else you suggest ?

  • Read chapter 3, work through it. If this is a 'Must Fix Now!' crisis, tell your boss to hire a consultant.

    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
  • qew420 (11/1/2012)


    3. DBCC MEMORYSTATUS is showing me locked allocated page which is taking 47GB memorym, I need to know do I need to disable this page locking, why it is suddenly locked the pages?, the server OS is W28

    How to unlock this paging?

    Missed this first time I read.

    Locked pages is when the service account has the locked pages in memory privilege. It prevents windows from paging SQL's buffer pool to disk (which would be a rather bad thing)

    Hence you probably don't what to disable that locking, doing so could easily make matters far worse.

    If SQL is using too much memory, reduce the max server memory setting (the book I previously recommended, chapter 4)

    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
  • ======================================================

    Locked pages is when the service account has the locked pages in memory privilege. It prevents windows from paging SQL's buffer pool to disk (which would be a rather bad thing)

    Hence you probably don't what to disable that locking, doing so could easily make matters far worse.

    If SQL is using too much memory, reduce the max server memory setting (the book I previously recommended, chapter 4)

    ====================================================

    Gail: how to make this to work, how to fix the page locking as now 59GB is occupied by the lock

    SQL Server is not eating any memory : it stays on 65GB but CPU consumption is high "75 to 88%"

    Just to test: I stopped the reporting services and CPU was back to 2%

    After turning on CPU went back to 85%

    So I really need to know how to fix Paging issue, how to unlock the pages and release memory ?, Is this where I need Windows admin assistance or I login with service account and fix as a DBA, can you share the steps please

    Also Why SQL is not CAChing Memory out of 100GB - it is only using 65 ? weather services are running or not the Memory count remains on 65GB

    Which make me think that 59GB is locked and SQL is only using 5GB ?

    Thanks

  • qew420 (11/1/2012)


    ======================================================

    Locked pages is when the service account has the locked pages in memory privilege. It prevents windows from paging SQL's buffer pool to disk (which would be a rather bad thing)

    Hence you probably don't what to disable that locking, doing so could easily make matters far worse.

    If SQL is using too much memory, reduce the max server memory setting (the book I previously recommended, chapter 4)

    ====================================================

    Gail: how to make this to work, how to fix the page locking as now 59GB is occupied by the lock /quote]

    Read what I said. Locked pages is something you probably want, not something you need to fix. It is SQL locking its memory so that the OS can't page it out to disk. Paging SQL's memory out to disk slows things down a lot

    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
  • Still working on this issue

    I changed the min and max but no effect

    Thx for helping with Paging logic

    Anything else you can recommend to resolve the issue

    Thanks

    Dave

  • Is this a dedicated SQL Server?

    Or are other applications running on it?

    SQL 2008 or 2008 R2?

    R2 has some improvements in how it handles memory.

    Especially if you are running both SQL Server and SSAS on the same box.

    As Gail points out, you might want to consider bringing someone in.

    I assume this is production, which is usually not a good place to do too much experimentation.

  • I see you are running R2.

    The reason for asking about other applications - especially SSAS - is that SQL and SSAS will use all the memory you can give them.

    Flushing to disk, as Gail pointed out, is I/O and CPU intensive.

  • qew420 (11/2/2012)


    Still working on this issue

    I changed the min and max but no effect

    Errr... please don't tell me you set min and max to the same value. Min isn't something that always needs changing.

    Anything else you can recommend to resolve the issue

    Did you work through the book chapter I recommended?

    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
  • qew420 (11/1/2012)

    SQL Server is not eating any memory : it stays on 65GB but CPU consumption is high "75 to 88%"

    Just to test: I stopped the reporting services and CPU was back to 2%

    After turning on CPU went back to 85%

    It sounds like SQL Server reporting services (SSRS) is causing high CPU utilization, not the SQL database engine. If this is the case then start looking at what reports are causing the high utilization (google "ssrs report utilization").

  • Thanks all and Gail issue fixed

    I changed the Min and Max few times & finally I ended up restarting the server too

    & no I didn't used same values for Min/Max

    Dave

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

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