Sql-server – SQL Server ’12 Using RAM equal to 2x size of the only user database

sql serversql-server-2012

Only user database on instance is ~8.5GBs.
MAX Mem set to 16GBs, which is ALL being consumed.
Virtual server has 24GBs RAM and 2 vcpus
Insert Poltergeist meme "What's happening?!"

***Side notes****
2 vcpus have low avg utilization(<50%).
Server hosts app (using ~500MBs of RAM).
Indexes are rebuilt based on size(page count >= 1000) and avg_fragmentation(>=50%).
Thought Tempdb may be issue but not seeing wait stats to indicate.
Top 10 expensive queries aren't using much CPU, RAM(logical reads), or being executed over and over.
Wait stats show ASYNC_IO almost with 1-to-1 wait count per second, but these are coupled with high wiats for backup related waits…

Best Answer

SQL Server will never ever ever give back any memory that it uses unless pressure is exerted on the service by the operating system. It's designed that way intentionally to do as much processing in memory as possible to prevent disk I/O.

In the comments above there's a linked article to A Sysadmin’s Guide to Microsoft SQL Server Memory which contains some good information explaining the specific use cases and behavior that you can expect. Generally though it's best to remember that the structure of data held in the SQL buffer cache is based on performance, not resource conservation. Make sure your maximum memory is set appropriately for your environment and needs.

If you are running SQL Server in a virtual environment where you are trying to reduce the amount of memory on an ongoing basis then you will need to use a balloon driver or other utility to exert memory pressure on the instance.

For performance purposes if you are seeing a sudden flood of complaints it's possible that the SQL instance is running out of memory and is paging to disk, which will result in a very significant performance hit. There are multiple recommendations, but my personal preference is to not use a page file at all which will result in certain crash dumps not being returned in the event of a windows fault.