Cache Refresh (Auto) takes forever and hangs VS

PDinCA

Upgraded to 3.5 after uninstall of 3.5 RC and reboot.

Cache manager window is still open after about an hour of work. It sometimes shows the connection and progress, other times just the logo, title and nothing - for minutes on end.

The SP I'm editing generates dynamic SQL but is only 650 lines - not that large. The other window was a simple pair of SELECTs for an ad-hoc query.

Response times are very variable - sometimes 10 seconds between keystrokes, other times "normal", other times "take a coffee break" with the "Management Studio is busy" balloon appearing occasionally.

No exceptions triggered but it appears to be stuck "Finishing off..."

2gigs of memory with over 700MB free. Configuration:

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.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,


The best thing I can suggest is that you disable the automatic refresh and just use the CTRL+SHIFT+D shortcut to refresh the database you're currently working on whenever you need to.

Hope that helps.


Thanks,
Bart

PDinCA

Sorta helps, but cache refresh still takes 5 minutes and the DB is not large by many standards - 100 tables, a few views, about 400 SPs and 100 UDFs.

Bart Read

This might be for a variety of reasons. What's the CPU usage like when this happens? If it's low then it suggests that either the connection is slow or the server is under heavy load. It's possible, although less likely, that it could be due to overloading of the client machine. I think this would really only become an issue if you were using a lot of virtual memory and Windows would have to do a lot of swapping.


Thanks,
Bart

PDinCA

I'll keep an eye on it, but suffice to say:

Client has 2gig RAM. I Use FreeRam Pro, which does a great job of minimizing paging and keeps memory clear of redundant artifacts - I have over 300MB free now and usually around 250MB. I just did a Ctrl+D refresh and it took up to 65% CPU. Production Server is never busy - if it gets to 10% we're having a busy day! Connection is a fat VPN tunnel - that may choke occasionally so I'll ask our network chaps if they have some measurement tools they can look at the times when this occurs. Client is usually running at low tens% CPU - even with Outlook in the background, SS2000, Word and Excel in play.

Bart Read

Yeah, I didn't think it would be the client: the most likely issue would have been insufficient physical RAM but 2 GB is way more than enough. Interesting that it takes 5 minutes to refresh that database though. Is is just that one database?

So a few follow-up questions:

- Do the tables/views have an unusually large number of columns?
- How about your SPs, are they small, medium, large? (I think it was you that said you had one that was 5000 lines of SQL or something.)
- Same for the functions, any really big ones in there?
- One other thing to check, although this shouldn't be an issue, could you verify that the About box version number is 3.5.0.333 please?

It's difficult to say how long any given database might take to cache, but even a database with a couple of hundred tables might take a while if all those tables have got 50 columns and all the SPs are 1000 lines long.


Thanks,
Bart

PDinCA

There's only 1 table with over 50 columns - the typical business entity has 15 to 20, with classifying tables having from 4 to 8 columns, then the 'slave' tables may have 10 to 15. There are 166 tables of type 'U' in the database I most frequently connect to.

We have a few 'search' SPs that form dynamic SQL and execute it, but that's only 6, each having 4 to 8 child SPs that specialize in either a canned query or do the dynamic thing - these are the 500+ SPs. Maybe 10 others run to 500 lines or so - the largest is 1325 lines and does a load of extract-format-load for our fulfillment transmission. I use comments heavily and employ the one-column per line in any SELECT, UPDATE, INSERT I code, so that ups the line count, i.e., it's not "dense code".

The largest UDF is a full-blown Receivables Aging across 4 buckets in a single SELECT - 420 lines. Lots of fun that one! A few others run to 300, some to 200, but most are 50 or less. - the majority are, again, decently commented, where each comment is at least 3 lines deep.

There are approximately 250 SPs and 200 UDFs (having taken a fresh look rather than a gut feel).

Version verified at 3.5.0.333

I noticed the worst hangs were when I made an SP change in DEV, then changed connection to Staging, then UAT in quick succession, and deployed to each target DB, then changed back to DEV. That was when I had auto-refresh on, but that's off right now. I still had MS hang yesterday while trying to edit the 650 liner...

Like I said, I'll keep an eye on things and see if I can spot a trend...

Hope this helps.

Bart Read

OK thanks, I must admit I'm drawing a blank at the moment, but if I think of anything I'll post up here. We'll obviously keep an eye out here and fix anything nefarious that might cause this.
Thanks,
Bart