SSMS22 Application Itself Slower Than SSMS21 (1 Viewer)

[dalski]

Have many experienced this? I'm not talking about through Access or Query performance, I mean the actual application SSMS22 itself is extremely slow. Through Access is quick but I'm working in SSMS most of the time converting the BE for Access.
Seems there are quite a few complaints with the newer version itself being considerably slower than the the previous. I'll stay away from ver-25 for sure during beta period, but research indicates MSN have problems with it due to fully moving to x64bit system...

I think it's to do with OLE ODBC; as despite being < 1 year old computer & using MS365 it did not have ODBC Driver Ver 18; had to install manually. When I was installing I got a prompt about ODBC drivers not being present (despite them being present). Recommendations were to delete all OLE drivers, uninstall SSMS-22 & reinstall. Which I have done. Performance seems better on first few minutes of playing around but the Diagram view is still slow.

Specs

• SSMS22 Managed Instance Of Azure
• RAM 32 GB (50% consistent use); pretty sure this has increased considerably since installing/ using SSMS (new to it); pretty sure this is due to amass of services now running but not too worried about this atm
• CPU - hardly anything ever (2.5 GHz available)
• WiFi - nowt; circa 8Kbps/ 30Kbps rare

Diagram View

• Add Relationship
• 30 secs to load Table & Column Definition
• 50 secs to write changes
• 3 minutes to save relationship changes (sometimes hangs requiring restart sometimes)

Concurrence

• ODBC Driver Fixed - Karl Donaubauer
• SSMS 22 Keeps Getting Worse By Brnet Ozar
• Troubleshoot Slow Db/ Application MSN

I know it would be quicker to use a local instance during development, but I understand I'm likely to run into problems on deployment. As I'm so inexperienced I do not think I would be capable of identifying the issues so would rather suffer a slow Azure development I think.

 

[sonic8]

I think it's to do with OLE ODBC;

Why do you think that?
SQL Server Management Studio to my knowledge neither uses OLEDB nor ODBC.

as despite being < 1 year old computer & using MS365 it did not have ODBC Driver Ver 18; had to install manually.

This is expected. The new SQL Server ODBC drivers are not installed on Windows by default.

 

[dalski]

Hi @sonic8 , I'm usually wrong & know very little, but I thought I saw some posts with people having issues with the ODBC. I thought ODBC strings is how we connect. Well if that's your assumption given your knowledge I'm certainly wrong. I got a prompt on installing the new app about OLE/ ODBC. I read somewhere to delete existing ones & let the new installation take effect. I've definitely seen an improvement with this. Appreciate I could be well off-course & is just the new installation which has helped. I thought I saw some threads elsewhere saying this helped also.

This is expected. The new SQL Server ODBC drivers are not installed on Windows by default.

Thanks, that's reassuring as I thought I was in trouble.

Thank you for your videos btw; I feel like I'm talking to a celebrity . Really enjoyed this one, posting link to boost SEO & awareness, please advise if undesired & I'll remove; just trying to help is all.

Maybe my performance on Diagrams is normal & I have unrealistic expectations? What version do you use/ prefer? I have seen a big improvement with general speed of the app in all other areas since reinstalling.

 

[tvanstiphout]

Diagram View

• Add Relationship
• 30 secs to load Table & Column Definition
• 50 secs to write changes
• 3 minutes to save relationship changes (sometimes hangs requiring restart sometimes)

These are all instantaneous for me, with a local SQL Server.

 

[dalski]

Thanks Tom, that's helpful. I knew Azure would be slower but surprised with Diagrams atm. I hardly have any data in the db but through Access it's very quick but we'll see how it goes when I get a bit more done. Still a lot to do on the development side.

 

[tvanstiphout]

I just tried it with a basic Azure db, and it was also almost instantaneous.

 

[dalski]

Thanks Tom, really good of you; interesting, I definitely have a problem which I should solve then.

 

[GPGeorge]

I've often noticed that creating diagrams in remotely hosted SQL Server or Azure SQL can be slow in SSMS, and not just in the most recent version. However, once a diagram exists, it usually loads noticeably faster.

The times you mention do seem longer than I'd expect.

 

[dalski]

Thanks George, I've also tried:

• Disabling Publisher Certificate Revocation Check in Windows
• Modified Host File For Certificate Revocation; C:\Windows\System32\drivers\etc\hosts adding 127.0.0.1 crl.microsoft.com to bottom of file
• No luck on above tried resetting connection settings of SSMS 22

