SQL Prompt causes SSMS to hang

wonderbison

Hi
On a daily basis SQL Prompt causes SSMS to hang when looking for a suggestion.
I am typing a command and the SQL Prompt dialog appears to aid typing but then the SSMS hangs. I haven't found anything consistent as to why that is or happens so its hard to give you more info.

The command today was

SELECT TOP 10 * FROM myDB..myTable AS MT WHERE index (and at this point the popup comes up and hangs)

I was in master DB in the query window

I have about 8 windows open and 2 management studios.

Version info

Microsoft SQL Server Management Studio 13.0.16106.4
Microsoft Analysis Services Client Tools 13.0.1700.441
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.11.9600.18893
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.1.7601

SQL Prompt 9.0.7.3692

FabiolaB

Hi @wonderbison ,

I'm sorry to hear that you are encountering this problem.

Can you please update to the latest version (9.0.8)? The development team has worked on performance issues these last weeks.

Please let us know if your problem has been fixed!
Kind regards,
Fabiola

wonderbison

Hi

Downloaded the latest and still happening

Been working for about 5 mins on a new SSMS start and writing a SELECT * FROM tablea, get the dialog pop up suggestions which hung. Then received a message indicating SSMS was busy. The whole SSMS is hung and will not return. I have to kill SSMS and try again

Any further ideas?

wonderbison

Hi

This is still happening. SSMS pops up with a message saying that its waiting on an internal operation

I was in "master" database and I typed

SELECT * FROM tb

At which point a popup came up with just 1 row listed (its now gone so cant capture) and the process stuck



Thanks

stevemartinaws

I have also been having some serial hanging issues in SSMS with Redgate SQL Developer Addons - I suspect this is due to SQL Prompt. I have just installed on a fresh windows 10 OS and still seeing issues with hanging as soon as the addons were installed to SSMS

RichardL

Hi @wonderbison

Thanks for your posts.

Your organisation has a support contract in place so I have created a support ticket now for you relating to this issue and a product expert will soon be in touch with you on your ticket.

@stevemartinaws Thanks also for your post. If you have a support contract with us you can send in a support request in a mail or via the Redgate Portal to support@red-gate.com

Kind regards

fscottf

