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, 09:42
- Joined
- Jun 11, 2009
- Messages
- 116
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, 10:42
- Joined
- Feb 28, 2001
- Messages
- 30,588
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, 09:42
- Joined
- Jun 11, 2009
- Messages
- 116
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, 10:42
- Joined
- Feb 28, 2001
- Messages
- 30,588
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, 10:42
- Joined
- Feb 28, 2001
- Messages
- 30,588
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, 10:42
- Joined
- Feb 28, 2001
- Messages
- 30,588
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:
