SQL 2022 not using all cores

Question

SQL 2022 not using all cores

Paw Boel Nielsen 5

I am using SQL 2022 Developer edition and migrating from an older laptop (win10) with 11th gen intel to a new Dell XPS (win11) with a 13th gen intel CPU. However SQL performance on the new machine is a lot slower. Digging in it seems SQL server is not taking advantage of all the cores on the new machine.

I run a long query with select, inserts and updates to test.

Old machine:

User's image

New machine:

User's image

I run sp_readerrorlog and get this output:

User's image

All cores seem online:

User's image

Any ideas what is happening and why SQL Server is not using all cores?

Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2023-10-12T21:30:11.86+00:00

The obvious question is: what are the query plans on the new and old laptop?

There was a similar issue recently. That poster was running a natively compiled stored procedure. What sort of code you are running?

3 answers

Your answer

Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2023-10-12T21:30:11.86+00:00

The obvious question is: what are the query plans on the new and old laptop?

There was a similar issue recently. That poster was running a natively compiled stored procedure. What sort of code you are running?

Answer 1

Anonymous

Hi @Paw Boel Nielsen

You can refer to the previous thread. Hope it can help you.

https://learn.microsoft.com/en-us/answers/questions/119509/sql-server-not-using-all-cores

Best regards,

Percy Tang

Paw Boel Nielsen 5 Reputation points

2023-10-12T09:18:36.32+00:00

I have seen that thread and it is about licensing, which is not the issue here as you can see above.

Answer 2

Christoph Muthmann 181

Hi Percy,

please have a look at the capacity limits: https://learn.microsoft.com/en-us/sql/sql-server/compute-capacity-limits-by-edition-of-sql-server?view=sql-server-ver16

Standard Edition is limited to lesser of 4 sockets or 24 cores.

Oh, I've seen that you are using Developer Edition. This should use all cores. Operating system maximum!

HTH!

Paw Boel Nielsen 5 Reputation points

2023-10-12T11:04:20.75+00:00

Hi Christoph, yes so I am clearly not having a licensing issue as I am using the developer edition. And my output is showing all cores are detected by SQL and available. But not even one core is running at maximum during the long query which I find very strange.
Christoph Muthmann 181 Reputation points

2023-10-12T11:37:29.0733333+00:00
Can you please provide this data?

exec sp_configure 'cost threshold for parallelism'; exec sp_configure 'max degree of parallelism';

I'am using 50 for cost threshold and 6 for the maximum degree of parallelism for my systems.
Paw Boel Nielsen 5 Reputation points

2023-10-12T11:55:33.45+00:00

Christoph, I think I just have whatever is standard as this is what I get:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0] The configuration option 'cost threshold for parallelism' does not exist, or it may be an advanced option. Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0] The configuration option 'max degree of parallelism' does not exist, or it may be an advanced option.

But checking in SMSS I see this:

But whatever the value for parallelism I still wonder why SQL Server does not at least use one core at its maximum?
Dan Guzman 9,411 Reputation points

2023-10-12T13:24:18.15+00:00

Try setting MAXOP to use all cores:

exec sp_configure 'show',1;

RECONFIGURE;

exec sp_configure 'max degree of parallelism', 0;

RECONFIGURE;
Christoph Muthmann 181 Reputation points

2023-10-12T13:37:22.9233333+00:00

I believe you don't want to use 24 cores for a single statement.
10 means you have to parallelize the statement on 10 cores, even if 4 or 6 would show better performance. Try some numbers with your workload and use 4 with OLTP and 6 or 8 with OLAP.

So you can set the parameters in SSMS and give them a try.
Cost threshold is based on CPUs in 1998 and should be increased to 50 up to 70.
Paw Boel Nielsen 5 Reputation points

2023-10-12T14:46:38.0733333+00:00

This did not make a change to the run time of the query. As mentioned not even one core is running at maximum whereas on the old laptop all cores run maximum, so the issue seems not to be parallelism

Answer 3

Bruce (SqlWork.com) 82,146 Volunteer Moderator

In general extra cores only help with concurrent queries. Your new box does not seem to be compute bound. As you cpu and disk are probable faster I’d suspect the real issue is your anti-virus software.

Paw Boel Nielsen 5 Reputation points

2023-10-12T20:23:48.5233333+00:00

Anti virus is vanilla windows defender on both the old and new box, not sure that will interfere?
Bruce (SqlWork.com) 82,146 Reputation points Volunteer Moderator

2023-10-13T01:14:05.4866667+00:00

On your old box you probably excluded the Sqlserver process and more importantly the data files.
Paw Boel Nielsen 5 Reputation points

2023-10-16T08:08:28.5066667+00:00

Hi @Bruce (SqlWork.com) none of the machines had any exclusions, but I tried excluding the sql process and data/log folder, this did not change anything unfortunately. So I am stil scratching my head as to why the new machine does not have better sql performance, everything else is running very fast.

Share via

SQL 2022 not using all cores

3 answers

Your answer