SSMS freezes each 1-2 minutes

Here is what's concerning me. The activity seems be triggered by something outside of SQL Assistant like something is interfering with it. It doesn't have a function that activates minutely.

I also had a quick look at SSMSBoost features on the vendor's website and couple of features may potentially interfere. My particular concern is SQL statements history and Fatal actions guard. I don't know how they are implemented. If they've got a kind of low level implementation intercepting all database queries, it might be potentially conflicting here, also if they scan code in the editor minutely and that kind of pushes some functions in SQL Assistant that are normally dormant. May I ask you to try temporarily disable SSMSBoost add-on in SSMS to rule out that the issue is due to a conflict? Here is what I've found out for how to disable it temporarely https://www.ssmsboost.com/social/posts/t1185-Disable-SSMSBoost-Add-in--NOT-uninstall

Thanks you.

If I understand well, there is no reason that S.A. executes each minute the Context DB Query ?

I disabled SSMSBoost, and SSMS continues the freeze and just after the Context Query is executed :

I think I found something.

I started Microsoft Sysinternal Process Explorer.

The first graph is "CPU Usage" , and each minute we see an activity (in green) ; which is when I have the lag/freeze.
The third graph is "I/O" , and we see an activity in the same time (in blue and pink). 9 MB each time.

I started Perfmon.exe, I/O and I see this :

And the file C:\Users\xxxxxxx\AppData\Roaming\SQL Assistant\11.5\saDocMng.xml is .... 9.1 MB !

I think that S.A. refreshes this file each minute. Perhaps I have a slow SSD drive, but it takes time to write more than 49'000 lines.
I see also the file "asDocMng.log" is 2.0 MB.

Ok, I renamed saDocMng.xml and saDocMng.log.
These 2 files were created the 11-Jan-2021.

S.A. created it again from scratch.
Each minute S.A. append new lines...

Maybe S.A. should trim these files sometimes....
For what the xml is for ?

Anyway, it solve my problem ! :)

Thanks.

Awesome, great job!
Thank you for sharing.

Even though it still doesn't explain why the query is executed every minute, we know now what's going on. What you encountered is the Document Revisions Backup feature. It saves revisions of your scripts so that you recover you changes in case of a crash, or just undo some, or compare them later. Also allows you to reopen tabs you worked with before you closed SSMS. You can see the complete thing if you use SQL Assistant -> Recent Documents menu. Expand any script showing in the list to see what got saved and what was changed.

Now, the 9 MBytes saDocMng.xml file is a bit too big. that file stores the change history catalog. The log file can be of any size, it's not important, it just keeps appending to it. Please check why the XML file isn't get trimmed automatically, maybe your SQL Assistant settings upgraded from the old versions are incomplete. Open SQL Assistant's main Options dialog. On the Target's tab, select in the left top list SQL Server Management Studio, then on the right side expand Document Manager section. The default setting for the history length is supposed to be 3 months, which is 180 days and you can change it if you want. Also check the frequency settings, in the Recovery Backup Interval settings the value should be normally set to 15 minutes or something similar. Please check if you got 1 minute there or a blank value.

One more thing, all script/file revisions are saved to C:\Users\xxxxxxx\AppData\Roaming\SQL Assistant\History folder. If you don't need very old files, you can delete them manually, but better let the proper settings take care of that and let SQL Assistant delete these files as set in the settings.

I have 15 min in "Recovery Backup" , not 1 min.
And I have 90 days for file history, not 7 months.
Same options as you said.
So there is a bug as we cannot explain why this file is refreshed each minute.

I think I installed SQL Assistant 9.0 and after I only did upgrade.
Yes maybe one incomplete upgrade.

What can I do ? Uninstall completely and reinstall ?

Let me open a support ticket for you. I'm not able to explain the observed behavior. My understanding it shouldn't work like that.

Here is what I found out. Saving every minute is a feature. My bad my information wasn't up to date. It was initially as I described but then the internal things changed. The rationale for that is the same feature provides crash recovery. If the environment crashes, tabs can be restored to the state they were before the crash. The frequency parameter is now a legacy parameter not controlling the frequency anymore, it's kept there for backward configuration file compatibility. As for the history length, I was told its behavior is a bit more complicated than straighwordard deletion of any archived revision files older than 18 days. It deletes old files that don't have recent revisions, basically it deletes files that go out of scope. The issue here if you always restore tabs on startup and you basically work with the same tabs changing their content, never closing them, you create new revisions of the same name files, instead of new files, and as a result, the revisions for such files remain archived and never deleted.

The defect I entered on your behave is being investigated by the development team. The metadata XML file appears to be too big, bigger than the expected normal size. Updating this file and flashing changes to the disk leads to high CPU usage and as you found out that's the trigger for brief SSMS freezes. I will post new status updates for that ticket as they become available. In a mean time, if you experience this issue again, please rename the XML file again and restart SSMS.

Last edited by SysOp on Mon Aug 09, 2021 9:56 am; edited 1 time in total

Thanks for the explanation.
No problem, I'll rename the XML file when it'll be too big.

FYI, we plan on resolving this issue in version 12.0, optimize the metadata storage and update methods to make them more efficient and scalable.

Thanks !