SQL Server Developer 2022: queries running too slow

Question

SQL Server Developer 2022: queries running too slow

Fer C 5

Our team is building an application on top of SQL Server. We used to work with SQL Server Developer 2019, running on Docker. At that point, the queries run at an expected pace considering the hardware we have at our disposal: 12GB Ram, and a i5 11th generation Intel Core (4 cores, 2.4 GHz). That means, a 2 or 3 seconds max for big queries.

Last week we moved to SQL Server 2022 Developer edition and we moved Docker away. The server connects directly to the SQL Server on Windows. For small queries, the running became much faster, but for complex queries, much slower (in some case cases, 10 or more seconds for the same big queries). We notice that SQL Server is using less than 1GB ram (while in Docker we could assign around 4 GB). The processing capacity of the computer is also underused. The impression is that SQL Server is not using the full capacity of the hardware.

Why is that? According to the description of the SQL Server Developer edition, there should be no restrictions ...or does it have? How could we improve the performance?

Thanks

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Erland Sommarskog 135.6K Reputation points MVP Volunteer Moderator

2023-05-29T21:08:30.7533333+00:00
There are not very many hard facts in your post to give concrete answer. Let me first ask a few questions:

Did you have Query Store enabled when you were on SQL 2019?

What compatibility level do you have now?

What does sp_configure 'max server memory' return?

Generally, SQL Server grabs as much memory as it needs and as it can. So if SQL Server only allocates 1GB of memory, that would be one of:

The workload do not need more memory.

There are other processes in the machine competing for memory.
Fer C 5 Reputation points

2023-05-30T00:13:46.6866667+00:00
Thanks a lot for your reply.

When using SQL 2019, we did not enable Query Store. Now in 2022, we do.

The compatibility level is 150

Max server memory is: 2147483647

Agree, the queries are complex, but the workload does not need more memory.
Erland Sommarskog 135.6K Reputation points MVP Volunteer Moderator

2023-05-30T21:16:41.48+00:00

Pity that you did not have Query Store enabled in SQL 2019. Then we could have dug out hard data about performance differences and investigated differences in query plans.

Since you are compat level 150, you have the same optimizer as in SQL 2019, so you are not a victim the changes in the optimizer back-firing. But there can still be plan differences for various reasons. (Of which not all are related to the version change.)

I guess then, we don't have much other options than to ask you to share a sample query that is slow together with the actual execution plan for that query. You can upload plans on http://www.pastetheplan.com.
Fer C 5 Reputation points

2023-05-31T02:23:30.47+00:00

Hi Erland, I found something interesting:

I uploaded the same database to Azure and picked a standard database for testing (10 DTUs)

"Complex" Queries take when running in the localhost up to 7 seconds for completion. In Azure, a fraction of a second. That is similar to what I experienced when running in Docker vs directly from Windows 11.

How could be that possible?
Erland Sommarskog 135.6K Reputation points MVP Volunteer Moderator

2023-05-31T21:00:01.47+00:00

Did you compare the query plans? I would be surprised if they are the same.
MarcusNilsson-8584 0 Reputation points

2026-01-22T17:18:44.6266667+00:00

I'm experiencing the same issue.

Running query on an Azure Database S6 (400 DTU) yields a query to execute in 10 seconds.

Running locally on 2022 Developer Edition = 40 seconds.
Local laptop: quick SSD, 32 GB RAM, 20 core CPU
~7% CPU while running query
Disk I/0 really low while running query (peaks at 4-5 MB/sec, but 99% of time at < 5KB/sec)
SQL Server using < 4 GB RAM workingset with 15 GB available

Is Developer edition restricted ("capped") ?
Erland Sommarskog 135.6K Reputation points MVP Volunteer Moderator

2026-01-22T22:10:55.1+00:00

No, Deverloper Edition is not capped. The fact that you have 7% of CPU suggests to me that you have a parallel plan. If you have further questions, please ask a new Question. Piggybacking on old threads do not work well.

1 answer

Erland Sommarskog 135.6K Reputation points MVP Volunteer Moderator

2023-05-29T21:08:30.7533333+00:00

There are not very many hard facts in your post to give concrete answer. Let me first ask a few questions:

Did you have Query Store enabled when you were on SQL 2019?

What compatibility level do you have now?

What does sp_configure 'max server memory' return?

Generally, SQL Server grabs as much memory as it needs and as it can. So if SQL Server only allocates 1GB of memory, that would be one of:

The workload do not need more memory.

There are other processes in the machine competing for memory.
Fer C 5 Reputation points

2023-05-30T00:13:46.6866667+00:00

Thanks a lot for your reply.

When using SQL 2019, we did not enable Query Store. Now in 2022, we do.

The compatibility level is 150

Max server memory is: 2147483647

Agree, the queries are complex, but the workload does not need more memory.
Erland Sommarskog 135.6K Reputation points MVP Volunteer Moderator

2023-05-30T21:16:41.48+00:00

Pity that you did not have Query Store enabled in SQL 2019. Then we could have dug out hard data about performance differences and investigated differences in query plans.

Since you are compat level 150, you have the same optimizer as in SQL 2019, so you are not a victim the changes in the optimizer back-firing. But there can still be plan differences for various reasons. (Of which not all are related to the version change.)

I guess then, we don't have much other options than to ask you to share a sample query that is slow together with the actual execution plan for that query. You can upload plans on http://www.pastetheplan.com.
Fer C 5 Reputation points

2023-05-31T02:23:30.47+00:00

Hi Erland, I found something interesting:

I uploaded the same database to Azure and picked a standard database for testing (10 DTUs)

"Complex" Queries take when running in the localhost up to 7 seconds for completion. In Azure, a fraction of a second. That is similar to what I experienced when running in Docker vs directly from Windows 11.

How could be that possible?
Erland Sommarskog 135.6K Reputation points MVP Volunteer Moderator

2023-05-31T21:00:01.47+00:00

Did you compare the query plans? I would be surprised if they are the same.
MarcusNilsson-8584 0 Reputation points

2026-01-22T17:18:44.6266667+00:00

I'm experiencing the same issue.

Running query on an Azure Database S6 (400 DTU) yields a query to execute in 10 seconds.

Running locally on 2022 Developer Edition = 40 seconds.
Local laptop: quick SSD, 32 GB RAM, 20 core CPU
~7% CPU while running query
Disk I/0 really low while running query (peaks at 4-5 MB/sec, but 99% of time at < 5KB/sec)
SQL Server using < 4 GB RAM workingset with 15 GB available

Is Developer edition restricted ("capped") ?
Erland Sommarskog 135.6K Reputation points MVP Volunteer Moderator

2026-01-22T22:10:55.1+00:00

No, Deverloper Edition is not capped. The fact that you have 7% of CPU suggests to me that you have a parallel plan. If you have further questions, please ask a new Question. Piggybacking on old threads do not work well.

Answer 1

Anonymous

Hi @Fer C

There is a document on troubleshooting slow queries on SQL Server, which you may use as a reference: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-slow-running-queries.

Best regards,

Aniya

0 comments