Post reply

memory issues in SQL Server 2005 cluster frequently

  • March 3, 2011 at 4:33 pm

    #247565

    Hi all

    I have a A/P cluster server running with SQL Server 2005 RTM and allocated min memory is 8GB and max memory is 24 GB.(Total memory 32GB)

    Today SQL Server showing usage of 26GB,I seached for who are uisng more memory and I found that "CACHESTORE_SQLCP" using 11GB memory.

    I hit with problem second time with in two months....What is best solution for SQL server 2005/2005SP2 if they hit menory bottle neck ?

    I appreciate you help and suggestions.....

  • March 7, 2011 at 4:24 am

    #1295241

    Hi

    I got answer for Technet site 🙂

    http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/47d44d7f-7422-4137-98ab-1c8160563395

  • March 8, 2011 at 7:06 am

    #1295834

    snivas (3/3/2011)

    Hi all

    I have a A/P cluster server running with SQL Server 2005 RTM and allocated min memory is 8GB and max memory is 24 GB.(Total memory 32GB)

    Today SQL Server showing usage of 26GB,I seached for who are uisng more memory and I found that "CACHESTORE_SQLCP" using 11GB memory.

    I hit with problem second time with in two months....What is best solution for SQL server 2005/2005SP2 if they hit menory bottle neck ?

    I appreciate you help and suggestions.....

    SQL 2005 RTM can have over 75% of the buffer pool used for procedure cache!! You need to upgrade to SP2+ to have a fix put in place to limit this. It was a known and VERY bad problem with early versions of SQL 2005.

    Until you upgrade, you can do dbcc freeproccache as a brute-force mechanism to get buffer pool memory back. I have a client where I set up a job to do just that every hour because they don't have the bandwidth to test upgrading from their early SQL 2005 version. NOTE: you will hit some CPU usage pretty hard for compilations after you run this, but it should quickly stabilize as plans are cached.

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

  • March 8, 2011 at 7:45 am

    #1295876

    I should mention that if you are going to upgrade your SQL 2005 installation you should probably go straight to the latest service pack and cumulative update. Don't want you or others to think you should just go to SP2+! 🙂

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

Viewing 4 posts - 1 through 3 (of 3 total)

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