SQL Server Occupies more than allocated memory. Possible memory leak

Question

Using SQL Server 2012 64 bit (v11.0.6020.0 - 2012 SP3)

Below is the scenario:

On our production we have 32 GB DDR3 RAM installed:

Maximum memory limit has been set in SQL server to 16 GB ie 50% capacity:

When I fire up the task manager and check memory occupied in value it shows 16 GB which is correct:

But when I select memory occupied by Percentage, it shows 80%-85% which is NOT RIGHT:

This will keep on Increasing, until it occupies more than 95% and the

1. Entire system will slow down
2. Queries will time out

The only way to correct this is a server restart

My questions are

1. Is SQL Server leaking memory?
2. Any quick fix so I don't have to restart?
3. Permanent resolution?

Answer 1

My questions are Is SQL Server leaking memory?

Highly unlikely but you should plan for SQL Server 2012 SP4 ASAP. From my past experience I could say that since SQL Server and other applications like SSAS and SSRS are all running on same machine SQL Server might be facing memory pressure and so might be the case for SSAS/RS. I have seen lot of systems like this and it all boils down to memory pressure.

You have 32 G of RAM and you have given just 16 G to SQL Server make it to 20 G and see if this help. Adding more memory would definitely help if you can go for it.

There can be plethora of reasons why queries are timing out and Troubleshooting SQL Server performance issues can help you to find the root cause of it.

But when I select memory occupied by Percentage, it shows 80%-85% which is NOT RIGHT:

I would start by saying Task Manager is not a correct place to gauge SQL Server memory consumption, it will not tell you correct value when SQL Server service account has Locked Pages in Memory(LPIM) privilege. This is because normally task manager tracks Process Private bytes memory which is pageable and allocated via VirtualAlloc() function but with LPIM chunk of memory allocation is done by AWE API which is NON pageable so task manager does not tracks it and this can lead to incorrect value. For percentage thing which you are looking is actually Percentage of Process Private bytes not the complete memory and it does not provide any relevant information so just stop looking at it.

It is quite normal for SQL Server to utilize memory allocated to it and to know how much physical memory SQL Server is using please use query

select * from sys.dm_os_process_memory

PS: It is always recommended to move other applications on different machine (if possible) and let SQL Server run solely on it own system this will help SQL Server run faster and better

Answer 2

The answer is detailed in a KB article.

SQL Server 2012 and later versions might allocate more memory than the value that's specified in the max server memory setting. This behavior may occur when the Total Server Memory (KB) value has already reached the Target Server Memory (KB) setting (as specified by max server memory). If there is insufficient contiguous free memory to meet the demand of multi-page memory requests (more than 8 KB) because of memory fragmentation, SQL Server can perform over-commitment instead of rejecting the memory request.

It would be more likely that you're hitting this issue than that you've discovered a bug or memory leak.

You should follow the advice of the other posted answers: apply SP4, and think more carefully about the other apps and components you pile onto the SQL Server box you pay thousands of dollars per core for.

Answer 3

No. This is normal. You are experiencing too many applications installed on your database server. Start migrating your database (SQL Server database engine) to its own server or start uninstalling all other applications in the database server.

Currently your server have the following: IIS, RS, AS, IS, mongodb installed. Not to mentioned if you have monitoring tools or anti-virus installed. When you login to the server, you also use memory. filter drivers also use memory.

Check out this blog post by Jonathan. Let's say, you've a barebone database server (without the other application installed fighting for memory), you can at least have 27GB of memory for your SQL Server.