Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Memory Management Expand / Collapse
Author
Message
Posted Tuesday, November 12, 2013 10:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 1:15 AM
Points: 173, Visits: 162
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?
Post #1513706
Posted Wednesday, November 13, 2013 1:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:27 AM
Points: 1,402, Visits: 278
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.
Post #1513731
Posted Wednesday, November 13, 2013 6:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 4,472, Visits: 6,402
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 at GMail
Post #1513842
Posted Wednesday, November 13, 2013 9:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #1513952
Posted Wednesday, November 13, 2013 5:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 1:15 AM
Points: 173, Visits: 162
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.
Post #1514120
Posted Thursday, November 14, 2013 2:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 2,900, Visits: 3,301
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 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1514186
Posted Thursday, November 14, 2013 8:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 4,472, Visits: 6,402
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 at GMail
Post #1514309
Posted Thursday, November 14, 2013 11:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #1514399
Posted Thursday, November 14, 2013 5:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2014 1:15 AM
Points: 173, Visits: 162
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.
Post #1514540
Posted Thursday, November 14, 2013 7:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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
Post #1514557
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse