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:
*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:
- Is there some difference in how Locks, latches, or page splits are handled on an AMD vs Intel Processor?
- Is there some difference in how Locks, latches, or page splits are handled on a Single Socket vs Multiple Sockets?
- 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)
- 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!
UPDATE 1
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.
UPDATE 2
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).
SELECT * FROM sys.database_scoped_configurations) is the same on both.