Sql-server – SQL Server 2012 Crashes and service doesn’t restart

1. Use the maxmemory to set a limit to how much your Redis database can grow too. Failing to do so, Redis will grow until the OS will kill it once memory is exhausted (per your current experience).
2. The usage of maxmemory should be coupled with maxmemory-policy - you can choose from different eviction policies depending on your use case's requirements. For example, if you use the allkeys-lru eviction policy, Redis will indeed start evicting (the least recently used) data once maxmemory has been reached. Alternatively, you can instruct Redis to evict only expire-able data with the volatile-lru or volatile-random policies. Lastly, you can set the policy to noeviction but that would mean that once memory has been exhausted, Redis will deny further writes with an OOM message.

Edit:

First disable swap - Redis and swap don't mix easily and this can certainly cause slowness.

Also do free -m instead of top for the full picture of your RAM's state (http://www.linuxatemyram.com/).

Max server memory controls buffer pool and all page size allocations, but still does not control things like direct Windows allocations (linked servers, sp_OA, XPs), memory required for threads/thread stacks, etc.

You can probably expect this to be higher on NUMA (though I'm not sure 20 GB is normal); the point is, you can't expect max server memory to fully control the memory used by an instance of SQL Server. If you want the whole instance (not just buffer pool, plan caches, and CLR) to use no more than 64GB, you should set max server memory to something lower.

Some potential ideas for tracking this down (I will normalize everything to MB):

• performance counters

  See if anything jumps out here as excessively large:

  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;
  

• top 20 clerks

  You've already done this, but for completeness:

  SELECT TOP (21) [type] = COALESCE([type],'Total'), 
    mb = SUM(pages_kb/1024.0)
  FROM sys.dm_os_memory_clerks
  GROUP BY GROUPING SETS((type),())
  ORDER BY mb DESC;
  

• thread stack size

  First, make sure this is zero, and not some custom number (if it is not 0, find out why, and fix it):

  SELECT value_in_use
    FROM sys.configurations 
    WHERE name = N'max worker threads';
  

  But you can also see how much memory is being taken up by thread stacks using:

  SELECT stack_size_in_bytes/1024.0/1024 
    FROM sys.dm_os_sys_info;
  

• 3rd party modules loaded

  SELECT base_address, description, name
    FROM sys.dm_os_loaded_modules 
    WHERE company NOT LIKE N'Microsoft%';
  
  -- you can probably trace down memory usage using the base_address
  

• memory-related DMVs

  You may also be able to spot something out of the ordinary looking at these DMVs:

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

This article was written before SQL Server 2012, so some column names and calculations may have to be adjusted, but may give some other avenues to try as well:

• Troubleshooting SQL Server Memory

Some good background in another article on that site too:

• SQL Server 2012 Memory

Some good info about the types of things that use memory outside of max server memory (but no good data about how to collect the actual usage):

• MTL Consumers