SQL Server Memory is Full (Urgent help!)
-
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
Please suggest a solution where we can improve performance.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
Leave it as it is. That's how it's supposed to work. SQL Server won't give back memory it has taken unless the operating system asks for it. Provided you've set max server memory such that there is enough memory for the OS and any other programs running on the server, you don't need to do anything.
John
-
Is the counters i had given are at optimal condition or do i need to increase RAM to 128 GB as [highlight="#FFFBCC"]Target server memory[/highlight] is showing 62 GB approximately
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
If you've got all that additional memory in your server and it's not doing anything, then yes, increase your max server memory. Check it's not needed for anything else (Reporting Services, a second database instance, other applications and so on) and leave enough for the OS (maybe 8GB).
John
-
info.sqldbamail (4/26/2016)
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
Looks like a well-behaving SQL Server instance doing exactly what it should be doing, nothing looks off in any of those settings or values. I wish all the servers I saw were as good.
So, what's the problem that you have?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
SQL Server is occupying the RAM which it is set Max, and additionally it is also taking the Memory which is left for Windows, where do i need to look at this memory bottleneck,
Does SQL Server 2012 RTM(No Service pack applied) can cause or have a memory leak,
I have a CMEMThread poison wait is that a problem of NUMA configuration/Memory Configuration.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
I think I may have missed the server's actual physical RAM, but if it is 64GB then 60GB for max memory for SQL Server is definitely too high.
The worst thing I see BY FAR is that you are on SQL Server 2012 RTM. That is a REALLY REALLY bad place to be!!!
Oh, there is absolutely no need (based on your original post anyway) to increase the memory on the server. Your PLE is 80000, meaning pages are staying in memory for a VERY long time. I would expect that you don't have very much read activity on the box.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
info.sqldbamail (4/26/2016)
SQL Server is occupying the RAM which it is set Max, and additionally it is also taking the Memory which is left for Windows, where do i need to look at this memory bottleneck,That's not a memory bottleneck. That's normal, expected, documented behaviour, and it's what you want to have.
SQL uses memory to cache data and execution plans. The more memory it has, the better.
If you think that SQL's using a little too much, then lower max server memory a tad. From the metrics you posted, SQL's not under any memory pressure, so it is safe to drop max server memory to maybe 56GB, however having Available MB at 800 is probably fine.
There's no indications from the stats you posted that there are any forms of memory bottlenecks or memory problems there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Your memory is behaving exactly as it's supposed to. As Kevin has said, your max might be a little high. Probably leaving 8gb for the OS would work better.
However, the real issue is the one that Kevin has, again, already pointed out. You're running RTM of SQL Server 2012. Get that fixed right now. That's a major issue. There are any number of bugs and security issues that have been addressed in the four years since 2012 was released. Get the latest service pack and cumulative update installed as soon as you possibly can.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thanks for your answer, one last question i would like to know is, if SQL Server like to consume whole available memory then why it is not consuming whole RAM at Server Start up/restart, it increases day after day by 4-6 GB and then it stays at 63 GB, why it can't allocate to buffer at server startup.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
Because it's not designed to. It will increase memory usage as necessary, up to the max allowed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
info.sqldbamail (4/26/2016)
Thanks for your answer, one last question i would like to know is, if SQL Server like to consume whole available memory then why it is not consuming whole RAM at Server Start up/restart, it increases day after day by 4-6 GB and then it stays at 63 GB, why it can't allocate to buffer at server startup.I do not recommend this because it will seriously negatively impact your startup time, but if you really want it to immediately use the memory you allocate for it, you can set the minimum to the higher value. Again, this is not recommended, but it is how you would do it.
It doesn't pre-allocate the buffer at start-up because it doesn't know how much of the buffer you will need or use, so it only loads what it needs. It tries to keep as much in memory as possible because that's faster than constantly going to disk. Everything you're describing is good, expected, behavior that you do want.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant Fritchey (4/26/2016)
I do not recommend this because it will seriously negatively impact your startup time, but if you really want it to immediately use the memory you allocate for it, you can set the minimum to the higher value. Again, this is not recommended, but it is how you would do it..Sorry, but that's not what will happen.
Minimum isn't what's allocated at startup. It's the value that, once allocated, SQL will not drop memory usage below. It'll still ramp memory up the same way it does with a low min server memory.
The only way to get memory allocated at startup is to use large pages, which I absolutely do not recommend doing without a damn good reason and a lot of testing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This sounds like the typical struggle that DBAs have with Server Ops or with business folks who look at memory from a typical app server perspective. They see SQL Server running hotter than 90% memory and then all sorts of alerts go out. At my last job they published a report that gave end users a dashboard on server metrics without explaining what was normal, and we spent so much time explaining that a well running SQL Server should be over 90% memory used. To them it was a sign something was wrong, to us it was proof all was well (combined with PLE over 100,000 natch). SQL Server runs hot in memory when it runs well.
-
info.sqldbamail (4/26/2016)
Thanks for your answer, one last question i would like to know is, if SQL Server like to consume whole available memory then why it is not consuming whole RAM at Server Start up/restart, it increases day after day by 4-6 GB and then it stays at 63 GB, why it can't allocate to buffer at server startup.Because even though it only gives memory back when asked, it doesn't take memory for itself until needed. So if you ran SELECT * FROM 20GBTable immediately after startup, you'd find SQL Server using 20GB of memory (or maybe 20GB more memory than it would have been using had you not run the query).
John
Edit - oops, posted before I read the second page of posts! Others have already pointed out more or less what I said.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply