Application crash

fatherjack2

Hi,

does anyone else suffer with a total SSMS stop and sometimes crash when creating a script with a self join? ie

select e1.colA, e1.colB, e2.colC from employees as e1 inner join employees as e2 on e1.managerid = e2.staffid

I get SSMS using 50% CPU for minutes at a time and then my patience expires (5 minutes+) and I crash out with Task Manager.

I am using XP sp3, dual core 3Ghz, 4GB RAM

cheers

Jonathan

Brian Donahue

Hi,

Can you please provide some details about the crash, such as anything in the error dialog or event logs?

SSMS add-ins like prompt should not terminate SSMS unless the computer configuration is corrupt or there aren't enough resources available to do whatever processing is necessary.

fatherjack2

Hi Brian,

There is no dialogue, it just stops. CPU trace shows 1 core dedicated 100% to SSMS and its unresponsive to everything but Task Manager.

I cant find any related entries in any log.

Jonathan

Brian Donahue

Hi Jonathan,

Are there any log files for SQL Prompt in %LOCALAPPDATA%\red gate\logs?

fatherjack2

Hi Brian,

I cant find any such folder I'm afraid ... I am on XP sp3 so checked
C:\Documents and Settings\allenj\Application Data\Red Gate
and there are only SQL Backup, SQL Multi Script, SQL Refactor and SQL Response Client folders
and
C:\Documents and Settings\All Users\Application Data\Red Gate
where there are Licences and SQL Search folders.

where else can I look?

Brian Donahue

How about %ALLUSERSPROFILE%\Local Settings\Application Data\Red Gate\SQL Prompt

fatherjack2

no such folder :-/

Brian Donahue

I'm starting to suspect that the Prompt add-in ins't even loading. It should create a log folder, provided it's the latest or reasonably recent version.

Can you uninstall Prompt and see if SSMS still crashes?

fatherjack2

I'll give it a go Brian.

The SQL Prompt menu is in SSMS and the tray icon is there, object suggestions pop up and the Ctrl + ... key combinations work. The About SQL Prompt details says v 4.0.3.10 is on the PC.

I'll get back once I've uninstalled and reinstalled.

Jonathan

fatherjack2

Hold on, will I lose my snippets if I uninstall?

fatherjack2

yet another post!

found the snippets via the Snippet Manager (Locate on Disk is a nice feature) here:
C:\Documents and Settings\allenj\Local Settings\Application Data\Red Gate\SQL Prompt 4 in the same level there is a Logs folder that has 3 folders - SQL Backup, SQL Object Level Recovery Pro 1 and SQL Response 1. Not sure how I missed these, I will have to ditch windows search index and get some other indexing tool...

fatherjack2

Right.
Uninstalled.
Reinstalled.
Upgraded to 4.0.3.10
tried query

Select pr.knownas, pr.surname from personnel as pr inner join personnel as pr2 on

aaaand the system locks. After a few minutes you can get in to the SSMS UI and slowly step by step select the query and delete it and then all returns to normal but it is a slow process as you have to wait for a very delayed response.

It seems to be having trouble gathering all the join options. The table has 368 columns... could be a problem if its trying to provide the cross join of all of those as possibilities?

cheers

Jonathan

Brian Donahue

Hello,

Since the Prompt code is all managed, it should not cause the SSMS to lock up with no explanation -- an error in Prompt would either cause an error dialog to appear or a message about the add-in being unloaded. That usually indicates a kernel-level fault, maybe a dodgy graphics driver or something like that.

MikeyC

fatherjack wrote:

Select pr.knownas, pr.surname from personnel as pr inner join personnel as pr2 on

aaaand the system locks. After a few minutes you can get in to the SSMS UI and slowly step by step select the query and delete it and then all returns to normal but it is a slow process as you have to wait for a very delayed response.

It seems to be having trouble gathering all the join options. The table has 368 columns... could be a problem if its trying to provide the cross join of all of those as possibilities?

If I do a query like that the suggestions come immediately, but the table I did a self join on only had 232 columns, maybe your extra 136 columns go over some limit?

Also, I am running 4.0.3.12, if you are really on 4.0.3.10 you might try upgrading.

fatherjack2

Hi CodantiM,

Thanks for the info. When I check for updates I get told that there are No updates available ... how did you get hold of 4.0.3.12?!

I have now also tried it on tables with fewer rows:
columnspacetime to
countspaceload list
114..............1s
277..............15-18s
326..............35-40s

on the last one the list doesnt show to screen but the cursor becomes available for editing. This list would be enormous 326x326=106k suggestion rows!

2 things:
1 - there is a definite increase in the time as the column count grows and I think that when I am working with the 368 column table I am just seeing an extension of the series above (368x368=136k suggestions)
2 - my PC is not showing the 277 table anywhere as quickly as yours shows 232. Do you actually mean immediately or is it just nice and quick!? I cant test exactly as I cant find any tables between the 114 and the 277 at the moment.

regards
Jonathan

Brian Donahue

Those two versions are exactly the same software: the difference is that one had been downloaded from the website and the other from Check For Updates. Someone at Red Gate did this to try to track how people were updating SQL Prompt.

MikeyC

fatherjack wrote:

Hi CodantiM,

Thanks for the info. When I check for updates I get told that there are No updates available ... how did you get hold of 4.0.3.12?!

From the link posted in the forum: http://www.red-gate.com/MessageBoard/vi ... php?t=9993

on the last one the list doesnt show to screen but the cursor becomes available for editing. This list would be enormous 326x326=106k suggestion rows!

It doesn't match every field to every field does it, only fields where the field name matches...

2 things:
2 - my PC is not showing the 277 table anywhere as quickly as yours shows 232. Do you actually mean immediately or is it just nice and quick!? I cant test exactly as I cant find any tables between the 114 and the 277 at the moment.

The suggestions come up in less than a second for me with 232 columns. You must have the option selected to show join matches based on data type, if I turn that option on then SSMS locks up for about 30 seconds before the suggestions pop up. Even with that option selected it shouldn't match every field to every other field unless all fields were of the same data type. In any case do you really want the "Columns with matching data types" selected? (I know I don't.)

fatherjack2

Ah, great spot CodantiM, I did indeed have that selected and now that it is unchecked things are not locking up with a self join on the biggest table.

Many thanks.

RedGate - it may be worth adding a note to the UI by the Columns with matching data types check box that indicates it could cause performance issues on large tables..? And/Or maybe getting the suggestion dialog to display a note that it has timed out trying to match all columns and is showing an abbreviated list after maybe 10s or so??

Thanks again

Jonathan

Brian Donahue

Hi,

Thanks for all of your help! I'll raise this as an issue for the docs and save some people this trouble in the future.