5

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:

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

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?
Improve this question
4
  • You have both SQL Server 2008 and SQL Server 2012 tagged to your question. Was that intentional? Also, do you see the issue with SQL Server 2012 SP4? – Joe Obbish Jan 3 '18 at 5:26
  • what other application running on the box? i see IIS, Reporting Services. and mongodb? – user37701 Jan 3 '18 at 5:28
  • @JoeObbish Have edited the tags. No, have not tried with sp4, currently running sp3 – Sunny Jan 3 '18 at 5:30
  • @dco SQL Server Integration Service, SQL Server Analysis Service, The ones you saw were the important ones. Others hardly take any processing or RAM. – Sunny Jan 3 '18 at 5:33
5

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

Improve this answer
1
  • ,select * from sys.dm_os_process_memory; For +1 – Md Haidar Ali Khan Jan 3 '18 at 8:27
3

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.

Improve this answer
1

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.

Improve this answer
2
  • Accepted, that multiple applications can cause a issue. That is why the SQL server has been limited to 16000 MB. But as shown it occupies much more than that in percentage. In values it shows value as 16000 MB. I know it is normal for SQL to occupy huge amounts, but it is within the allocated range. Also after 90 % occupied by SQL performance fully degrades and restart is required. – Sunny Jan 3 '18 at 6:13
  • @Sunny linked servers, dlls can use outside of max memory configured. so you need to isolate your SQL Server database engine to its own server. and your SQL Server instance might need more than 16GB of memory. – user37701 Jan 3 '18 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.