Why does SQL Prompt slow down SQL 2005 Management Studio?

Calen

When I am connecting to network databases, SQL Prompt 3 will often make SQL 2005 Management Studio come to a complete halt for 5, 10, even 15 minutes at a time. Sometimes a semi-transparent window pops up in the lower right hand corner telling me it's indexing or such. Other times there is no message, it just hangs up. SQL 2005 didn't behave this way before I installed SQL Prompt 3, and it really dilutes the value of intellisense, when I have to wait such a long time to be able to start writing queries.

Is there any way to tell SQL Prompt to index, refresh it's cache, or whatever in the background, instead of stopping me in the middle of working?

Thanks,

Calen

Calen

As a follow up to my earlier post... I've now been waiting for over 15 minutes for SQL Prompt 3 to let go of SQL Server 2005 Management Studio. There is a SQL Prompt popup in the lower right hand corner that says "Loading from snapshot file", but it is a database that I'm not working with. While that's happening, SSMS is blank, just a white filled window with a title bar.

Evaluating SQL Prompt 3 is now preventing me from being able to do any of my work!!! Please reply with a solution to this problem. Is there something that I did wrong when I installed it? Is there a setting that needs to be set/unset? Other than drastic measure like Task Manager, is there any way to make SQL Prompt let go of SSMS? Why does it have to do this in the foreground anyway?

This is beyond frustrating. It is becoming infuriating. Please help make SQL Prompt work as advertised, or I'll have to demand a refund of the order my boss just placed.

Thank you,

Calen

ccage90

I wanted to report the same experience with SQL Server 2000 Query Analyzer. It is very frustrating. The free version SQL Prompt was less intrusive than this. I might have to switch back until these bugs are fixed.

Chris

Bart Read

Hi Calen and Chris,


That's somewhat odd. I'm assuming that after the popup appears you're not seeing any progress being made, it just kind of sits there at 0%?

There is one thing it might be: do either of you regularly use a VPN to access any of your databases, or is there some other reason some of the databases you work with might not always be available?


Thanks,
Bart

ccage90

Bart,

Thanks for responding. I am working on a laptop and use it at the office and over VPN while offsite. I typically switch between networks before rebooting. In other words, I work at office with QA open, put computer to standby and undock, open computer at home, connect to VPN, and use use same QA instance over VPN.

However, at the time I have the stalls from SQL Prompt, I have connectivity to all db servers. I'll open a new QA connection and see the popup in the corner, it gets to 30-40% progress during which time it seems SQL Prompt is in background thread. That is, I can still work in the QA window. Then, suddenly SQL Prompt takes over and I can't click/type in the QA window for up to several minutes. I then try finding ctrl-C or other cancel options to no avail.

One other item to report: I often open more than one instance of query analyzer, one for each server connection. (This is when I get the multiple SQL Prompt menus within the first QA instance. Harmless feature...) I find pretty regular crash-bug of Query Analyzer when starting the second instance since installing SQL Prompt 3. Then trying again, I am able to open second QA.

Thanks.
Chris

Bart Read

OK thanks Chris. Can you give me a bit more information about that crash please? Do you get any kind of error dialog pop up, and if you do can you send me the contents please? I have a feeling I might know what that is, and if it's what I think it is then I fixed it earlier today, however I'm a bit concerned about that hang still.

It doesn't sound like a VPN issue but obviously I can't rule that out at this point. We're going to spend some time looking at that since many people have reported it. Does it ever come out of the hang? E.g. if you walked away for half an hour and came back would it all be working again?


Thanks,
Bart

Bart Read

Hi Chris,


One more question: when the progress bar in the bottom right hand corner hangs what's the message it's displaying please?


Thanks,
Bart

ccage90

Bart,
Sorry for the delay. I will try to get a screenshot for you next time.

I believe the progress bar popup usually indicates that it is reading objects or indexing. I also noticed last time that the query analyzer window title bar shows "Not Responding" when I click in that window.

I still have not determined how to reproduce this regularly. It seems to happen every few days after my computer has been running for a while without reboot.

Thanks,
Chris

Brian Donahue

Have you tried going into the cache management and changing the 'automatic cache' setting? I've run into problems with this -- particularly when I use different accounts to connect to the server.

ccage90

How should I change the cache management settings? Always run with refresh checkbox disabled? I'll try it.

I have a snapshot of a lockup that just happened to me. The SQL prompt popup read "Indexing database objects..." and the query analyzer window title bar indicated that the program was "(Not Responding)".

Thanks,
Chris
[/url]

PDinCA

I've had the same problem, particularly since applying VS2K5 SP2, although I recall it happening prior to this.

I only use Windows Authentication for each of the four instances I work with and never login to my laptop with anything but my domain account.

I have noticed that if I'm applying an SP change across three of the environments in quick succession, SQL Prompt is left gasping, attempting to refresh for each Connection change. This is the most common scenario I've found for a VSMS lockout. Calen's 15 minutes is typical when this occurs.

It's not like I'm swapping databases either... It's the same default login database in each environment, with a very stable schema.

