MSSQL Server 2014 Freezes every time RAM get's to 79GB
-
We have the Enterprise version 12.0.2000 running on:
Windows 2012 R2 Standard 64 Bit
8x16GB Micron 16GB DDR4 2Rx4 (128 GB of RAM)
2.6GHz Intel Xeon-Haswell (E5-2690-V3-DodecaCore)
2.6GHz Intel Xeon-Haswell (E5-2690-V3-DodecaCore)The Issue that we have:
Around the same time each day for the past 4 days 1 hour up and down the SQL Server becomes completely unresponsive, we are not able to connect to it at all, we can RDP into the machine and watch what's happening but we can not connect to the SQL Server, since it happened 4 times the first two times it lasted for 20 minutes the third time it was 10 and today it was 30 minutes. RAM starts to drop at that point and after it drops to 0 everything is back to normal.What's interesting
The RAM almost always get's to almost 80GB which you can see in the attached Usage.png screenshot and one of the cores is at 100% utilization when this happens. Then as time starts to pass from when it happens the RAM starts to drop little by little for the sqlserver.exe and then it drops down to 0 and, the process that was at 100% starts to work again normally and everything goes back to normal and server starts working again without any issues.Insight into when this started happening
5 Days ago we moved onto a more powerful server which has the same exact specs as this server. Then we had this happen for two days in a row, the second time it happened and we could not figure out what's going on we moved onto a brand new server yet again thinking something is wrong with the server. The same thing is happening on a brand new server and we have absolutely no idea why. The primary difference between the old server and these new ones is that this server has more RAM and has more cores. The old server we had was a 64GB and 16 cores machine & ran the same versions of windows and MSSQL.RAM Usage
Cores
-
I would not even try to figure out what is causing the issue - you are still on the first version of 2014 - upgrade that to the latest SP (SP3) which fixes lots of issues.
-
Adding a bit to this - first the annoying bit. this has been posted on at least 4 sites.
This can lead some of those that know a possible answer not to reply at all.Here plus
https://stackoverflow.com/questions/54846400/sql-server-2014-freezes-every-time-ram-gets-to-79gb
https://community.spiceworks.com/topic/2194655-mssql-server-2014-freezes-every-time-ram-get-s-to-79gb
https://www.tek-tips.com/viewthread.cfm?qid=1793012Not a bit more to the tech.
By looking at the processes image above, it does not seem that SQL Server is the one pegging the CPU - get another screen where it is ordered by CPU and not memory - Memory from a SQL Server point of view is more than OK .
Whatever process is using that 1 single core/thread is most likely the one causing the issue.Then go through Windows Event Log and through SQL log to see what messages are there around the times where SQL "crashes". Some will most likely indicate possible root causes for the issue.
Regardless of what is causing the issue upgrade your SQL Version to the latest SP and CU. 2014 had lots of issues on the first versions.
-
Quick question, what else is running on this server?
😎Few thoughts and questions:
- Don't use RDP to monitor the server, rather find a less intrusive monitoring solution?
- Have you correlated the Windows logs and the SQL server logs, there may be some activity that's causing this?
- Have you ran any hardware tests?
- Any reason why the SQL Server is not on the latest service pack and CU?
- What is the max/min memory settings for the SQL Server?
- frederico_fonseca - Sunday, February 24, 2019 4:00 AM
First I am so sorry about posting to all the forums. I was desperate for information and I promise I will update each and every one of them with the solution.
So you sir may be very well right, the Mcafee process is the one actually running at 2% which is about what that 100% single thread is on the overall is equivalent to. It's the Mcafee on access scanner which when we look at it's logs executes almost at the time when we have these issues. We have disabled the scanner completely from executing and stopped it's service to see if it's going to cause the same issue tomorrow or not. This is a production server and unfortunately we can't force start it to see if it causes the same issue but we are trying to replicate it on another machine.
I will update with more info tomorrow.
- Eirikur Eiriksson - Sunday, February 24, 2019 6:45 AM
The hardware test was that we switched to two completely different servers where both had the same issue happen.
We are updating the SQL server to the latest version, this is something we haven't even looked into, we clearly need a DBA.
We also tried setting the max RAM thinking the issue could be due to server getting to almost 80GB of RAM, we had set the RAM to 60MAX and it still happened today.I just replied earlier to the other thread, from the looks of it the issue may be related to Macfee scanner, we have disabled the scanner and will see what happens tomorrow.
-
Disabling AV should not be required - but what needs to be done is to add some exceptions to the scan engine to ignore SQL Server related files (normally .mdf, .ldf and .ndf but can have other names).
To be safe, all folders related to SQL Server itself should be on the exclusions list. -
First check the error log there should be information why SQL wasn't able to accept new connections eg. non yielding scheduler. Depending on the error log there will be next steps.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply