Post reply

Memory Utilization Advise

  • June 11, 2012 at 4:02 am

    #392478

    Hi Guys,

    I do understand that SQL Server's caching behaviour is the reason for the substantial memory use. This behavior is normal and is an intended behavior of the SQL Server.

    Just need some advise here from experts out there.

    1. With setting max server memory limit: Let's say we have 8GB Physical Ram and SQL Server Memory is limit at 4GB. With this limit in placed, we will have a nice monthly memory utilization chart, probably about 70% monthly. However the memory utilization chart doesn't mean anything since the memory is capped. Kindly advise how do we know if the memory (4gb) allocated to SQL Server is really sufficient and any indicators that will tell us that it's time to allocate more memory to SQL Server.

    2. Without setting max server memory limit: Let's say we have 8GB Physical Ram and the memory utilization is 95%. How do we know when the memory is really insufficient so that we can plan to beef up the Memory?

    I'm asking this as basically in alot of environment, the management use memory utilization as the sign to deem whether a server is healthy or not. However for SQL Server, we can't really use the memory utilization as a gauge anymore. As such, I wondering if there is any indicators that we can use that can justify that the memory is healthy.

    thanks

  • June 11, 2012 at 4:33 am

    #1499225

    http://www.sqlskills.com/blogs/jonathan/post/How-much-memory-does-my-SQL-Server-actually-need.aspx

    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

  • June 11, 2012 at 9:05 pm

    #1499573

    Hi Gila,

    thanks.

    According to the url, we can monitor 3 perfmon counters to see if our memory utilization is healthy:

    •SQL Server:Buffer Manager\Page Life Expectancy

    •SQL Server:Buffer Manager\Page reads/sec

    •Physical Disk\Disk Reads/sec

    Based on ur expertise, what is a good value range for the above counters?

    thanks

  • June 12, 2012 at 4:08 am

    #1499681

    You could start by what Jonathan says in his blog post, the paragraph right below where he lists the counters.

    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

  • June 12, 2012 at 7:54 am

    #1499805

    "good enough" values for pretty much ANY counter are VERY dependent on your system and what type of performance you need and are actually getting. Assuming your server can hold more memory, adding RAM is usually WAY CHEAPER and WAY MORE EFFECTIVE at making things go faster in SQL Server, because stuff that is already in the larger RAM-based buffer pool doesn't have to be retrieved from the 3-4 orders-of-magnitude-slower disk system.

    Having said that, virtually EVERY client I have EVER had has not had sufficient IO response time and/or throughput for what their server really needed to make things as fast as they needed/wanted.

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

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

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