SQL Prompt Hanging
Giggles220
Posts: 154
Yesterday I updated my SQL Prompt from a version of 5. something to the most recent version 5.0.0.651. Now whenever suggestions pop up it freezes my whole Management studio for at least 20+ seconds. I can not type ahead or do anything. One example was I typed "Select " (notice there is a space) so the suggestion pops up, I wanted to then press enter and type the from clause to get the proper suggestions for the give table, I was unable to do so. It took 26 seconds for the suggestions to disappear and the fact that I pressed the enter key to register. THis is making my code writing extremely slow which is the opposite result I'd like to have from using SQL Prompt.
Comments
Did you have SQL Search installed prior to installing the latest version or did you install it with the official release of SQL Prompt 5 (it's included in the installer)? SQL Search performs a one time indexing task that could potential take some time and hinder your SQL Prompt suggestions whilst it is indexing. If you notice the SQL Search binoculars on your toolbar try activating a search for some feedback on the state of this task.
Let me know how you get on.
Luke Jefferson
Product Manager
Red Gate Software
As discussed privately by email, your slowness issue seems to have gone now, maybe something to do with having a "phantom" Visual Studio open.
If any other users experience similar behaviour, please post here with as many details as you can, as we're still not really sure what caused the slowness in the first place.
Paul Stephenson
SQL Prompt Project Manager
Red Gate Software
Project Manager, Red Gate
Since yesterday I've the same problem with the newest version of SQL Prompt!
Windows 7 ultimate 64 bit, SQL2008R2 64 bit, 4GByte RAM.
Earlier I heavily used Prompt, now I can type SQL only if SQL Prompt - Options - Behavior - Use SQL Prompt DISABLED
Symptom: Opening MMS with "Enabled" SQL prompt, WITHOUT new Query window - no hanging.
Any Source loading into new Query window - hanging starts...
Richard Dragossy
Thanks for your details. When you open a query window, can you type at all if you wait for 30 seconds like Giggles220, or does SSMS completely hang?
Does this happen only on really big databases or the small ones too?
Before you open a query window, if you right-click on a database in Object Explorer and choose Find Invalid Objects, does it complete the operation or hang there too?
Regards,
Paul Stephenson
SQL Prompt Project Manager
Red Gate Software
Project Manager, Red Gate
Meantime I had some tests with Your Tool
Finally I've read on red-gate.com, then .NET 3.5 or higher needs.
Downloaded and installed .NET 4.0 framework on my computer.
After some computer reboots :oops: (and reinstalling SQL Search after installing .NET 4.0) - everything seems to be working correctly.
My SSMS- "About" says the next information:
Microsoft SQL Server Management Studio -> 10.50.1600.1
Microsoft Analysis Services Client Tools -> 10.50.1600.1
Microsoft Data Access Components (MDAC) -> 6.1.7600.16385
Microsoft MSXML -> 3.0 6.0
Microsoft Internet Explorer -> 8.0.7600.16385
Microsoft .NET Framework -> 2.0.50727.4952
Operating System -> 6.1.7600
I suppose, that the reason of this problem whereever around .NET versions, but I'm not sure in it.
The interesting from the information above the .NET 2.0!!
Maybe SQL2008R2 needs only 2.0 version?
I don't know, whether Your installation process checks the .NET version or not? If not, it's possible, that in time of first installation I had only 2.0 version :?, because after istalling 2008R2 immediately came Your tool ....
According to Your questions:
My simptoms WERE the next:
1. Start SSMS with disabled SQL prompt, working half our - no problems.
2. Enable SQL prompt - SSMS immediately completely hangs - hour-glass - Task Manager - kill....
Now "Find Invalid Objects" works on my local computer without any problems, on client's side (connected via VPN) stops with timeout.
3. I'm working on large databases (over 250 user table, 500 USP 200 VIEW, 2000 constraints ..), I had no experience on small ones.
Regards: Richard
"installing .NET 4.0" means "installing .NET 4.0 64 bit version".
Regards: Richard
Thanks for being so detailed. It's interesting that reinstalling .NET 4.0 seemed to help. The installer for SQL Prompt 5 does check for .NET 3.5 being present on the system -- did you not have 3.5 or later when you first used SQL Prompt 5? If so, it could be there's a bug somewhere in the detection code.
I think the "Microsoft .NET Framework 2.0.50727.4952" in SSMS is a bit of a red herring -- it's what my machine says on that About screen too, and I definitely have 3.5 installed and a working version of SQL Prompt 5. I think it's the version of the framework being used directly by SSMS.
Regards,
Paul
Project Manager, Red Gate
I'm not sure, whether "Installing" or "reinstalling" happened. My old computer went wrong, and I bought the new one.
The installation order:
1. Win 7 Ultimate (64 bit) /hungarian/
2. Office 2010 professional plus 64 bit /hungarian/
3. MSSQL2008R2 (64 bit)
4. Red Gate SQL toolbelt
... and so on ...
I've never checked the "prerequisites", the installation process went on "Next - next - Finish" way....
Regards: Richard
I've recently updated SQL Prompt and am now running version 5.0.0.651 in combination with SQL Source Control, Compare, Data Compare, Search (all of which are running their most recent version).
After the update I noticed that SSMS became sluggish whenever the query editor window receives focus.
After reading the above posts, I tried uninstalling SQL Search, but that didn't fix it. Neither did repairing the .Net framework 4.0 installation (and rebooting my pc).
I can easily reproduce it by switching from SSMS to another program and then back to SSMS. If I immediatly try to scroll through my code (about 80 lines), SSMS freezes for at least 5 seconds. When I disable SQL Prompt, it reacts instantly.
Hopefully you guys can reproduce this as well and come up with a solution.
Best regards
Erwin Dockx
Here are some errors in the SQLPrompt log from today:
http://wiseman-wiseguy.blogspot.com
SQL Server Architect\Developer
MCITP: Database Administration SQL Server 2008
As other people have stated, it makes it unusable. I would also agree that it seems to be the worst when a complicated script is loaded - I am looking at one now with CTE's in it.
Microsoft SQL Server Management Studio 10.0.4000.0
Microsoft Analysis Services Client Tools 10.0.4000.0
Microsoft Data Access Components (MDAC) 6.1.7600.16385
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.7600.16385
Microsoft .NET Framework 2.0.50727.4952
Operating System 6.1.7600
As this is a serious error, we've backported the fix to 5.0, but haven't fully regression-tested it. I've emailed people on this thread with a link to the private download. If others reading this are suffering from slow performance with CTEs and would like to try the patched version, then please email me at paul.stephenson@red-gate.com.
Regards,
Paul
Project Manager, Red Gate
Regards,
Paul
Project Manager, Red Gate
Config.
Windows 7 64bit.
SQL Server Management Studio 2008 32bit.
SQL Prompt 5.1.4.11, Professional Edition
Edit Stored Procedure with 1124 lines.
Stored Procedure works with Numerous Temporary Tables and one CTE.
Steps to duplicate.
Reboot computer. Start up SSMS with SQL Prompt Enabled. Connect to Database. find proc in object list and click modify. Press page down 8 times, to move towards the middle of the proc. Screen hangs about line 473. SSMS becomes unresponsive. Unable to open SQL Prompt 5 menu to disable SQL Prompt. Sometimes I can. If there is another window open in a tab, sometimes I can click on the tab. If the focus shifts to that tab, SSMS becomes responsive again. I can then disable SQL Prompt and then go back to the original tab. If I go back to the original tab without having disabled sql prompt, then it acts locked up again.
Note, I don't normally use Visual Studio to edit procedures, but I gave it a shot and could also lock visual studio trying to page throught the same procedure. the main difference is that it seems Visual Studio 2010 Recovers after a few moments of sitting there. SSMS does not recover.
It's now with the developers for further investigation. Thanks Mikeh!
Project Manager, Red Gate
Any update from the developers as to what the issue might be? (Is it a particular feature like CTEs?)
There are some possibilities around avoiding too many repeat parses, but no easy solution has so far presented itself, I'm afraid. The parsing code is very complex, but we hope to be able to make some improvements in a future release without breaking any other behaviour.
Project Manager, Red Gate
I am also experiencing similar issues that I thought were originally my hardware. Now I have a brand new fresh i5 Windows 7 Ultimate x64 unit with 8 Gb or RAM. Lots of hard disk space, no garbage software. I am running SSMS 2008 R2 and SQL Prompt v5.1.6. Things start off well but during the course of the day as I open more query windows and attach to multiple servers/databases, SQL Prompt starts to get slower and slower and SSMS seems to get more sluggish. If I disable SQL Prompt, SSMS responds normally. If I quit SSMS and restart with SQL Prompt, the whole cycle starts again. I have also been seeing quite a few of these in the Application Event log:
2011-06-16 11:35:25,422 [14] ERROR RedGate.SqlPrompt.Engine.LogService [(null)] - LogService caught unhandled exception in AppDomain: 'Thread was being aborted.'
System.Threading.ThreadAbortException: Thread was being aborted.
at RedGate.SQLPrompt.CommonUI.TaskExecuter.Executer.ExecuteTask()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
or similar. All in all I have been really disappointed with this product since version 4. My old version 3 used to work awesome and there was more control of the app by the end user ( cache clean out, tweaking, etc ). But since then I think the quality has really dropped in this product badly. It's getting to the point where I may have to look for alternate products and not renew my maintenance when it comes due. I really like the product when it works, but when it doesn't, it really sucks !!! Please fix !
Max
Note, that in my original posting, my issue is that SQL Prompt never returned and hung the SSMS session that it was processing. I can understand things being slow, but the app shouldn't hang the UI. Note in VS2010, there was no hang, only a long delay. Thus that is what indicated to me an unexpected behavior, i.e. bug.
I also have slowdown in SQL Prompt 5.1.6.35 on a pretty decent rig. I am also seeing the thread aborted exception.
The slowdown can be quite severe (10-30 seconds of application hang time).
It's more common when working on a file with a lot of text, but sometimes occurs on smaller files as well. It doesn't seem to be based on any particular patterns in the code. I haven't been able to figure out the first hint as to what might be causing it, it's just... random.
The issue below is now resolved in my environment using the August update of 5.1.8.2.
If you have a number of temp tables in a procedure and you experience hanging, I suggest updating to the 5.1.8.2 version.