March 8, 2017 at 8:07 am
Hello.
Last saturday I migrated a sql server 2008 r2 sp3 from a server Dell R730 Xeon E5-2697v3 with 512GB
to a server Dell R730 Xeon E5-2697v4 and 768GB, the old machine was allocated both min and max server memory the value 470GB,
the new machine I set to min and max server memory the value of 740GB. But when the reindex routine started the server suddenly crashes and reboot by yourself. We send the logs to Dell and after they analyze, they said that the server was OK and the problem was caused by SQL Server when was Allocating this amount of memory.
Someone already have seen this kind of issue with SQL Server 2008 R2 ?
March 8, 2017 at 8:14 am
Is SQL Server Enterprise Edition, or Standard? You might try experimenting with max server memory (maybe set it a little lower to give the OS a slightly larger slice of the memory).
John
March 8, 2017 at 8:21 am
John Mitchell-245523 - Wednesday, March 8, 2017 8:14 AMIs SQL Server Enterprise Edition, or Standard? You might try experimenting with max server memory (maybe set it a little lower to give the OS a slightly larger slice of the memory).John
Hi John.
SQL Server Enterprise Edition with Win 2012 R2 STD.
I will do this test, lower the memory and put the reindex again and see what happens
March 8, 2017 at 8:31 am
Setting min server memory and max server memory to the same value is a bad idea. It disables a lot of SQL's automatic memory management, if the OS comes under memory pressure, SQL will not release memory, the OS will likely end up paging SQL's memory allocation to disk, causing slow downs
If you've also set SQL to lock pages in memory, then the OS can't even do that, and may well crash.
Why is the min server memory set so high?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2017 at 9:10 am
GilaMonster - Wednesday, March 8, 2017 8:31 AMSetting min server memory and max server memory to the same value is a bad idea. It disables a lot of SQL's automatic memory management, if the OS comes under memory pressure, SQL will not release memory, the OS will likely end up paging SQL's memory allocation to disk, causing slow downsIf you've also set SQL to lock pages in memory, then the OS can't even do that, and may well crash.
Why is the min server memory set so high?
Hi GilaMonster.
There´s no specific reason why min server memory is so high. I usually set 50% of amount of server memory on min server memory parameter.
I will lower the min server memory parameter and test again.
March 17, 2017 at 2:05 pm
New tests was made and the results were:
When was set to max server memory values above 512Gb of memory and the SQL Server consumed more than 512Gb, the server rebooted.
When was set to min server memory the value of 470Gb and the max server memory the value of 512Gb the server worked fine.
March 18, 2017 at 4:27 am
I typically leave min server memory at 0, unless I expect memory contention on the server and I want SQL never to reduce below a value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2017 at 7:00 pm
The same issue just happens on a SQL Server 2014, this time was allocated 512Gb to max and 256 to min. The server has 768Gb ram.
July 30, 2017 at 7:33 am
Can you post any relevant logs, SQL error log, Windows event log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2017 at 8:54 am
The crash happens yesterday at 19:57 and before that the last event on SQL Server was this below and doesnt meaning nothing.
2017-07-29 19:01:00.83 spid18s FlushCache: cleaned up 258 bufs with 248 writes in 78397 ms (avoided 413 new dirty bufs) for db 9:0
2017-07-29 19:01:00.83 spid18s average writes per second: 3.16 writes/sec
average throughput: 0.03 MB/sec, I/O saturation: 2577, context switches 5591
2017-07-29 19:01:00.83 spid18s last target outstanding: 2, avgWriteLatency 679
After that the new log file only shows info about the SQL Server startup, nothing relevant for troubleshooting.
On Event Viewer at 19:57 nothing too.
Only shows that the previous system shutdown was unexpected.
I think that maybe could be related with memory compression on windows 2012.
I will try disable the task RunFullMemoryDiagnostic and see if crashes after that.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply