SQL Prompt Hanging

Giggles220Giggles220 Posts: 154
edited August 3, 2011 10:22AM in SQL Prompt Previous Versions
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

  • Is there a way to get the last version. This problem is hindering my ability to get my work done so much that I'm actually going to disable SQL Prompt.
  • Hi,

    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
  • I previously had SQL Search installed. I don't remember if it updated to a new version yesterday when I did the install. I don't remember if I had SSMS open after I upgraded SQL Prompt either. Since is has been several hours since I tried using SQL Prompt I enabled it and try again and still find it hanging although now instead of 26 seconds it is only 20 seconds but still too long in my opinion.
  • Hi Giggles220,

    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
    Paul Stephenson
    Project Manager, Red Gate
  • RickRick Posts: 7 Bronze 2
    Hi Paul!

    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
  • Hi Richard,

    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
    Paul Stephenson
    Project Manager, Red Gate
  • RickRick Posts: 7 Bronze 2
    Hi Paul!

    Meantime I had some tests with Your Tool :) on the weekend. Disable some properties; disable SQL Prompt itself /Behavior: Use SQL Prompt disabled/; Uninstall SQL Search and so on.

    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
  • RickRick Posts: 7 Bronze 2
    Refining:

    "installing .NET 4.0" means "installing .NET 4.0 64 bit version".

    Regards: Richard
  • Hi 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
    Paul Stephenson
    Project Manager, Red Gate
  • RickRick Posts: 7 Bronze 2
    Hi Paul!

    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
  • Hello

    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
  • unclebigunsunclebiguns NH, USAPosts: 23 Silver 1
    I'm experiencing similar slowness problems with SQLPrompt 5. For me it appears to happen when I have a long SQL script open, particularly with CTE's and or temp tables in the script. It also happens in Visual Studio 10.

    Here are some errors in the SQLPrompt log from today:
    29 Dec 2010 20:53:31,489 [1] INFO RedGate.SqlPrompt.Engine.LogService - Starting new SQL Prompt editor session
    29 Dec 2010 20:58:00,290 [9] ERROR RedGate.SqlPrompt.Engine.LogService - LogService caught unhandled exception in AppDomain: 'Thread was being aborted.'
    System.Threading.ThreadAbortException: Thread was being aborted.
    at System.Threading.Thread.SleepInternal(Int32 millisecondsTimeout)
    at System.Threading.Thread.Sleep(Int32 millisecondsTimeout)
    at RedGate.SQLPrompt.CommonUI.Controls.SpinnyThing.SpinnyWidget.?
    Jack Corbett
    http://wiseman-wiseguy.blogspot.com
    SQL Server Architect\Developer
    MCITP: Database Administration SQL Server 2008
  • I concur - I am seeing massive lag/slowdown in my query windows. If I drop out of SQL Prompt AND Visual Studio and go back in, it goes away for a while but something is seriously wrong. I'm sitting here right now watching the processes in the task manager - as soon as I give the query window focus, SMSS jumps to the top sucking up 100% of one of my cores.

    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
  • Ok it is VERY likely a CTE problem. I can open and close one stored procedure with three CTE's in it and make the lag come and go.
  • Here at Red Gate the developers have identified that there was a gross inefficiency in the way scripts with CTEs were parsed. This has now been resolved and the fix will be included in SQL Prompt 5.1.

    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 [email protected].

    Regards,
    Paul
    Paul Stephenson
    Project Manager, Red Gate
  • As an update to my previous post, please be aware that the CTE slowdown fix is one of the improvements in the 5.1 beta release, so if you are experiencing this issue then download from the link in the 5.1 beta announcement.

    Regards,
    Paul
    Paul Stephenson
    Project Manager, Red Gate
  • Still manage to hang SSMS easily using SQL Prompt.
    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.
  • Mikeh has sent me his procedure and I can confirm I've reproduced extreme slowness followed by SSMS hanging.

    It's now with the developers for further investigation. Thanks Mikeh!
    Paul Stephenson
    Project Manager, Red Gate
  • Paul,

    Any update from the developers as to what the issue might be? (Is it a particular feature like CTEs?)
  • The slowness is down to the SQL Prompt parser working extremely hard. In this case it seems that having many columns and table-like objects (temporary tables and CTEs) that are themselves embedded SELECT statements seem to be killing it. It's not going into an infinite loop, but enough things trigger another pass through the script that the parser starts again as soon as it finishes, causing SSMS to become unresponsive.

    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.
    Paul Stephenson
    Project Manager, Red Gate
  • That sounds similar to what I am experiencing, but I'm not seeing it quite that bad. For instance it takes about 10 seconds when I select a window with a large/complex SP in it before SSMS is responsive to anything.
  • Hi,

    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
  • 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.

    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.
  • allmhuranallmhuran Posts: 19 Bronze 2
    GearBahx wrote:
    Hi,

    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.

    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.
  • *** Note ***
    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.
    Mikeh wrote:
    Still manage to hang SSMS easily using SQL Prompt.
    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.
Sign In or Register to comment.