SQL Server 2019 on Windows 11 AMD Ryzen 9000 - Very Slow on localhost

Question

SQL Server 2019 on Windows 11 AMD Ryzen 9000 - Very Slow on localhost

asked

Kristijan G 1

I am facing an issue with SQL Server 2019 both Express & Developer version. I am not even sure this in a correlation with AMD Ryzen, but it is for sure connected with Windows 11. I'll explain why.

I've bought this laptop in July and ever since I installed SQL Server, I am facing extremely slow performance on any SQL operation (Select Table, Edit Table, Select Database, execute Query, etc.). When I got the laptop, Windows 10 Pro was preinstalled. After my personal investigation, I manage to fix this lagging issue by following this article: https://www.surfacetablethelp.com/2016/05/fix-slow-response-sql-connection-network-in-windows-10.html

Few weeks ago, I've updated my windows 10 to windows 11 and once again, I am facing the same problem all over again. I checked to see if during installation process previous steps (from the article) were deleted/overrode but no. All the changes I've made in windows 10 were/are still present. Again, I started to search online for any tip/solution, but so far nothing really helped.

Until now, I've used SQL server on localhost only (no remote connections whatsoever). Delays I am taking about are relatively small, but if you are using SQL queries and checking/updating tables all the time, it stars to be boring very fast for you to wait and wait.

I am using SSMS. Also tried Azure Data Studio, but the problem is still present.

FYI - I am using PC too with an AMD processor on Windows 10 and everything works smoothly since day one.

Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-07T21:07:19.283+00:00

Please post the output of "SELECT @@version". How long time does it take to run this query?

How much RAM do you have?

