So we've been using a workhorse of a DB server for just over 7 years now, last year we began easing into a new server that is essentially better in every way, transitioning one database at a time. There is one database in particular that we moved to the new server and things got bad...

When under even low-moderate load, queries began timing out, performance on this database and others that had already been transitioned were noticeably slower in almost every way until we transitioned back to the original server. Once we did, performance on the new server stabilized and the problem database was just fine on the original server.

Some specifics would be helpful!

Specs on our current (aging) server:

  • Dell Poweredge T640
  • Dual Intel Xeon Gold 5120 processor (2.20GHz, 56 cores total)
  • 512 GB RAM
  • NVMe RAID 1 for C (OS), HDD RAID 10 [8 disks] for D (SQL Data), SSD RAID 1 for L (SQL Logs), SSD RAID 1 for T (TempDB)
  • Windows Server 2016 DataCenter
  • SQL Server 2019 Enterprise

Specs on our new server:

  • Dell PowerEdge R7515
  • Single AMD EPYC 7H12 processor (2.6GHz, 64 cores total)
  • 1024 GB RAM
  • NVMe RAID 1 for C (OS), SSD RAID 10 [10 disks] for D (SQL Data), SSD RAID 1 for L (SQL Logs), NVMe RAID 1 for T (TempDB)
  • Windows Server 2022 Standard
  • SQL Server 2019 Enterprise

SQL Server Config

Server configuration is nearly identical, except where makes sense due to hardware variances (Max RAM). Here are screenshots: General Config Memory Config Proc Config Security Config Connections Config DB Settings Config Advanced Config

*Enable Contained Databases is not enabled on the new server, however, we don't utilize this feature.

**Before the comments start, I realized the CTFP is outrageous, that's a whole other ball of wax.

In particular, when reviewing our monitoring tools we've noticed unproportionate values when comparing the following metrics obtained from sys.dm_os_performance_counters:

  • Transactions
  • Lock Requests/sec
  • Lock Timeouts/sec
  • Average Latch Wait Time (ms)

Happy to provide additional details/graphs if it will help.

The database in question is of decent size, but large for us (120 GB), but has many active writers in an OLTP application. There are a lot of Page Splits involved with this DB in particular.

Academically, everything about the new server should be able to handle the load of the old server, and then some.

All this info to ask these questions:

  1. Is there some difference in how Locks, latches, or page splits are handled on an AMD vs Intel Processor?
  2. Is there some difference in how Locks, latches, or page splits are handled on a Single Socket vs Multiple Sockets?
  3. Are there any SQL Data structures that may behave differently between the servers? (this is the only database we make use of Column Store indexes, filtered indexes, and a few other newer/fancy constructs to help speed things up)
  4. Are there other factors that should be would cause a database to behave so differently server to serer?

Thanks in advance for joining me in the crazy!


We run Ola Hallengren's amazing SQL Server Index and Statistics Maintentnce script nightly, reorganizing each table at 5% fragmentation and rebuilding at 30% fragmentation AND Updating INDEX statistics. This is run by SQL Agent on each server with no errors.


After taking some time to build out ways of replicating the load we were setting without impacting the customer, we started testing out various theories. The winner was @StrayCatDBA who called it with the power settings. Balanced power options ended up parking many of our cores, the server was never under enough load to get past this throttling, but it was enough to drive the server to struggle enough to impact workloads.

I thank everyone who took some time and chimed in. Some of the feedback has been helpful in the short term and some of it has helped to start the process of refactoring some of our more "aggressive" queries (and the practices required to compensate).

  • 1
    Did you verify the execution plans are the same on the old/new server? Make sure the problem database compatibility level and database-scoped config (SELECT * FROM sys.database_scoped_configurations) is the same on both.
    – Dan Guzman
    Commented Dec 29, 2023 at 21:30
  • 1
    ...even when nothing else changed but the server itself. The SQL engine is complex, there are many variables at play, and the execution plan your query is getting one day can be completely different and change to something that's regressed and worse another day, even on the same server with no apparent changes. When there's a performance issue, comparing execution plans is almost always the first place to look which will then highlight what that issue is, whether it's a performance tuning issue or at least to show you what the wait stats are so you know where to look next.
    – J.D.
    Commented Dec 30, 2023 at 14:45
  • 1
    Side note: "We run Ola Hallengren's amazing SQL Server Index and Statistics Maintentnce script nightly, reorganizing each table at 5% fragmentation and rebuilding at 30% fragmentation AND Updating INDEX statistics." sounds like a bad idea. Fragmentation is much less of an issue on SSDs, and reorganizing is anyway mostly a pointless exercise. Wait til it's seriously fragmented and just rebuild. Commented Dec 30, 2023 at 20:26
  • 1
    "In an Entity Framework world without full query logging, it gets messy." You can use an XEvents trace to get the long running queries, then run them in manually in SSMS "Query tuning also does not help explain the differences between servers, which is key here. I need to approach these as two distinct matters." So you say, but I've seen no evidence that it's not the case. The compiler certainly might compile differently, if statistics are slightly different etc. EF is known to often produce poor plans, and needs to be coaxed to get the right result. Commented Dec 30, 2023 at 20:29
  • 2
    You say "we don't make use of QueryStore" like this is some immutable fact. Why don't you enable it? Personally rather than wonder about what's different I'd likely just see if there are specific problematic queries that are using lots of resources on the new server - and if so you can start investigating those and either just tune them if they are doing something obviously inefficient or at least have something specific to compare with the other server Commented Jan 2 at 13:29

1 Answer 1


Confirm power settings are set to "high performance", especially on machines with a large number of CPU's.

"Balanced" power setting will throttle down CPU's to save energy and in theory un-throttle them under load. On a 64 CPU box, 10 CPU's at 100% is only ~15% overall load, which may not be sufficient to un-throttle things.

This behavior results in horrible performance for single queries at really low loads.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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