SQL Server Memory is full and application is hanging

Saketh.K 2016-04-26 07:30:13

We have production SQL Server 2012, where we had allocated Max memory of ~60 GB and Min Memory of 4 GB, We have the following situations and if it is full we are restarting the SQL Server services.

Problem is after restarting RAM is increasing from 4-8 GB a day and on 4 to 6th day RAM is occupying complete RAM,

1) SQL Server memory is occupying to its fullest i.e. 63 GB RAM,
2) CPU Utilization is between 15- 26 %
3) We have 1000 I/O ops disk(SAN).
4) We are using Windows server 2008 R2
5) We are using SQL Server 2012 RTM Enterprise Core edition.(No Service Pack Applied)
6) No: of Core Processors = 24.

We have analyzed through the areas through perfmon counters that are

1) Buffer cache hit ratio is 100
2) Buffer Manager: PLE is > 80000
3) Memory Available Mbytes is : 800 MB
4) Total Server Memory is > 62 GB(62,042,747.22 KB)
5) Target Server Memory is ~ 62 GB (62,047,742.400 KB)
6) Batch Requests/sec 15.2
7) Pages/Sec is <10 8) Latch waits/Sec : 19,936 9) Total Latch Wait time(ms) : 23,916 10) Deadlocks are also occurring 11) Memory Grants Pending is 0(Zero). Please suggest a solution where we can improve performance to fix this issue.

sdyckes 2016-04-26 15:27:28
Saketh,

Your description of SQL slowly allocating and utilizing the RAM until it has taken all the memory allowed by your Max Memory setting is the expected behavior of SQL Server. Your current Max Memory setting of 60GB may be too high for the amount of memory and number of CPUs the OS is managing. Jonathan Kehayias has a blog post, How much memory does my SQL Server actually need?, that will help you to set the memory allotment correctly. You will want to monitor the settings and performance to possibly alter them for your environment.

I am not clear as to why the restart of the system is required. Are you restarting the server to release the memory SQL Server has taken on the system? Your metrics seem to indicate the SQL Server is functioning well from the memory perspective (PLE and Memory Grants Pending). My concern would be the Latch Wait time or possibly starving the OS of memory. The second will be addressed by the link above, the first would need additional research. I would recommend examining the SQL Wait statistics, Check out Paul Randal's post, Wait statistics, or please tell me where it hurts.

Saketh.K 2016-04-27 05:24:57
Thanks for your answer, I am restarting SQL Server to release the memory as you expected, but here as you said if wait stats are my problem i need to check that whether it is causing my application to hang after consuming the memory of OS.
PARAG 2016-06-03 16:29:31
I don't think restarting the SQL server repeatedly is a good idea

Until the SQL server ramps up memory usage, you will see performance issues

Your available megabytes counter also looks good which means OS is not starved of memory

That's how SQL server behaves being a memory hungry application. It will consume whatever space has been allocated to it

Try reducing the max memory to 56 GB

Also if the application hangs, at that exact time, what is the memory grants pending counter ? I also suggest to watch out for RESOURCE_SEMAPHORE wait type

Try to see which queries are consuming more memory (doing more reads) and try to tune them