0

Environment

We have a legacy system with a MS SQL Server 2012 database, which should be migrated to a new server with MS SQL Server 2022. Specs of the new system:

  • vServer with dedicated processor cores and memory
  • Processor: AMD EPYC 7702P, 1 Sock, dedicated 14 Cores for our server
  • 64 GB RAM
  • Windows Server 2022 Standard
  • MS SQL Server 2022 Developer (on current Dev system)
    • Memory: max. 32768 MB
    • Cost Threshold for Parallelism: 50
    • MAXDOP: 8

We restored the database on this system and set the compatibility level to "SQL Server 2022 (160)".

Problem description

The database has a Stored Procedure, which updates a mass of data and produces some load on the CPU. It runs for ~15min and didn't cause other SQL queries to run slower on the legacy system. Also on the new server in approx. 50% of all runs, the most load is distributed between the first 3 CPU Cores and everything works fine in this case (no lags on other queries):

OK CPU distribution

But in the other half of all runs, the system does not behave in this way. Instead, another CPU Core (which is not CPU Core 0-2) is fully utilized and the load is (nearly) not spread to other CPU Cores:

Not OK CPU distribution

In this situation, all other queries (even the simplest SELECTs) show massive delays. E.g. a simple query that normally returns in 0ms now runs up to 300ms, which is a showstopper for our business.

We could not find any info about this problem or how to fix it. A temporary -while not ideal- fix (which we found by trial-and-error) seems to be to manually set the "Processor Affinity" of CPU0-CPU5 and leave the other CPU Cores out. When we also activate the trace flag 8002, the load is always distributed between the first 3 cores (as was the "ok behavior"), but of cause, this "solution" is not scalable.

1
  • 2
    What code is the procedure doing, what is its query plan? What are the indexes etc? Oct 5, 2023 at 18:13

1 Answer 1

4

We could not find any info about this problem or how to fix it.

This is an XY issue, essentially, which is why you aren't "finding" any real resources on it.

But in the other half of all runs, the system does not behave in this way. Instead, another CPU Core (which is not CPU Core 0-2) is fully utilized and the load is (nearly) not spread to other CPU Cores

This is most likely (since we don't have any other data) the difference between the specific query being executed in the stored procedure running serially versus parallel, though it could easily be other things (again, no data).

Typically after 8 cores per socket, SQL Server will use automatic soft numa in SQL Server 2016+ which will change the node setup in SQL OS. This may be a behavior change you're not used to, running on SQL Server 2012 if you already didn't have multiple NUMA nodes or create your soft-numa layout. This isn't the problem, though, just that task behavior for execution will change from how it was in 2012 with the addition of soft-numa.

If a single CPU being under load causes massive issues then query tuning would be the first step while also investigating any issues into the virtualization layer - such as oversubscription.

Without more data it's not going to be possible to help further, such as execution plans, wait stats, etc.

As an aside, I haven't found AMD processors to be particularly good performers with SQL Server.

Your Answer

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