Solved SSMS22 Application Itself Slower Than SSMS21
- Thread starter dalski
- Start date
I've got plenty to spare, it must be something else.
Editing objects was really slow in using an Azure Db for me. Things definitely improved as stated in recent posts thanks to input from forum. However it was too slow to continue developing. I changed to a local instance of SSMS 22 with near instantaneous Object Explorer now with exception to Diagrams which take around 10 secs or so on initial load.
Editing objects was really slow in using an Azure Db for me. Things definitely improved as stated in recent posts thanks to input from forum. However it was too slow to continue developing. I changed to a local instance of SSMS 22 with near instantaneous Object Explorer now with exception to Diagrams which take around 10 secs or so on initial load.
Last edited:
Albert D. Kallal
Active member
- Local time
- Today, 16:10
- Joined
- Jun 11, 2009
- Messages
- 137
I just installed SSMS22 on my laptop (an older one).
It seems to run just fine (loading wise is same, or even somewhat better then SSMS21).
To be fair, SSMS (recent editions) is perhaps the slowest loading application I have!!!
So, my current laptop?
4ghz, 6 cores, (12 threads), 24 gigs ram.
And I find that SSMS22 seems to be actually better then previous 21......
If I loaded SSMS one time (say during the day), and then re-load again?
I SSMS comes up in 5.8 seconds. Connection screen comes up about 4-5 seconds later.
Hence this:
So, if anything, I find 22 loads a bit faster then 21....
Edit:
So, I would test/try against a local running copy of SQL server. Opening tables in design view, or opending diagrams occures without any real delay.
This suggests something else is at play here - perhaps domain authentication or something else......
So, as a FYI, I'm not seeing any real "new" delays by using SSMS 22. However, as above screen video shows, SSMS for the recent editions does indeed load slow, or better stated slower then just about anything else I run on my computer - even the big whopper Visual Studio loads faster then SSMS.....
But, you should not be seeing such delays. As noted, I would test/try against a local running edition of SQL server. Once SSMS is loaded, then general operations occur without any real delays....
R
Albert
Last edited:
Thanks for doing that Albert, really appreciated. Yes must've been Azure 's latency on the most basic of Resource Group package spec... As per nr-22 ditched Azure for the time being as it was too slow to develop on. Locally much faster, which was always going to be the case for obvious reason, but not as slow as received (unusable really).
- Local time
- Today, 17:10
- Joined
- Feb 28, 2001
- Messages
- 30,643
First, I will preface my comments by stating clearly that I have stayed away from this one because of zero direct experience with SQL Server and Azure. I will ask what might therefore be seen as dumb questions that are hopefully at least not totally irrelevant.
1. Using Task Manager >> Performance >> Memory - look at Memory Composition and determine how much, if any, FREE memory you have. On that line, your four categories are In Use, Modified, Standby, and Free - in that order. If "Free" memory is very low, this is not a good sign. Below that you have a "Committed" statistic shown as xxx.x/yyy.y and (probably) units of GB. The second number (yyy.y) is the total VIRTUAL RAM size on your system counting both physical RAM and the so-called "Virtual Memory" file (a.k.a. swap file.) If the first number (xxx.x) is greater than the size of your physical RAM, you are likely using part of the virtual memory file that is controlled from Windows "Settings" section. Use of virtual memory to any degree is a bad sign because that means you are possibly swapping. There is an old saying, "When the O/S swaps, the world stops." In more blunt terms, performance during swapping sucks. Because page swap operations are always executed at extremely high priority, potentially getting in the way of other things.
2. Do you have a persistent connection associated with that back end? If your network connection has to go through gyrations to get to the BE and you don't have a persistent connection for it to ride, you would spend a lot of network overhead in re-verifying your connection permissions. Because you get version-differentiated results, this one is unlikely, but if the two different version differ because of a patch to the connection manager code, it could happen.
Albert D. Kallal
Active member
- Local time
- Today, 16:10
- Joined
- Jun 11, 2009
- Messages
- 137
The issue(s) of a persistent connection doesn't apply nor effect nor help when using SQL server. The persistent connection works due to eliminating the time for windows to grant op-locks, and switch from single user file mode to multi user file mode (and to create the. Ldb locking file). In case of SQL server connections? There's no files on backend that front end is touching or opening. It's a pure tc/ip socket connection, and no files are involved (and hence no fix or benefits occurs by using nor having a persistent connection). So when using sql server backend, the persistent connection from access front end doesn't help. Of course this is SSMS connecting to SQL server and thus once again the concept or introduction of a persistent connection doesn't apply, nor help....
- Local time
- Today, 17:10
- Joined
- Feb 28, 2001
- Messages
- 30,643
Albert, I wasn't worried about files... I was worried about access arbitration, which applies to ALL network based connections in some degree. Particularly when either a certificate or a trust relationship is being exploited. Re-validating either kind of relationship adds some level of extra traffic on the net.
I would imagine Doc is referring to 'access' in a protocol context; not MS Access. But you're right that I'm not really hooked up to MS Access & this is relating purely between SSMS-22 & Azure.
Thanks Doc, my trial with Azure has ran-out but in the interest of learning I didn't have an identical set of params you referred to Doc, but i don't think it would be hardware related. Granted I'm no longer connected to Azure but whilst running half a dozen apps (SSMS inc'd) I got pic'd below.
2. Do you have a persistent connection associated with that back end? If your network connection has to go through gyrations to get to the BE and you don't have a persistent connection for it to ride, you would spend a lot of network overhead in re-verifying your connection permissions.
Google says default with a single SSMS window is a persistent connection though this seems to contradict that.
I'm going to continue locally for now but will look at Azure once I have the application built locally. Performance was good when I tested pulling data with Access with Azure, but for developing the application with Azure & SSMS-22 was not a fruitful experience.
Last edited:
- Local time
- Today, 17:10
- Joined
- Feb 28, 2001
- Messages
- 30,643
If there is a window that you keep open, that counts as a persistent connection. Operating without an open window still works but if there is no other persistent connection, you have to go through little-a access arbitration more often than you would like. That Windows operation is related to permissions and security and usually doesn't really care if someone else is in the same folder from another machine - though someone COULD in theory put an exclusive file lock on something via a Windows action. Access can do that if you set the "Open Exclusive" option in the File >> Options >> Database section. Excel does it all the time because Excel worksheets are Open Exclusive cases unless you use some options that would override the exclusivity.
Big-A Access arbitration (record-level locking) is strictly about checking the .LACCDB or .LDB file to see what data blocks are already in use and is just looking for two users in the same block. Usually, Access doles out data in 8-block chunks = 4096 bytes because of old memory management considerations and the fact that the size of a physical disk block is 1/2 Kbytes.
Thanks Doc, that is extremely interesting & I want to learn more of that kind of thing.
Going to mark this as Solved; apologies with my inexperience. I think it was more of a connection with Azure with SSMS-22 being slower than SSMS-21 on the same connection. Apologies again.
Going to mark this as Solved; apologies with my inexperience. I think it was more of a connection with Azure with SSMS-22 being slower than SSMS-21 on the same connection. Apologies again.
Last edited:
- Local time
- Today, 17:10
- Joined
- Feb 28, 2001
- Messages
- 30,643
First, apologies are not necessary. If we were all perfect, this forum might not even exist because we would already know all the answers. While I admit I went through a "know it all" phase while I was still gainfully employed, I have matured considerably since retirement to recognize and remember my own limitations. Fortunately, though I don't actually know it all, I still know enough to sometimes be useful even though my active use of Access is now at hobbyist level.
Second, as a side comment, the point I was making earlier is that it wasn't clear at first whether the comparison WAS based on the same connection - if there was possibly a difference in whether both versions did - or did not - maintain or honor a persistent connection.
As an example of that kind of problem popping up after a patch: The Server Message Block protocol used by Windows as a File and Printer Sharing protocol has evolved over time, currently (I believe) at v3.1.1 according to Goggle Gemini. When the transition to v3 occurred, some Access users reported odd behavior leading to corrupted BE files. Turned out that SMB v3 handled deferred write-back differently than earlier versions of that protocol, and it became necessary to turn off the "reservation" feature that reserved buffers for deferred operations even when the buffer's owner task had exited. I.e. Windows SMB v3 wasn't always finishing updates.
Thanks Doc, the connection parameters available in the GUI were all the same. So in the unlikely event that SSMS-22's default properties for the exact same connection differentiated then the connection was the same.
Persistent Connection - MS Access is not at play yet. Opening SSMS connected to Azure was whatever the default is on both of them & when we say 'Window' I imagine this is the application window (opening of SSMS). Though only working locally now I did not see SSMS settings for persistence in the Connection Settings. Google suggests not a persistent connection with Azure > SSMS.
I imagine it would be the same through an Access 'window' (application opening to that db; connection is made when Access opens, stays open then closes when Access closes I would guess). Suprisingly this is lacking on Google so lucky to have you teach me this - thanks; other users in future see ltd info here.
So presumably SSMS locally (no Azure) is a persistent connection. Surprised this is not more ubiquitous on the net.
Persistent Connection - MS Access is not at play yet. Opening SSMS connected to Azure was whatever the default is on both of them & when we say 'Window' I imagine this is the application window (opening of SSMS). Though only working locally now I did not see SSMS settings for persistence in the Connection Settings. Google suggests not a persistent connection with Azure > SSMS.
I imagine it would be the same through an Access 'window' (application opening to that db; connection is made when Access opens, stays open then closes when Access closes I would guess). Suprisingly this is lacking on Google so lucky to have you teach me this - thanks; other users in future see ltd info here.
So presumably SSMS locally (no Azure) is a persistent connection. Surprised this is not more ubiquitous on the net.
Last edited:
Albert D. Kallal
Active member
- Local time
- Today, 16:10
- Joined
- Jun 11, 2009
- Messages
- 137
Well, actaully, if you leave the solution exploer tree open, then in most cases, a live connection to SQL server does exist.
And in fact, since about SSMS 21, OR 22?
When I'm restoring a database, I now OFTEN have to close the explorer tree right before the "restore", else I receive a message about how the database (local sql server) is "in use".
So, I now often have to do this:
(note how I go to SSMS explore, right click on the database server, and choose disconnect)
Previous to SSMS 21, I don't ever recall having to do this.
so, often, if not most of the time, if you have SSMS open, and especially any query window?
then a connection to SQL server is quite much persisted.
And I also often find the same if I'm using a VPN, close the VPN, and then try to shut down SSMS. Once again, I get messages about how some stay connection is open, and SSMS struggles to shut down (in fact, it complains about how some connection NOW does not exist!!!).
So, if you often use the SSMS GUI to restore a database? Then you NOW will find often that you have to dis-connect the project explorer tree, and if you don't, then you get messages about how the database is "in use", and you can't restore the database.....
R
Albert
Isaac
Lifelong Learner
- Local time
- Today, 15:10
- Joined
- Mar 14, 2017
- Messages
- 11,462
Since migrating to the newest SSMS, I can no longer double-click a .SQL file and have it open in the same already-open instance of SSMS. I've posted on AWF about this and tried every single hack offered to me with no joy. That's a pretty big loss IMO and a major boo-boo of MS.
RonPaii
Active member
- Local time
- Today, 17:10
- Joined
- Jul 16, 2025
- Messages
- 273
I have had issues with v3 fixed by setting LeasingMode to none on server share with Access BE files. It does slow down BE. With this setting I rarely see corruption problems and that is with up to 35 users hitting the same backend.
To disable LeasingMode on a server share (not on the entire server).
From a admin command prompt on the server
set-smbshare -name "ShareName" -LeasingMode none
What is SMB File Sharing for Windows and Windows Server?
Learn how the SMB protocol enhances file sharing in Windows Server with features like encryption, performance optimization, and high availability.
learn.microsoft.com
Albert D. Kallal
Active member
- Local time
- Today, 16:10
- Joined
- Jun 11, 2009
- Messages
- 137
I'm not seeing this issue.
If I have SSMS open, and click on a .sql file (from windows explore), then it opens up with current SSMS instance - not a whole new version.
And if I have a sql query window open to a given database connection? Then double clicking on a .sql file not only opens the file with the given instance of SSMS, but it ALSO opens to the given current connection.
eg this:
Now, I had what 2 (or maybe 3 versions of SSMS installed on my computer - so, I did of course re-associate the .sql file suffix with SSMS v22...
So, it's not a big issue, since most of us are not experiencing this issue.
(and I'm sure you would have seen boatloads of complaints on this issue - but we don't.....do we???)
Do you have multiple editions of SSMS installed? (I'm just been busy - not bothered to un-install previous versions - have about 2 or 3 of them to un-install when I find the time....)
R
Albert
Isaac
Lifelong Learner
- Local time
- Today, 15:10
- Joined
- Mar 14, 2017
- Messages
- 11,462
Yes, I found plenty of similar people with same problem when googling the issue. Along with advice on how to 'fix'. Your verbiage in your response to me sounds dismissive and rude.
Maybe you have a different SSMS version? I only have one version installed. 20.2.37.0
- Local time
- Today, 17:10
- Joined
- Feb 28, 2001
- Messages
- 30,643
Double-clicking on any Windows file has the same effect every time. Windows reads the file extension. It looks up the Associations table (which for Win11 is reached via Start >> Settings >> Apps >> Default Apps). IF the file extension has a default associated app, it launches the default app. If not, it asks you what you want to use.
Since "Launch via double-click" is a Windows O/S function, the Default Associated App, if defined, is immutable unless someone ran code to piddle around in the registry... OR without someone doing a direct keyboard-video-mouse action to adjust the default app - OR if someone uses a right-click followed by an "Open With" option OR - only on the first use of that file type, gets the "Pick your favorite app to use" option and takes the subsequent option to make that a new permanent association.
On a Windows box that is set up for multiple possible user accounts, I believe the list of default apps IS sensitive to current username.
Since "Launch via double-click" is a Windows O/S function, the Default Associated App, if defined, is immutable unless someone ran code to piddle around in the registry... OR without someone doing a direct keyboard-video-mouse action to adjust the default app - OR if someone uses a right-click followed by an "Open With" option OR - only on the first use of that file type, gets the "Pick your favorite app to use" option and takes the subsequent option to make that a new permanent association.
On a Windows box that is set up for multiple possible user accounts, I believe the list of default apps IS sensitive to current username.
