SSMS Hangs

I have employees using version 6 SQL assist with SSMS 2005.

Ever since the upgrade SSMS will periodically hang for 5 to 10 seconds and then come out of it. This happens enough that my team is frustrated.

Has anyone experienced this or have any ideas to resolve?

(Also, I apologize if this is a duplicate post. I posted earlier today but don't see the post on the forum)

It sounds like your database server is slow processing queries. Please try disabling the background performance analyzer feature in SQL Assistant which is checking the code and highlighting suspicious code fragments. Isn't that a bit ironic? So if you disable the performance analyzer, it will free the resources, send less queries to the database, and make SSMS more responsive. Please let us know if that suggestion doesn't help.

I'm seeing this as well. Once or twice a day. It's possible SQL Assistant is sending a query that takes 10 seconds to process. That would take quite a query for our systems. The background perf analyzer, might need to be more background. Perhaps an async call?

It is already asynchronous. :) It blocks SSMS only while highlighting questionable text and rendering status bar on the right side.

Let's try figuring out what is causing the freezing. Do you feel like the issue is script specific or it can happen anywhere at anytime?

It isn't frequent for me, maybe 2-3 times a day. It seems to happen on any script. It might be limited to just really large scripts.

Here is an example. It doesn't happen everytime, but it did freeze once today. I can send it because it only uses system tables.

Code:

/************************************************************
Execute in text mode
************************************************************/

DECLARE @Tablename SYSNAME = 'tbl',
@Schema SYSNAME = 'dbo',
@Database SYSNAME = DB_NAME()
DECLARE @QualName SYSNAME = @Database + '.' + @Schema + '.' + @Tablename;

SET NOCOUNT ON

PRINT 'Select'
SELECT 'prod.' + c.COLUMN_NAME + ',dev.' + c.COLUMN_NAME + CASE --All this just to remove the last column comma
WHEN c.ORDINAL_POSITION <SELECT> 1 THEN 'and ' ELSE '' END +
'prod.' + COL_NAME(ic.OBJECT_ID,ic.column_id) + ' = dev.' + COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
INNER JOIN sys.objects o
ON o.[object_id] = ic.[object_id]
INNER JOIN sys.schemas s
ON s.[schema_id] = o.[schema_id]
WHERE i.is_primary_key = 1
AND o.name = @Tablename
AND s.name = @Schema

PRINT 'Where'

SELECT 'IsNull(prod.' + c.COLUMN_NAME + ', '''') <> IsNull(dev.' + c.COLUMN_NAME + ', '''') ' + CASE --All this just to remove the last column comma
WHEN c.ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
                                                               FROM INFORMATION_SCHEMA.[COLUMNS] c2
                                                               WHERE c2.TABLE_NAME =c.Table_name
                                                                  AND c2.TABLE_SCHEMA = c.Table_schema
                                                      ) THEN ' or '
ELSE ''
END
FROM INFORMATION_SCHEMA.[COLUMNS] c
WHERE c.TABLE_NAME = @Tablename
AND c.TABLE_SCHEMA = @Schema

I'm unable to reproduce that. I copied the script many times to make it much longer and it is still fine.

Here is one tip that may help figuring our whether the syntax checking or performance checking is an issue. While running background operations, SQL Assistant animates little status symbol at the top of the syntax bar. Next time you notice SSMS freezing, check what the symbol is doing.

I also run SSMS Tools and just grabbed the latest (2.0.4) that fixes an issue with SQL Prompt. This might be related. I'm curious if anyone else is experiencing this issue that also uses SSMS Tools