I have 10 databases listed in my Cache Manager. I occasionally visit a legacy server, so it has 2 DB entries, but the other 4 environments have matching pairs of DB names; the second of which is "master".

All 5 servers are on SS2000 SP4.

Cache management is set at "automatic".

In case you need some laptop stats:
Pentium M with 2gigs RAM and over 10gigs disk space free. T1 LAN connection. SQL Server is not running locally.

VSMS info:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Bart Read

Hi Stephen,


Something you might want to try, although I'm not saying this will definitely help, is increasing the InMemoryLimit setting in the meta-data cache. This isn't exposed through the UI as yet, however if you edit the "SQL Prompt Meta Data Cache.cache" file in Notepad or whatever you'll see an <InMemoryLimit> element at the top. Try increasing this value and see if it improves things (the minimum acceptable value is 2). You'll find this file in

C:\Documents and Settings\{username}\Local Settings\Application Data\Red Gate\SQL Prompt 3

If you're experiencing very high memory usage already this may actually worsen the situation, and in any case as I said it may not have any effect, but probably worth giving it a try.

Hope that helps.


Thanks,
Bart

PDinCA

I'm at 3 right now.

What does each unit increment represent? Is it megs, 100K, tens of megs, or what?

I have 500MB free of the 2GB RAM available.

Recommendations?

Is it best to edit the file when VSMS is closed? I noticed that changes I make to the EngineOptions.xml are overwritten if I edit while VSMS is up and running... Is this file treated the same?

GearBahx

Hi,

I would just like to report the same issues as everyone else. No VPN. 1 Gb NIC access to DB. No VPN. Everytime I drop a new SQL query into a new query window OR I open a new query window, the "Indexing Database object" semi-transparent box appear and takes forever to disappear , essentially making SQL Prompt virtually useless at this point and I am on the same DB!!!
What is going on here !!!

VisualDragon

The only time I experienced horrible performance due to SQL Prompt was after installing a service pack. (for Visual Studio OR SQL Server) I found that uninstalling and reinstalling SQL Prompt fixed everything up. This has happened to me a couple of times.

Calen

Over 4 months since I started this thread, and I see that no solutions have been put forward for this problem. Is Red Gate even working on it, or have we been hung out to dry?

I have spent 2 hours this morning staring at the SQL Prompt window in the lower right hand corner of the screen saying either "Loading snapshot file..." or "Indexing database objects...". And I'm only trying to open a New Query on the database that I've been working on, and for which the cahce was already refreshed. But, with several departments waiting on me to run some ad hoc reports, I can't get SQL Server Management Studio to work due to SQL Prompt freezing it up.

Refreshing the cache manually doesn't stop this, either. It still hangs up while loading, or indexing.

How about a real solution to this VERY FRUSTRATING PROBLEM?!?!?!? Not just a "not saying this will help ... and this may actually worsen the situation..." (From Mar 07)

On Feb 22, Bart said "...if it's what I think it is then I fixed it earlier today..." Would you mind posting that fix?

If I "walk away" for 15-30 minutes it might (or might not) come back from the freeze.

Could some method be added to SQL Prompt to allow the user to interupt whatever it's doing that it's freezing up on.

Reading the history of this thread, it really seems like this very serious problem was just dropped. Can it be picked up again? Is anyone working on this? Will Red Gate refund our money for slowing us down, instead of speeding us up?

To try to be fair. When SQL Prompt works, it is great. I really don't want to stop using it. But, when it doesn't, it prevents me from doing any work at all.

Unless a solution is forthcoming, I am just about ready to uninstall all Red Gate products, demand refunds, and disrecommend it to PASS (Professional Assoc. for SQL Server) and everyone I meet.

(By the way, I've been waiting for the Cache Management window to come up while I've written this. Over 15 minutes and it is only a blank while window with no contents.)

At my wits end,

Calen

Bart Read

Hi Calen,


I'm sorry you've been having all these problems. Without sitting in front of your machine and poking around myself it's difficult to state definitively what the problem might be, however, when this happens what's your CPU doing? And specifically, what's the CPU usage of SqlWb.exe?

The symptoms you're describing, especially the fact that it "might or might not come back" suggest a deadlock is occurring in the SQL Prompt caching code. Now we weren't aware of this situation when we shipped 3.0 and 3.1 and because we hadn't seen it here we assumed the issues people were having were largely related to spurious connection attempts by SQL Prompt. This is unfortunate given the amount of effort we went to in testing, and given the different machine and OS configurations we used, however it's not unheard of for a bug that's never shown itself during test to suddenly appear in the wild.

However it's come to light with 3.5 when we started testing cross-database and linked server support. This obviously exercises the caching code more thoroughly, and more importantly, in different ways that might mirror the differences seen previously due to running on single core, hyperthreaded or dual core machines. Subsequently we've done a lot more concurrency testing running multiple editors and so forth to allow us to isolate and fix the problems that occur in these and similar scenarios. I've therefore been able to refactor the synchronization code to ensure that in these situations SQL Prompt doesn't just hang and wipe out your editor.

