 |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
stephh
Joined: 29 Jul 2021 Posts: 46
|
|
SSMS freezes each 1-2 minutes |
|
Hi,
I use SQL Assistant 11.5.362 with SSMS 18.9.2
We have many databases and linked servers, wiith a lot of tables (1000) and a lot of stored procedures (6000).
Each 1-2 minutes my SSMS freezes for 2-3 seconds :-(
I started a profiler, and I see this query :
 |
 |
IF (@@version LIKE '%SQL Server 20%' AND @@version NOT LIKE '%SQL Server 2000%')
or @@version like '%SQL Azure%'
EXEC ('select
SERVERPROPERTY(''ServerName''),
SYSTEM_USER,
DB_NAME(),
ISNULL(SCHEMA_NAME(), ''dbo'')')
else
EXEC ('select
SERVERPROPERTY(''ServerName''),
SYSTEM_USER,
DB_NAME(),
ISNULL(USER_NAME(), ''dbo'')')
|
This code is the "Context (MSSQL)" query.
What is strange, this query is executed many times, but doesn't take a lot of time (only few milliseconds).
I see on that forum to add in the first line
 |
 |
SET ARITHABORT ON |
but without succes....
What can I do ?
I'm tired to work on this conditions.
Thanks for your help :-)
|
|
Thu Jul 29, 2021 5:43 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
Could you please describe what you are doing before it freezes? If there is a specific pattern, like you are typing something? Typing a dot or specific keyword?
I doubt the Context query is an issue. It's used internally by intellisense to find out your session basic context, like current database, user, schema, and it's very light.
|
|
Thu Jul 29, 2021 5:33 pm |
|
 |
stephh
Joined: 29 Jul 2021 Posts: 46
|
|
|
|
I don't type anything special.
For instance it also freezes when I use the arrows keys (up or down) to scroll my code.
Maybe the problem is not a SQL query but internal in SQL Assistant ? Bit each time it freezes, this query is executed.
|
|
Fri Jul 30, 2021 1:45 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
Thank you. Do you see any dependencies on the size of the code in the editor? How long is the code you are working with? Does it refer to any objects associated with Linked Servers, like a view accessing remote tables or something?
If you'd like to eliminate Context query as a factor while we are troubleshooting the issue, please change it temporarily to a query with hard coded values. That is to test that the Context query is not a root cause of the issue. Let's assume for now that network connection is not an issue. Still would be a good idea to check SQL Assistant logs if show frequent re-connects to the server after a lost connection,
 |
 |
SELECT
'server name here',
'your login here',
'your database',
'your default schema' |
The obtain values for the above test, you can run
 |
 |
EXEC ('select
SERVERPROPERTY(''ServerName''),
SYSTEM_USER,
DB_NAME(),
ISNULL(SCHEMA_NAME(), ''dbo'')') |
And one more thing, can you please verify that SQL Assistant system tray application is running? If not, please start it and check if that makes a difference. The add-on for SSM sends messages to the system tray application to notify it of add-on working state changes and also to show some non-interrupting status messages. The basically work in tandem even though the add-on can be used standalone. If the system try application isn't running, it may lead to minor delays because of the broken communications.
|
|
Fri Jul 30, 2021 2:37 am |
|
 |
stephh
Joined: 29 Jul 2021 Posts: 46
|
|
|
|
Thanks to help me.
I changed the code, but it's still the same, it continues to freeze.
Yes, the application in system tray is up and running.
There is no dependences, no linked servers. It can freeze even if I type comments.
Where can I find the logs ?
|
|
Fri Jul 30, 2021 3:22 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
Thank you. Based on your test results with hard-coded values we can assume it's not the code of the query that causes the issue directly. Let's now focus on investigating network connection performance/stability and code folding. But first thing first, please restore the original version of the Context query.
The log is located here C:\Users\your_user_name_here\AppData\Roaming\SQL Assistant\11.5\SQLAssist.log
Please check if you see any exceptions reported or messages like lost connection to ... reconnecting
|
|
Fri Jul 30, 2021 9:25 am |
|
 |
stephh
Joined: 29 Jul 2021 Posts: 46
|
|
|
|
No "lost connection to ... reconnecting" in the log.
But I see a lot of programs, like Notepad++ , Office, outlook, teams, ... ? Why ?
I want to use S.A. only with SSMS.exe
|
|
Mon Aug 02, 2021 4:04 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
You can ignore these diagnostic messages, they come from SQL Assistant's low level framework. It monitors programs starting to check if it needs to start running its integration code for the started program if that program is referenced in the target list.
Back to the issue. Can we now look into impact of SQL code in the editor. Is the issue observed with small files? Large files? How large, Specific types of files, like for example, stored procedures? Does the code contain programmatic regions like --#region=Region Name ?
|
|
Mon Aug 02, 2021 8:56 am |
|
 |
stephh
Joined: 29 Jul 2021 Posts: 46
|
|
|
|
No nothing special in my code.
But I have this lag / freeze when I'm connected to a SQL Server with a large amount of databases / linked servers / stored procedures.
If I'm connected to a server with few db / lks and SP, it's OK.
I write 99% of my time stored procedures.
I changed some db queries to avoid replications objects for instance, so that's faster to refresh the cache when I connect to a server, but this lag/freeze is persistant.
I'm sorry that I cannot be more precis.
|
|
Mon Aug 02, 2021 9:15 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
I think this is actually a very good observation and it's very good that you have an opportunity to compare its behavior with different servers. Our next step should be to identify the exact timing of the freeze and its trigger. Would be great if it can be reproduced with a small number of lines in the editor. Do you see that happening after you type dot symbol? After you stop typing and wait for 5-7 seconds and then start typing again?
Am I correct that you only profiled in SQL profiler the sid assigned to the SSMS editor?
FYI, SQL Assistant runs additional background threads for code syntax and performance analysis, they wake up by default every few seconds after an idle time, they are responsible for highlighting various code issues so they have to interface with the editor and the main foreground thread. Both features can be turned off if wanted.
|
|
Mon Aug 02, 2021 10:14 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
P.S. if we identify specific activity and the query behind it, we can optimize that query for your environment.
|
|
Mon Aug 02, 2021 10:15 am |
|
 |
stephh
Joined: 29 Jul 2021 Posts: 46
|
|
|
|
Unfortunately I cannot isolate a specific case. For me it's randomly.
For instance, in SSMS, I open a new sheet, I start to type my code, and few second after I have the lag.
It's not specifically after a dot, or after a pause. The most annoying it freezes in the middle of a word.
I opened the task manager, when SSMS freezes, the CPU for SSMS is 100%.
I also see 2 thread with S.A. : SQL Assistant (32 bits), and SQL Assistant Command Line Utility. Both are 0%.
I profiled my login, so I saw some queries from S.A. for all SPID I have (one per sheet), not only the one I type.
May we test to disable background threads ? Or is it possible that come from an option of SSMS ? I did not try to disable S.A...
|
|
Tue Aug 03, 2021 4:40 am |
|
 |
stephh
Joined: 29 Jul 2021 Posts: 46
|
|
|
|
I think I found something.
- I stopped S.A. in sys tray and in SSMS, menu SQL Assistant
- I open a new tab/sheet in SSMS.
- I see cursor blinks, I don't type anything.
- After few seconds, the cursor disappears, SSMS freezes / CPU 100% for 2 seconds
- Immediately AFTER , I see in the SQL Profiler the Context BD Query
It's the same thing each minute. See the print-screen below.
What does SQL Assistant just before the Context Query ?
|
|
Tue Aug 03, 2021 5:13 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
On your screenshot the time isn't completely visible, seconds and milliseconds are outside of the visible area, I can't say how long it takes, but it does appear like it runs every minute or so (not sure why yet, that's not expected). The activity is within the add-on which is part of SSMS process space and resource usage (CPU) and it's executed from within SSMS in a foreground thread. Can you please let us know how long that query takes to run as you see it in SQL Profiler?
Do you have other add-ons for SSMS installed?
|
|
Tue Aug 03, 2021 10:48 am |
|
 |
stephh
Joined: 29 Jul 2021 Posts: 46
|
|
|
|
The procedure take few milliseconds.
What I say is that the freeze is just BEFORE the Context Query.
I have SSMSBoost as a plug-in.
|
|
Wed Aug 04, 2021 1:53 am |
|
 |
|
|
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
|
|
|