SQL Server Memory is full and application is hanging
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.
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.
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