I realise that's not terribly much help right this second, however the 3.5 release candidate should be available a week on Friday (the 13th), and I'd encourage you to give that a go. I'd be extremely surprised if it didn't fix the problems you've been seeing, but if it doesn't then we want to know because we don't want the 3.5 final release to go out with this problem (I'd consider that a hopefully unlikely situation though).

Let me again apologise for the difficulties you've been having. We certainly haven't been ignoring you but sometimes if it's a problem we've not seen before then it can take some time for us to figure it out.

Hope that helps.


Thanks,
Bart

Calen

Hi Bart,

Thanks for a quick reply.

When SQL Prompt is hung up, the CPU is mostly idle. SqlWb.exe will have from 0 to 5% and Mem Usage of around 820,000 KB.

This happens even with Cache Management set to NOT automatically update. It doesn't seem to care about that setting.

Another very annoying, but not as serious, aspect of all this... the semi-transparent window that says that SQL Prompt is indexing or loading in the lower right hand corner of the screen. It's there no matter what application window I bring up. This can be actually interfere with my being able to work on something else while SQL Prompt finishes doing whatever it thinks it's supposed to be doing.

I'm using a dual core 2.8 GHz Pentium 4 Dell machine with 2 GB of RAM, running Windows XP Pro (SP2).

Is there any way to stop SQL Prompt from doing that indexing and such, every single time I open a query? (Other than Task Manager, of course.)

Thanks,

Calen

iprudhomme

I have had very similar experiences with SSMS completely locking up during the reading object names or indexing objects, however I seems to occur for different reasons. If I open a query window with a saved sproc, and then use SQL Refactor to alter the layout while SQL Prompt is still doing it's reading object text or indexing objects, it compeletely locks up, my entire machine! Even Crtl-Alt-Del takes about 20 minutes to respond. This is repeatable everytime. Now I have a fairly large database, so it does take a while for SQL Prompt to load upwards of a minute to a couple of minutes. It has lockedup for other reasons, but I have not been able to isolate what the exact cause of those other times are from. During this lockup, the CPU usage goes off the chart pegging at 100% CPU.

Sorry for tagging on to this thread, but it was so similar that I didn't want to start another one. I have a Dell Laptop with a Pentium M 2.0Ghz with 2GBs of RAM

Bart Read

Hi Calen,


No problem. Happy to be able to help. From what you've said this sounds exactly like a deadlock to me, and unfortunately in that circumstance there's not much I can suggest, except an upgrade as soon as it becomes available. And again, in the new version we've changed the toast popup so you can close it at any time, but that doesn't help you much for now. Fortunately there's not too long to wait.


Thanks,
Bart

Bart Read

iprudhomme wrote:

I have had very similar experiences with SSMS completely locking up during the reading object names or indexing objects, however I seems to occur for different reasons. If I open a query window with a saved sproc, and then use SQL Refactor to alter the layout while SQL Prompt is still doing it's reading object text or indexing objects, it compeletely locks up, my entire machine! Even Crtl-Alt-Del takes about 20 minutes to respond. This is repeatable everytime. Now I have a fairly large database, so it does take a while for SQL Prompt to load upwards of a minute to a couple of minutes. It has lockedup for other reasons, but I have not been able to isolate what the exact cause of those other times are from. During this lockup, the CPU usage goes off the chart pegging at 100% CPU.

Sorry for tagging on to this thread, but it was so similar that I didn't want to start another one. I have a Dell Laptop with a Pentium M 2.0Ghz with 2GBs of RAM

Hi there,


That sounds like something a bit different to me. Does the layout SQL function in Refactor always exhibit this behaviour on the same SP regardless of whether Prompt is running?


Thanks,
Bart

iprudhomme

No, if I wait until SQL Prompt is finished initializing, it works just fine. Only when SQL Prompt is running and only when it's involved in the Reading tables or indexing data (which is the same place as I've seen other complaints of lockups).

Bart Read

OK thanks, it sounds like what's happening there is that reformatting the script causes the caret to move (obviously enough), then we do some checking to see if the right database is loaded for the new position of the caret in the script. In 3.1 this could potentially cause the editor to hang until such time as loading the meta-data is complete, so it looks like this is what you're seeing.

As I said above, we'll have a release candidate out very soon, however it might not be next Friday. We're having some problems with our toolbelt installer and trying to make it work with only SQL Prompt bundled in it for the RC, so it's possible it might slip a little, hopefully only a couple of days, and certainly no more than a week.


Thanks,
Bart

iprudhomme

Thank you Bart. I'll wait to see if this next release resolves the issue I'm having.

paschott

Thanks for the update, Bart. This is a minor issue for me, though I do notice that the larger the script, the slower the IDE is when I make a global change/replace (e.g. a large script to scrub data where I change a Customer ID globally each time I run it - a sadly manual process due to the potential to cause great damage)

I have to admit that I'm looking forward to the next release. Heck, I'd almost be willing to manually install the beta rather than mess with an installer.

-Pete

Bart Read

Hello again,


I wanted to let you know that the 3.5 RC is now available:

http://www.red-gate.com/messageboard/vi ... php?t=5263

Hopefully this will fix your SQL Server Management Studio issues.


Thanks,
Bart