Are there any interesting errors in SQL Server errorlog? (You find it in C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\Errorlog. Note that the exact file path depends on your installation.
Kristijan G 1 Reputation point

2021-11-07T22:17:28.81+00:00

This is what I found from the log:

https://pastebin.com/z7pQvdH1

I have 16 GB of RAMs. Please notice that I've followed instructions from the link above (in my post) and it fixed all the issues I had on Windows 10.
Time per query, hmm I can't not define it because it is not that I am executing some "heavy" stored procedures manipulating with a big sets of data, we are talking about basic operations like "select top 1000 rows", "edit table" .....
Kristijan G 1 Reputation point

2021-11-07T22:19:56.703+00:00

Although, if we look at "SELECT @@version" record, we can see it is saying "...on Windows 10 Pro..." which is weird because I am currently using Windows 11.
Ramazan Kola 1 Reputation point

2022-06-07T09:37:41.947+00:00
Hello,

I think I found the solution to this problem. It worked on my computer.

Here is the solution:

Stop the sqlsrv.exe service.

Open the location of the sqlsrv.exe file.

Set the sqlsrv.exe file compatibility mode. For example "Windows 8".

Then run the service again.

Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-07T21:07:19.283+00:00

Please post the output of "SELECT @@version". How long time does it take to run this query?

How much RAM do you have?

Are there any interesting errors in SQL Server errorlog? (You find it in C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\Errorlog. Note that the exact file path depends on your installation.
Kristijan G 1 Reputation point

2021-11-07T22:17:28.81+00:00

This is what I found from the log:

https://pastebin.com/z7pQvdH1

I have 16 GB of RAMs. Please notice that I've followed instructions from the link above (in my post) and it fixed all the issues I had on Windows 10.
Time per query, hmm I can't not define it because it is not that I am executing some "heavy" stored procedures manipulating with a big sets of data, we are talking about basic operations like "select top 1000 rows", "edit table" .....
Kristijan G 1 Reputation point

2021-11-07T22:19:56.703+00:00

Although, if we look at "SELECT @@version" record, we can see it is saying "...on Windows 10 Pro..." which is weird because I am currently using Windows 11.
Ramazan Kola 1 Reputation point

2022-06-07T09:37:41.947+00:00

Hello,

I think I found the solution to this problem. It worked on my computer.

Here is the solution:

Stop the sqlsrv.exe service.

Open the location of the sqlsrv.exe file.

Set the sqlsrv.exe file compatibility mode. For example "Windows 8".

Then run the service again.

Answer 1

answered

Erland Sommarskog 67,481 Microsoft MVP

I see that you have the RTM version or SQL 2019. This can explain why it talks about Windows 10 - Windows 11 had not been invented at that time.

You should download and install the most recent Cumulative Update, which is CU13. I cannot promise that this resolves the issues you are seeing, but at this is a build that - I presume - is aware of Windows 11.

Kristijan G 1 Reputation point

2021-11-07T22:39:57.74+00:00

I've just downloaded CU13 update, but unfortunately, this error is still present.
Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-07T22:41:52.037+00:00

Which error? The wrong OS in "SELECT @@version" or the poor performance?

Answer 2

answered

Kristijan G 1

Both actually. But I just checked with a colleague of mine from work, he literally has the same process as I do and the same laptop and windows brand, just different model.

He has no issues with poor performance and query "SELECT @@version" shows Windows 10 Pro as a result to him as well.

Dan Guzman 6,926 Reputation points

2021-11-08T00:18:14.937+00:00
Don't be concerned with the Windows version display name. The build number distinguishes it as Windows 11. I see the same on my machine, and performance is good (or I should say excellent because I had to upgrade my desktop to run Win 11).

Some Windows 11 APIs still return Windows 10, although this may change at some point. To wit, PowerShell command Get-ComputerInfo | Select WindowsProductName, OsHardwareAbstractionLayer | Format-Table returns the output below on my fresh Windows 11 install:

WindowsProductName OsHardwareAbstractionLayer ------------------ -------------------------- Windows 10 Pro 10.0.22000.1
CathyJi-MSFT 20,651 Reputation points Microsoft Employee

2021-11-08T08:02:40.527+00:00
Hi @Kristijan G ,

You can find out the version number of your Windows version as follows:

Press the keyboard shortcut [Windows] key + [R]. This opens the “Run” dialog box.

Enter winver and click [OK].

For SQL server poor performance, did your network have any latency? Did your environment have any resource issue? Did you monitor resource usage?
Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-08T22:30:34.643+00:00
So did I get it right, even "SELECT @@version" runs slow?

What I don't get with the link in your original post is that I don't see how that would matter, if you are running against SQL Server on your own laptop. What does this query return:

SELECT endpoint_id FROM sys.dm_exec_sessions WHERE session_id = @@spid

If you don't mind, I have a question, completely unrelated to your problem, but can you check in Services if you see this service? I ask this, because a poster in another thread who also has updated to Windows 11, have a problem related to this service. (And I have yet to upgrade to Win11 myself.)
Kristijan G 1 Reputation point

2021-11-08T23:01:22.24+00:00

I don't know how this link from my original post successfully solved my poor performance issue, but it did. Since I did that back on Windows 10, I had 0 problems with SQL performance.

Secondly, I have executed that query of yours and it returned this:

And lastly, I can confirm I have this AzureAttestService up and running.
Kristijan G 1 Reputation point

2021-11-08T23:03:22.717+00:00

And unfortunately, my poor performance issue persists. I literally tried everything but reinstalling Windows. Although I am not sure this will fix the problem anyway.
Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-08T23:03:47.457+00:00

Endpoint id 2 is TSQL Local Machine, which is shared memory. That is, it is not involving TCP/IP.

So what is the response time for running "SELECT @@version"?

Thanks for the answer to AzuerAttestService, much appreciated!
Kristijan G 1 Reputation point

2021-11-10T22:42:58.227+00:00

Dead Erland, response time is 0 ms lol

About this part "That is, it is not involving TCP/IP.", is that bad or? Because now I am experiencing delays (poor performance) even inside the apps I am using/Developing.
Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-10T22:46:17.727+00:00

And did it feel like 0 ms to you?

That is, I'm trying to narrow down whether your queries are slow, or something is wacko with your setup so that everything takes time.

For a slightly more complicated query, try SELECT * FROM sys.objects.
Kristijan G 1 Reputation point

2021-11-10T23:00:36.54+00:00

Honestly, the first execution not, but every after, it felt like 0ms (probably because it was cached or something similar).

I've recorded a video so you can see what I am taking about. If you pay attention, you will be able to notice (you will not see my right clicks because windows recorder ignores them lol) delays and poor performance for any kind/type of operation.

Video:

https://drive.google.com/file/d/1vfvyoF7QXka7BLBASDnM4PECJgbQPd8g/view?usp=sharing
Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-11T22:41:14.497+00:00

I watched the video, and it seems to me that connection to a new window is slow. For instance, when you expanded the query tree in Object Browser that was quick.

I noticed that the SELECT TOP 100 thing took some time, but in the status bar, SSMS said that zero seconds.

I don't really know what might be going on, but what happens if you attempt to connect from a second machine?
Kristijan G 1 Reputation point

2021-11-12T07:18:13.623+00:00

Not sure if I understood you. What do you mean by "if you attempt to connect from a second machine"? I am connecting to my local dB (localhost).
Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-12T07:42:00.227+00:00

Yeah, I got that. I was thinking that if you have a second computer you could try to connect from that one. And vice versa, if you have SQL Server installed on the other box. The reason you would try this is simply troubleshooting to analyse the problem. Then again, if you don't have a second machine to test with, you don't.

What you also can try is to add tcp: in front of the server name, so that you are connecting over TCP rather than shared memory.
Kristijan G 1 Reputation point

2021-11-12T08:20:45.42+00:00

Maybe I can do that, but I'll need day or two to arrange everything.

About adding "tcp:" in front of the server name, I've just tested and everything is the same lol. I literally see no reason for this stupid/slow behavior. Maybe after all I will need to reinstall the windows itself.
Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-12T16:08:39.473+00:00

Another thing to try is try to use Process Monitor to figure out what might be going on. It's certainly tedious and advanced and can send you down a rabbit hole. Re-installing Windows may be faster.

And, oh, did I ask this: what anti-malware software do you have? Anything else than Windows Defender? Or any other software that installs a filter driver?
Kristijan G 1 Reputation point

2021-11-12T22:25:25.967+00:00

Only thing that comes up my mind is Lenovo Vantage (https://support.lenovo.com/us/en/solutions/ht505081-lenovo-vantage-using-your-pc-just-got-easier).

Because I am using Lenovo Yoga, this Lenovo assistant comes along with the laptop.

Maybe I could try to uninstall it and try some SQL queries again? But honestly, I am not sure it will work...
Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-12T22:40:53.157+00:00

I also have a Lenovo laptop, if a Thinkpad, and I have not seen this. Lenovo has some funky software that sometimes gets more in the way than being helpful, and I think I uninstalled some of that on my previous Lenovo.

But I would not expect that to be the culprit. But if you have another anti-malware program than Windows Defender, I would look suspicious at that one.

Answer 3

answered

Kristijan G 1

Finally, some good news. I've just reinstalled windows 11 and first program I've installed was SQL Server and SSMS. And FINALLY, everything works smooothly.

I think all of my problems are solved :)

Erland Sommarskog 67,481 Reputation points Microsoft MVP

2021-11-16T07:49:20.273+00:00

Glad to hear that it worked out, even if with more work that you might like to put in.

I think you should convert your last comment to an Answer (under the More menu), and then accept that to close this thread.

Answer 4

SOLUTION

After changing multiple different machines, Windows and SQL versions, I finally manage to discover what was the source of my problem.

Long story short, it was all about the Windows authentication.

On the first picture, I used Windows authentication and the connection was very slow, but once I changed it to the SQL Server Authentication and entered credentials which I set up during the installation process, all those lag/delay issues were gone.

At that time, I've started digging what could be the problem. Finally, the solution was:

open this file in notepad/notepad++ as an ADMINISTRATOR C:\Windows\System32\drivers\etc
add this line 127.0.0.1 KRISTIJAN-TRLEN but replace this ** KRISTIJAN-TRLEN** with your SQL server name. You can check your SQL server name by executing this query SELECT @@SERVERNAME

After adding this line, everything was working smoothly

Erland Sommarskog 67,481 Reputation points Microsoft MVP

2022-02-24T22:01:00.643+00:00

Thanks for the update! Glad to hear that you got it working. I don't really see why this would be necessary, but as long it works, it works.
Stefano Delpero 1 Reputation point

2022-03-23T09:00:33.183+00:00

Thank you!!!
Now SSMS is very fast. Before it was unnerving at how slow it was.

SQL Server 2019 on Windows 11 AMD Ryzen 9000 - Very Slow on localhost

4 answers

Activity