It's early days, I've definitely seen an improvement on reinstalling so I can handle the Diagram view for now, I have around 50 tables with a lot of relationships but I would think this is nothing to what you guys get up to. Thanks all for your help, I'll keep updating on my progress to help others in future.

 

[tvanstiphout]

Why do you think that?
SQL Server Management Studio to my knowledge neither uses OLEDB nor ODBC.


This is expected. The new SQL Server ODBC drivers are not installed on Windows by default.

WRT the connection used by SSMS: it says so on the Connect dialog: ADO.NET

 

[dalski]

Update - I dropped & recreated the db; faster opening an object from Object Explorer (1.5 secs).
Then added a relationship in the Table Design View; took around 30 secs to load up initial GUI (not Diagram) then after selecting columns 40 secs to add relationship. Then the Diagram took around 90 secs to refresh. This still sounds bad I think. Around 30 secs to save a change to table design. 10 secs to expand Tables in Object Explorer, 6 secs to Edit Table Top 200 Rows with no records.

Tried contacting Azure but where I'm on Trial no support is available & got a link to this thread . I would just join but don't want to throw away my £150 trial. The Support portal is hideous with many different topics, hard to navigate for an amateur.

SQL Server Management Studio to my knowledge neither uses OLEDB nor ODBC.

WRT the connection used by SSMS: it says so on the Connect dialog: ADO.NET

https://learn.microsoft.com/en-us/a...sql#recommended-versions-of-drivers-and-tools

Microsoft seem to contradict themselves on ODBC v17 to be used on the managed instance with 17 in an article & default settings in Azure of v18.

 

[tvanstiphout]

"... recommended MINIMUM versions ..."

 

[tvanstiphout]

"£" seems to indicate you may be in the UK.
Make sure your Azure database is in a data center near you, not in say Asia or USA.
As an example, on the portal home page I hover over Resource group, and it shows my resources correctly to be in West USA:

As another example, here I am in a command window, pinging the SQL Server. The "time" of <50 ms is fast.
C:\Users\tvans>ping tomplayboxserver.database.windows.net
Pinging cr13.westus2-a.control.database.windows.net [20.51.9.130] with 32 bytes of data:
Reply from 20.51.9.130: bytes=32 time=47ms TTL=251
Reply from 20.51.9.130: bytes=32 time=48ms TTL=251
Reply from 20.51.9.130: bytes=32 time=49ms TTL=251
Reply from 20.51.9.130: bytes=32 time=48ms TTL=251

I'm hoping this will help you figure out the bottleneck. What you are seeing is NOT normal.

 

[dalski]

Thanks Tom & for the reasurrance something is wrong. I must fix it now as it's slowing me down too much.

Yes I was set to relevant region (UK). I'm experiencing identical issues to this thread. So I changed Connection Protocol to TCP/IP & checked Trust Certificate; still no joy. Ping results are fast. Changed Packet Size to 8192. Amongst many other things... At a loss what to do.

 

[tvanstiphout]

One more suggestion: SSMS > Tools > Import and Export Settings > Reset all Settings.

 

[dalski]

Thanks Tom, I would've thought that would've got done when reinstalling but giving it a go I got warnings with GitHub. Just in case somewhere is aware of anything like this conflicting with my setup atm I post. Still got 10 secs to expand the Tables in Object Browser.

Trying LogHunter I noticed I was not sysadmin privileged. Haven't figured out how to change this yet.

• Running SSMS as Administrator (can't just be this as it didn't help previously & better now)
• Connection Settings:
  • via SQL Server authentication (as has always been)
  • Encryption (Mandatory [have tried previously & different types])
  • Trust Certification checked (tried many times)

Seems a big improvement.

1. Object Browser expanding Tables (initial opening) 3 seconds (much better; was 10 secs)
2. Diagram View circa 80 secs initial launch (25 secs after)
3. Add new column to existing table & save - 20 secs

But the worst thing is I don't know why this is, what actually changed for future reference. After SSMS > Tools > Import and Export Settings > Reset all Settings I closed & reopened several times; with no benefit. But I'm seeing an improvement now & don't know why. Is there a lag on this maybe; like a Domain Source Record on the internet it gets cached & can take 24 hours to see the change?

Is this normal performance now?