Apart from some Windows Hotfixes are there any special considerations (i.e. "things to worry about") when running SQL on a server with 512 GB RAM and 64 logical cores?

I'm aware of some guidance when operating above one or both of these thresholds but not right at them.

OS: Windows Server 2008 R2

SQL Server 2008 R2 SP3 (clustered)

  • I agree with @LowlyDBA - I would be concerned the number of months the MS is going to continue support for this product. This has to be an enterprise edition, because standard is capped at 64 gigs Memory Supported by the Editions of SQL Server. My only concerns is the cost to license this box. The first 4 cores you have to pay for, and the additional 60 cores end up being 30 additional licenses. If I recall right enterprise is around $17,000 per license. So you are looking at roughly $527k for this server. – H.79 Mar 9 '18 at 22:39
  • Not a new build. Just started at a new job. Thanks for the reminder about EoL! – SQL_Deadwood Mar 9 '18 at 22:42
  • 1
    You mention that this is clustered: do you have two physical boxes with the same configuration on both, or is this virtualized? – Randolph West Mar 10 '18 at 6:40
  • This is a physical server (possibly two) but, you know, I'm honestly not sure about the configuration. I've been kept busy with a more pressing issue but this is a good reminder. – SQL_Deadwood Mar 29 '18 at 8:28

Max Server Memory should be no higher than 445 GB. This gives Windows enough memory to manage itself. Yes, it really does need that much.

Max Degree of Parallelism should be set to the number of logical cores in one NUMA node, up to a maximum of 8.

If it's running an OLTP environment, make sure Optimize for Ad Hoc Workloads is enabled.

Ensure power saving on the OS is set to High Performance.

TempDB should be on your fastest storage, followed by transaction logs.

There's a whole lot more you can look at, but these are the main items.

As some comments have noted, SQL Server 2008 R2 is out of mainstream support. Extended support ends in July 2019 (not this year), so there's just about enough time to start a project to move to a newer version. I would be looking firmly at SQL Server 2017 or later.

| improve this answer | |
  • Oops! Nice catch, I misread the year on the date. Removing my comment about it expiring this year for extended support. – LowlyDBA - John McCall Mar 10 '18 at 13:17
  • No worries. I figured it was a typo. – Randolph West Mar 10 '18 at 18:52
  • Thanks, Randolph! The items you mentioned were some of the first changes I made to this server a few months back. Though I'll have to take a look back to see where max mem ended up. It's going to be quite the task rearchitecting this workload for a newer system, though CPU usage is always really low (as in <5%) so part of it could just be the older hardware paired with an unfathomable number of databases. – SQL_Deadwood Mar 29 '18 at 7:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.