SQL 2016 and max server memory

• TheMaskedDBA

  SSCrazy

  Points: 2787

  More actions

  August 18, 2017 at 9:24 am

  #323185

  I have a question on a particular scenario I have:

  I have 2 SQL 2016 named instances and a SQL 2012 instance running on a Windows 2012 Server with 32 cores and 512 GB of memory. The memory allocations(max server memory) are as follows:

  SQL 2016 SP1 Instance 1 - 200 GB
  SQL 2016 SP1 Instance 2 - 100 GB
  SQL 2012 SP3 Instance - 100 GB

  We still see events like this "A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 2408, committed (KB): 188956868, memory utilization: 0%%." and server becomes unresponsive for hours, days on end, culminating in a reboot as the only solution. During this slow period, task manager and perfmon still show plenty of free memory, but the server overall is extremely slow. To me, it looks like a memory leak or a memory mapping issue somewhere, but I would be interested in what you people think.

• samuel.bartick

  SSC Enthusiast

  Points: 162

  More actions

  August 21, 2017 at 4:02 am

  #1955955

  max server memory is the limit for the buffer cache memory allocation. there are other areas of memory usage outside of this allocation, such as clr, which can exhaust the available memory.

  run DBCC MEMORYSTATUS and see where additional memory is being used....also see if the following reveals anything:

  SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB] FROM sys.dm_os_process_memory;GO

SELECT TOP (20) * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC

SELECT * FROM sys.dm_os_sys_memory

  SELECT * FROM sys.dm_os_memory_nodes WHERE memory_node_id <> 64;

SELECT counter_name, instance_name, mb = cntr_value/1024.0 FROM sys.dm_os_performance_counters WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total') OR (instance_name = N'' AND counter_name IN    (N'Connection Memory (KB)', N'Granted Workspace Memory (KB)',    N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)',    N'Log Pool Memory (KB)', N'Free Memory (KB)') ) ORDER BY mb DESC;

• JakDBA

  SSCommitted

  Points: 1610

  More actions

  August 21, 2017 at 12:35 pm

  #1956045

  sqlGDBA - Friday, August 18, 2017 9:24 AM

  I have a question on a particular scenario I have:

  I have 2 SQL 2016 named instances and a SQL 2012 instance running on a Windows 2012 Server with 32 cores and 512 GB of memory. The memory allocations(max server memory) are as follows:

  SQL 2016 SP1 Instance 1 - 200 GB
  SQL 2016 SP1 Instance 2 - 100 GB
  SQL 2012 SP3 Instance - 100 GB

  We still see events like this "A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 2408, committed (KB): 188956868, memory utilization: 0%%." and server becomes unresponsive for hours, days on end, culminating in a reboot as the only solution. During this slow period, task manager and perfmon still show plenty of free memory, but the server overall is extremely slow. To me, it looks like a memory leak or a memory mapping issue somewhere, but I would be interested in what you people think.

  are you sure your server license is using all the resources you have on the box for SQL Server, show us the log... saying its been consumed.

  @JayMunnangi

• Perry Whittle

  SSC Guru

  Points: 233918

  More actions

  August 24, 2017 at 8:14 am

  #1956635

  sqlGDBA - Friday, August 18, 2017 9:24 AM

  I have a question on a particular scenario I have:

  I have 2 SQL 2016 named instances and a SQL 2012 instance running on a Windows 2012 Server with 32 cores and 512 GB of memory. The memory allocations(max server memory) are as follows:

  SQL 2016 SP1 Instance 1 - 200 GB
  SQL 2016 SP1 Instance 2 - 100 GB
  SQL 2012 SP3 Instance - 100 GB

  We still see events like this "A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 2408, committed (KB): 188956868, memory utilization: 0%%." and server becomes unresponsive for hours, days on end, culminating in a reboot as the only solution. During this slow period, task manager and perfmon still show plenty of free memory, but the server overall is extremely slow. To me, it looks like a memory leak or a memory mapping issue somewhere, but I would be interested in what you people think.

  You're likely not leaving enough physical RAM for the OS hence why it becomes unstable\unresponsive.
  Is sql server the only application running on the server?

  samuel.bartick - Monday, August 21, 2017 4:02 AM

  max server memory is the limit for the buffer cache memory allocation. there are other areas of memory usage outside of this allocation, such as clr, which can exhaust the available memory

  Starting SQL Server 2012 onwards, the max server memory stting accounts for more than just buffer pool page allocations, it now encompasses CLR and memtoleave as well as extended SP activity

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