SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
10,918 questions
SQL Server slow under Windows 11
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(51.2, 36%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESO%3C/text%3E%3C/svg%3E)
Stephen Otton
0
Reputation points
I recently purchased a new Dell XPS 15 with the expectation that I would be able to run some time-consuming SQL Server processes much faster than my old Dell Latitude. However, the new Dell XPS runs the same processes around 5x slower than the old laptop. Both laptops have SQL Server 2017 Developer installed. The only difference and it is a major difference is that the Dell Latitude is running Windows 10 Pro whereas the Dell XPS is running Windows 11 Pro. It appears that SQL Server is simply not using the CPU and memory power of the new Dell XPS laptop.
Is anyone aware of any issue between SQL Server and Windows 11 that causes performance issues?
Are there SQL Server settings that must be adjusted for the new hardware?
I thought that using the latest version of SQL Server might help. So I installed SQL Server 2022 Developer. The results were identical. As a result, my focus has returned to Windows 11.
{count} votes
-
Erland Sommarskog 87,271 Reputation points2023-09-18T21:04:27.72+00:00 The OS is not very likely to have anything to do with it. The specs of the two laptops might. I would expect the new laptop to have faster CPU, more RAM and faster disk, but I've been wrong before.
We know very little about these "processes" you are talking about. Can you give us some more detail on how you run them? We need to know so that we can help you with what diagnostics to run.
-
Stephen Otton 0 Reputation points
2023-09-18T23:44:51.42+00:00 Thank you for responding.
The older laptop is a Dell Latitude 7480 with Intel Core i7-7600 2.80 GHz 2.90 GHz, 32 GB RAM, Windows 10 Pro Version 22H2
The newer laptop is a Dell XPS 13th Gen Intel(R) Core(TM) i9-13900H 2.60 GHz, 64GB RAM, Windows 11 Pro Version 22H2
The process I run makes use of one memory optimized table and one natively compiled stored procedure that assigns values to variables by querying the table in recursion. The query is very simple with the memory optimized table inner joined to itself and one criteria in the WHERE clause based on the date column of the table.
Memory Optimized Table:
DateColumn, Column1 (smallmoney), Column2 (smallmoney), .... Column35 (smallmoney)
Natively Compiled Stored Procedure:
SET @var1 = SUM(Column1 * Coef), @var2 = SUM(Column2 * Coef), ... @var35 = SUM(Column35 * Coef)
FROM MOT as T1
INNER JOIN
(SELECT DateColumn, ((Column1 * @var1) + (Column2 * @var2) + ... (Column35 * @var35)) AS Coef FROM MOT) AS T2
ON T1.DateColumn = T2.DateColumn
WHERE (T1.TradeDate BETWEEN @ProcessDateFrom AND @ProcessDateTo)
The SQL Server version is the same on both laptops as are the databases, tables and stored procedures. The differences are the hardware and the OS.
-
Stephen Otton 0 Reputation points
2023-09-18T23:47:14.2166667+00:00 Thank you for responding.
The older laptop is a Dell Latitude 7480 with Intel Core i7-7600 2.80 GHz 2.90 GHz, 32 GB RAM, Windows 10 Pro Version 22H2
The newer laptop is a Dell XPS 13th Gen Intel(R) Core(TM) i9-13900H 2.60 GHz, 64GB RAM, Windows 11 Pro Version 22H2
The process I run makes use of one memory optimized table and one natively compiled stored procedure that assigns values to variables by querying the table in recursion. The query is very simple with the memory optimized table inner joined to itself and one criteria in the WHERE clause based on the date column of the table.
Memory Optimized Table:
DateColumn, Column1 (smallmoney), Column2 (smallmoney), .... Column35 (smallmoney)
Natively Compiled Stored Procedure:
SET @var1 = SUM(Column1 * Coef), @var2 = SUM(Column2 * Coef), ... @var35 = SUM(Column35 * Coef)
FROM MOT as T1
INNER JOIN
(SELECT DateColumn, ((Column1 * @var1) + (Column2 * @var2) + ... (Column35 * @var35)) AS Coef FROM MOT) AS T2
ON T1.DateColumn = T2.DateColumn
WHERE (T1.TradeDate BETWEEN @ProcessDateFrom AND @ProcessDateTo)
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Yitzhak Khabinsky 23,226 Reputation points2023-09-19T00:49:47.55+00:00 What about specs. for the hard drives on both machines?
-
Stephen Otton 0 Reputation points
2023-09-19T00:58:56.4933333+00:00 Old laptop disk drive: Micron 1100 SATA 512 GB
New laptop: NVMe PC801 NVMe SK hynix 2TB
-
Stephen Otton 0 Reputation points
2023-09-19T01:02:03.1333333+00:00 Old laptop: Micron 1100 SATA - 512 GB
New laptop: NVMe PC801 NVME SK hynix - 2TB
-
Erland Sommarskog 87,271 Reputation points
2023-09-19T21:00:12.76+00:00 On both laptops, run the stored procedure in a freshly opened query window.
After the procedure has completed, run this and share the result:
SELECT TOP 5 * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@spid ORDER BY wait_time_ms DESC SELECT cpu_time FROM sys.dm_exec_sessions WHERE session_id = @@spid§ -
Stephen Otton 0 Reputation points
2023-09-20T00:09:46.06+00:00 Old Laptop:
session_id wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
54 SOS_SCHEDULER_YIELD 41 8 7 8
54 MEMORY_ALLOCATION_EXT 104 0 0 0
cpu_time
15
New Laptop:
session_id wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
87 MEMORY_ALLOCATION_EXT 99 0 0 0
87 SOS_SCHEDULER_YIELD 101 0 0 0
cpu_time
0
-
Erland Sommarskog 87,271 Reputation points
2023-09-20T20:56:48.5433333+00:00 I'm a little confused here. You said that the process was time-consuming, but these numbers suggest that it ran in no time at all. Can you clarify here?
-
Stephen Otton 0 Reputation points
2023-09-21T04:37:46.3866667+00:00 I am also confused by those results. However, I can tell you that on the new laptop the stored procedure took 37 seconds to run.
-
Erland Sommarskog 87,271 Reputation points
2023-09-21T21:17:30.8366667+00:00 I don't work a lot with In-memory OLTP, but I made a test with a natively compiled procedure that ran for 30 seconds (designed to be slow). I don't see much of wait-stats, but the values in sys.dm_exec_sessions were noticeable.
Can you redo the test, with a few more columns:
SELECT TOP 5 * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@spid ORDER BY wait_time_ms DESC SELECT cpu_time, total_scheduled_time, total_elapsed_time FROM sys.dm_exec_sessions WHERE session_id = @@spidAlso make a note of the actual duration as recorded on the status bar in SSMS.
What I would also like you to do is to capture the actual execution plans on both laptops and share it in XML format. (You will need to rename the files from .sqlplan to .xml to be able to attach them here.)
-
Stephen Otton 0 Reputation points
2023-09-21T21:53:16.51+00:00 Old Laptop:
session_id wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
56 SOS_SCHEDULER_YIELD 40 11 10 11
56 MEMORY_ALLOCATION_EXT 105 0 0 0
cpu_time total_scheduled_time total_elapsed_time
0 0 0
SSMS Status bar - 15 seconds
New Laptop:
session_id wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
110 MEMORY_ALLOCATION_EXT 103 1 0 0
110 SOS_SCHEDULER_YIELD 103 0 0 0
cpu_time total_scheduled_time total_elapsed_time
0 0 0
SSMS Status bar - 38 seconds
I have been unable to capture an execution plan when I run natively compile store procedures. I choose "Include Execution Plan" from the Query menu but nothing shows up for the natively compiled stored procedures. I only see the execution plans for the sys.dm_ queries.
-
Erland Sommarskog 87,271 Reputation points
2023-09-21T21:59:48.49+00:00 Are you running these tests on SQL 2017? I did my test on SQL 2019, and I did get an execution plan.
-
Stephen Otton 0 Reputation points
2023-09-21T23:20:59.4433333+00:00 Yes, I'm running SQL Server 2017.
Was the stored procedure you got an execution plan for in SQL Server 2109 a natively compiled stored procedure?
-
Erland Sommarskog 87,271 Reputation points
2023-09-22T21:01:42.78+00:00 Yes that was a natively compiled procedure.
I would recommend that you try this:
- Go for SQL 2022.
- Set the compatibility level 160.
- Recreate the procedure, so that you have a DLL for the procedure built on your current platform.
Whether this will resolve the issue, I don't know, but it seems reasonable to try.
Sign in to comment