• SQLDBAPerth (11/14/2013)


    To address the other points raised - max server memory is set at 60GB - 4GB should be enough IMO for the OS and other services. There is literally nothing else running on the server - any non-essential windows services are disabled, no additional software is installed, there is no virus scanner, things like ole automation, extended procs, linked servers are not used at all. CLR usage is minimal and restricted to admin jobs.

    What you aren't accounting for is that it takes memory in the Kernel to map the memory being used. The overhead in Kernel memory usage is roughly 7% on top of the other memory allocations for the OS and other services, so at 60GB, you are certainly going to be bumping up against memory pressure. My recommended calculation for a starting point for 'max server memory', is to reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. For a 64GB RAM server, this would result in 10GB of reserved memory and max server memory being configured at 54GB. From there I always say monitor Memory\Available MBytes over time and slowing increase 'max server memory' up from this base calculation which is conservative and will leave memory available.

    The problem with setting 'max server memory' too high is that SQLOS will respond to Windows memory pressure and shrink memory usage to return memory to Windows when needed. If you look at the ring_buffer entries you will see this with low memory notifications that have IsSystem = 2, and the process is consistently in a grow/shrink/grow/shrink/grow/shrink cycle that isn't ideal for caching or performance, since each low notification is a system wide external clock hand sweep across all caches forcing entry cleanup to release memory. It is much easier to see that you have extra available memory and can increase 'max server memory', it is harder to tell you set it to high and the process is growing and shrinking regularly.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]