SQL Server Memory Issues

Question

We have a SQL server that appears to be leaking memory. Memory consumption linearly increases over time until the server is completely out of memory, causing major performance issues.

The SQL server houses 6 small (average than 50MB) databases and one that is a little bit larger (about 2GB).

The server is currently a virtual server inside VMware 5.1. It has very light average workload (~1% cpu, <10 batch requests / sec, < .5 mb/s IO typical; spikes push to ~10% cpu, 300 batch request / sec, 5 mb/s IO).

Currently, it has 8GB allocated to the VM. Of this, 4GB is set to the maximum server memory in SQL server. The SQL server process, however, uses significantly more than that - usually around 6GB within 12 hours of a restart.

Apart from its primarily read workload, the server runs many SSIS packages to get data in and out to other systems within the corporate structure. This utilizes MySQL via ODBC (5.2.6 drivers installed) to connect to an internal MySQL cluster and ConnX to interface with OpenVMS systems.

OS: Windows 2012 SQL: 2012 Enterprise VM Settings: 4 CPU cores, 8GB, separate virtual volumes for OS, Data, and Logs.

Questions: 1) Any ideas on why the SQL Server process would not be following the limit set on the server? 2) Does anyone know a good way of profiling memory usage within SQL server? We can see that page life expectancy is dropping and more memory is being allocated to the process, but is there any way to see what SQL is allocating the memory to (i.e. procedure cache, cached results, etc).

Answer 1

You've got a couple of questions in here:

1) Any ideas on why the SQL Server process would not be following the limit set on the server?

Max server memory refers to the buffer pool, but not everything that the server uses memory for. For example, SSIS isn't part of the SQL Server engine - it's another application that just happens to come free in the box with SQL Server. (Same thing with SSAS and SSRS.) Max server memory has no impact on SSIS. This is one of the reasons you'll often hear recommendations that you should separate SSIS onto its own instance - especially if you're using SQL Server Enterprise in virtualization. At $7k USD per core, you're talking about $28k worth of licensing here, and you're working with something like $500 of RAM.

2) Does anyone know a good way of profiling memory usage within SQL server? We can see that page life expectancy is dropping and more memory is being allocated to the process, but is there any way to see what SQL is allocating the memory to (i.e. procedure cache, cached results, etc).

Yes, inside the SQL Server engine, you can use DMVs like sys.dm_exec_memory_clerks. If you're using the free Opserver tool from the StackExchange guys for SQL Server monitoring, look at the memory clerks listing. If not, you can start with the memory clerks query it uses.

However, outside of the SQL Server engine - like with your SSIS packages - SQL Server DMVs can't help because this is happening out of process. You'll need to use conventional systems administration tools to do process monitoring and watch which processes use RAM.

Answer 2

The maximum server limit is just for the buffer pool!

Try (unless you are using numa):

dbcc memorystatus

If that isn't enough: there are a lot of DMVs like dm_os_performance_counters, dm_os_process_memory, dm_os_memory_clerks, dm_os_memory_cache_counters