SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
SSMS Hangs

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
SSMS Hangs
Author Message
brianshannon



Joined: 18 Feb 2010
Posts: 18
Country: United States

Post SSMS Hangs Reply with quote
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)
Wed Sep 21, 2011 6:06 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
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.
Thu Sep 22, 2011 12:38 am View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
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?
Thu Sep 29, 2011 11:19 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
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?
Fri Sep 30, 2011 10:21 am View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

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

Fri Sep 30, 2011 11:52 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
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.
Fri Sep 30, 2011 8:58 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
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
Tue Oct 04, 2011 1:39 pm 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
Page 1 of 1

 
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.