I've also been having this issue. It's very intermittent - *maybe* once a day with heavy use. I start typing an object name, SQL Prompt drop-down starts auto-suggesting, and then SSMS completely freezes with the drop-down still showing what I had started to type. The last two times this happened I tried disabling the NIC to see if SSMS would unfreeze (so I wouldn't lose my unsaved progress) but no luck.

SQL Prompt 9.1.0.4138
Microsoft SQL Server Management Studio 14.0.17213.0
Microsoft Analysis Services Client Tools 14.0.1016.232
Microsoft Data Access Components (MDAC) 10.0.16299.15
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.16299.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.16299

ToniG

Same situation with us. 10 User with SQL Server 2012, 2016, 2017. When starting to type and auto-suggesting starts --> SSMS completely freezes and after a while Windows Message (SSMS busy) comes along. Hard-stop & restart has to be done --> very annoying.

Microsoft SQL Server Management Studio 12.0.5000.0
Microsoft Analysis Services-Clienttools 12.0.5000.0
Microsoft Data Access Components (MDAC) 10.0.16299.15
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.16299.0
Microsoft .NET Framework 4.0.30319.42000
Betriebssystem 6.3.16299

RichardL

Hi @fscottf & @ToniG

Thank you both for your posts.

Could you please try the latest version which should get around this issue.
It is available from my link below for direct download.

ftp://support.red-gate.com/patches/SQLPrompt/07Mar2018/SQLPrompt_9.1.2.4363.exe

Do let me know if the error is still happening on this version.

Kind regards

fscottf

Thanks. All updated.

DBNewbie_2007

Glad to hear/see this is not just me having this problem.... and I do appear to have the latest version of SQL Prompt...  

SQL Prompt 9.1.2.4363

Microsoft SQL Server Management Studio 14.0.17224.0

Microsoft Analysis Services Client Tools 14.0.1016.244

Microsoft Data Access Components (MDAC) 10.0.16299.15

Microsoft MSXML 3.0 6.0 

Microsoft Internet Explorer 9.11.16299.0

Microsoft .NET Framework 4.0.30319.42000

Operating System 6.3.16299

The interesting part is it only appears to happen in one SSMS window that is connected to one SQL Server... I typically have 5-6 SSMS windows/instances running (one for each server). The one SSMS window hangs but all of the other SSMS windows work fine. Unfortunately, I have to close them all out, including the "hung" instance, then re-open the windows. 

RichardL

Hi @JReitter

Thanks for your post. 

If you've a got support contract, please send us a ticket. Provide as much information as you can - screenshots of any errors, log files etc – so we can help you as fast as possible.

If you're not covered by a Support contract at the moment, email our Sales team at sales@red-gate.com, and they'll be able to help. 

Kind regards

Richard Lynch.  

red5

SQL Prompt is the bomb.  It usually performs very well and gives me efficiency and higher productivity in my daily work.
This problem of freezing and killing SSMS (any version really) is happening to me 1/2 dozen times a day now.
It used to be just once every few days (still unacceptable).
I've been experiencing this issue in all my SSMS version and I've been developing in SSMS for many years.

Disabling SQL Prompt fixes my issues, but is really unacceptable...as I not only lose my productivity by not having SQL Prompt available, but we have now wasted our money on this product for our 25+ user licenses.

RedGate, this type of situation is now falling into the "product killer" category for us dedicated users.
I advise you to get your tiger team involved to find and fix this issue and provide an updated version to your users.
I really, really want RedGate to be successful, as your products are really great and I extol their virtues to other developers.
RedGate, the ball is in your court.....

DBNewbie_2007

I am still waiting for the system to hang again (it does cause slow downs in SSMS) before opening a support ticket, but I found that disabling the "Enable Code Analysis" option helped with preventing the freezing, SQL Prompt 9.1.2.4363. 

DBNewbie_2007

There is another thread that discusses the same problem with slowness and freezing... 
Here are some additional findings so far: 
1. After disabling the "Enable Code Analysis" option, the freezing went away as I mentioned before. 
2. Something (either SSMS or RedGate Prompt) is causing periodic slowness on my PC where either SSMS would have a hour glass (spinning circle) or my entire system would suddenly stop responding.
   - Note that,with stopping all instances of SSMS (I typically an have 4-6 running at once), the problem went away.  
3. The problem appears to be related to WmiPrvSE.exe

DBNewbie_2007

While SQL Prompt may have issues related to having "Enable Code Analysis" option enabled, I am beginning to think SSMS 17.5 might be causing the issue? 

I stopped the RedGate client service and disabled all of the SQL Prompt and still see periodic system freezing, with high WmiPrvSE CPU usage. I do know the problem goes away when all of the SSMS sessions are stopped. 

RichardL

Hi @red5 and @JReitter

Thank you for your posts. 

Just to let you know that the latest version of Prompt was released on the 28th of March. You can download it directly from my link below so see if this resolves your issue(s). 

ftp://support.red-gate.com/patches/SQLPrompt/28Mar2018/SQLPrompt_9.1.5.4619.exe

Kind regards

Richard Lynch. 

DBNewbie_2007

FYI, I did open a ticket with Support (it was getting EXTREMELY frustrating having my system lock up every 15-20 minutes)... installed SQL Prompt 9.1.5 and the issues appear to have been resolved! 

Thanks!   

torsten.strauss

Same here with version the latest version (28th of March)
SQL Prompt will freeze SSMS. Only way to resolve this is to restart SSMS from Task Manager.

Torsten

JoeKrauss

Same issue here with SSMS 17.6 and prompt version 9.1.5.4619. The suggestion box appears and typing comes to a quick end resulting in a locked or frozen SSMS. Glad I'm not the only one experiencing this issue. I've been experiencing this problem since I first purchased my license in December 2017. This error is very annoying and causes lost work as it always fails during the most inconvenient times.

Windows 10 x64
Visual Studio 2017
MS Office 2017

ballanda

SSMS 17.6 and SQL Prompt 9.1.6.4676

SSMS hangs for about 30 seconds when switching focus among items in the Object Explorer tree.  It also hangs when expanding or connecting to server objects in Object Explorer.  This was OK for a while, but seems to have returned in the past week. 

UPDATE- Turning off all experimental features in the options seems to have helped.

RichardL

Hi @ballanda & @JoeKrauss

Thanks for your posts. 

The latest version, released last Wednesday is available for download below. 

ftp://support.red-gate.com/patches/SQLPrompt/11Apr2018/SQLPrompt_9.1.7.4764.exe

Kind regards

Richard Lynch. 

edronen

I was having this problem also and realized there was an upgrade (9.2.5.6073 at this time), so I've updated and it might already be solved.

However, the one additional thing I found where this issue could be re-created easily was if the suggestions list was updating and I moved focus to somewhere else. I will try this with the newer version and see if I can re-create. Hopefully this will be helpful additional information. 

Thanks for all the work on SQL PROMPT - it's an amazing product!!! 

JonBrune

This happens to me but only when I am disconnected from my office network and connecting to a remote server outside of my office network.  I'm running version 9.2.6.6145.  Upgrading to 9.2.8.6358 does not help.

JonBrune

Looks like when I turned off "Automatically refresh suggestions" under experimental features that cleared my problem.  I'm assuming there is a high connection timeout so SQL Prompt keeps trying to contact db servers it no longer has access to.  It'd be nice if this timeout was configurable; I'd dial it way down.

gcomstock

I am so frustrated with SSMS hanging I'm close to uninstalling SQL Prompt.  After 1-2 minutes I usually just end task on SSMS and open it again.

I'm running version 9.4.0.6807.

Here's a simple way I can replicate the issue. 

1. Connect to a client VPN then connect to their SQL instance in Object Explorer.
2. Run a query against any database
3. Disconnect VPN while leaving query window up and the connection via Object Explorer 
4. If I wait 1-2 minutes then even try to disconnect or close the query window SSMS hangs for several minutes and at times I give up and end task after about 5 minutes.

I'm sure this issue would be mitigated if I just remember to disconnect from the client SQL instance but I hop from client to client all day so it's easy to forget.

JonBrune

gcomstock, that is exactly the same problem I've been having.  Have you tried disabling "Enable Code Analysis"?  That seems to help me.

gcomstock

Thanks JonBrune, I just gave this a test and it didn't help unfortunately.

JonBrune

gcomstock, you might also want to try unchecking the "Automatically refresh suggestions".  It's a pity red-gate hasn't fixed this problem yet.

gcomstock

JonBrune currently all experimental features are unchecked.  Yes, this is maddening when it happens daily and some days 3-4 times.

mbrooks

My SQL Prompt started behaving this way after upgrading to 9.4.4.7199. It's killing my productivity -- hanging every few minutes! -- and I am not having much success with turning it off either. It did it again after I turned off "Enable Suggestions" and it did it again anyway. No experimental features are on -- I learned about those the hard way long ago. I guess there's 'uninstall.' I will try a few more things.