SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
SSMS freezes each 1-2 minutes
Goto page Previous  1, 2
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
SSMS freezes each 1-2 minutes
Author Message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7379

Post Reply with quote
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
Wed Aug 04, 2021 10:32 am View user's profile Send private message
stephh



Joined: 29 Jul 2021
Posts: 29

Post Reply with quote
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 :


Thu Aug 05, 2021 2:22 am View user's profile Send private message
stephh



Joined: 29 Jul 2021
Posts: 29

Post Reply with quote
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.
Thu Aug 05, 2021 9:12 am View user's profile Send private message
stephh



Joined: 29 Jul 2021
Posts: 29

Post Reply with quote
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.
Thu Aug 05, 2021 9:32 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7379

Post Reply with quote
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.
Thu Aug 05, 2021 11:46 am View user's profile Send private message
stephh



Joined: 29 Jul 2021
Posts: 29

Post Reply with quote
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 ?
Fri Aug 06, 2021 2:12 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7379

Post Reply with quote
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.
Fri Aug 06, 2021 12:12 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7379

Post Reply with quote
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
Mon Aug 09, 2021 12:50 am View user's profile Send private message
stephh



Joined: 29 Jul 2021
Posts: 29

Post Reply with quote
Thanks for the explanation.
No problem, I'll rename the XML file when it'll be too big.
Mon Aug 09, 2021 1:52 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7379

Post Reply with quote
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.
Mon Aug 09, 2021 9:58 am View user's profile Send private message
stephh



Joined: 29 Jul 2021
Posts: 29

Post Reply with quote
Thanks !
Tue Aug 10, 2021 1:47 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page Previous  1, 2
Page 2 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.