SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Memory Management


Memory Management

Author
Message
SQLDBAPerth
SQLDBAPerth
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 167
I have a SQL Server 2012 server with SP1 - 64GB RAM, server is solely dedicated to SQL Server so it's grabbed just under 63GB of that.

According to the count of records in sys.dm_os_buffer_descriptors there is 2598791 pages in the buffer - which equates to roughly 20GB of buffer pool by my calculations.

The Memory Manager\Free Memory performance counter tells me there is over 33GB of unused memory yet at regular intervals throughout the day the page life expectancy plummets down to < 100 seconds where it will sit for a minute and then gradually start increasing again - sometimes it gets as far as what was normally its resting point of around 3,500 seconds, but sometimes it gets up to a few minutes and does its plummet again.

Looking at sys.dm_exec_query_Stats I can't see any queries having hit the server that have done massive physical reads that would force a load of things out of the buffer - yet something is happening causing this.

I'm wondering if I may be hitting some aspect of this bug as I'm also seeing some peaks of the lazy writes/sec counter so I've scheduled to install CU4 and see if that sorts things out.

I'm no expert in SQL Server memory management so looking for some guidance here as to why SQL Server would be leaving over half of the available memory free. Are there some pertinent performance counters/DMV's that I'm not looking at which might shed some more light on the situation?
SQLTuna
SQLTuna
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1438 Visits: 328
Do you have an excessive number of linked servers, CLRs etc? Multi-page allocations, BLOBs? Are you locking pages in memory?

Try running select * from [sys].[dm_os_process_memory] - posting the results would be useful. Also, possibly posting the output from DBCC MEMORYSTATUS would be too although that's a lot more data.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12244 Visits: 8541
1) SP1 has a CRITICAL BUG that has a MANDATORY patch for it. You MUST at least apply that.

2) SP1 has MULTIPLE (IIRC) memory leaks that will suck away your RAM over time. I STRONGLY recommend testing and patching to the latest CU!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2864 Visits: 1807
The condition you describe fits the SQLOS memory manager bug that is fixed in CU4 (http://support.microsoft.com/kb/2845380) but if you are going to take an outage to apply CU4, I would just go ahead and apply CU6 and bring your instance up to the latest update. You would have to track your resource monitor ring buffer entries to get more information about the internal low notification being set that is trimming memory internally to troubleshoot further, but it would generally be easier to test the CU on a test server under your normal workload to see if the behavior changes to what is expected in most cases.

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
SQLDBAPerth
SQLDBAPerth
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 167
Thanks guys - I had recommended that they apply the latest CU and I believe they applied CU6 last night. I'm on-site next at that client tomorrow so I'll check out their servers at that stage and see if things look any better and if the performance problems have eased off.
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5737 Visits: 3860
You have also over-allocated your server memory. On a 64GB server, you should not try to get SQL to use 63GB.

There are many processes that need to use memory... start with the operating system, add anti-virus and other services, SQL Server processes that run outside the main SQL memory space, backups, etc, etc.

A rough rule is to allow 20% of your server memory for processes outside the main SQL memory space, so on a 64GB server you should start by setting maximum SQL memory to 51GB. When you have some memory usage stats, you can fine-tune this setting, which may then move either up or down.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12244 Visits: 8541
GREAT catch Ed! I focused on just the memory leak situation and zoomed right over the obvious lack of MAX MEMORY cap! Personally I would start with something a bit higher than 51GB for a dedicated SQL Server with 64GB of RAM. I do 57GB as a starting point on SQL 2012 (where max is actually a MAX - as opposed to older versions) and monitor for excessive paging.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2864 Visits: 1807
TheSQLGuru (11/14/2013)
GREAT catch Ed! I focused on just the memory leak situation and zoomed right over the obvious lack of MAX MEMORY cap! Personally I would start with something a bit higher than 51GB for a dedicated SQL Server with 64GB of RAM. I do 57GB as a starting point on SQL 2012 (where max is actually a MAX - as opposed to older versions) and monitor for excessive paging.


Max Server Memory is not actually the max at all in SQL Server 2012. It is closer than previous versions due to the memory manager change to a unified "any size" page allocator for most internal components, but it doesn't account for thread call stack memory in SQLOS, or any allocations made from the VAS allocator directly through calls to VirtualAlloc, examples being SQLCLR, OLE Automation calls, extended procs or even inprocess linked server providers.

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
SQLDBAPerth
SQLDBAPerth
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 167
Thanks for all the comments guys - after they applied CU6 a couple of days ago I'm seeing PLE consistently at 1.5 hours, the buffer pool is no longer using only 20GB, it's taking 51GB with the SQL Server free memory counter down to 256MB. Much happier with that than I was with what I was seeing before and that obviously answers my question on why SQL Server was leaving such a large amount of free memory.

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.

In any event I take on board your suggestion of reducing the max setting and will monitor whether or not the server is being starved of memory for other processes and will consider reducing that if it is the case.

Thanks again for the comments.
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2864 Visits: 1807
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search