Application crash

fatherjack2fatherjack2 Posts: 311
edited December 22, 2009 6:39PM in SQL Prompt Previous Versions
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

Senior DBA
Careers South West Ltd

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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

    Senior DBA
    Careers South West Ltd
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Jonathan,

    Are there any log files for SQL Prompt in %LOCALAPPDATA%\red gate\logs?
  • 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?

    Senior DBA
    Careers South West Ltd
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    How about %ALLUSERSPROFILE%\Local Settings\Application Data\Red Gate\SQL Prompt
  • no such folder :-/

    Senior DBA
    Careers South West Ltd
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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?
  • 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

    Senior DBA
    Careers South West Ltd
  • Hold on, will I lose my snippets if I uninstall?

    Senior DBA
    Careers South West Ltd
  • 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...

    Senior DBA
    Careers South West Ltd
  • 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

    Senior DBA
    Careers South West Ltd
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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.
  • fatherjack2fatherjack2 Posts: 311
    edited December 18, 2009 5:51AM
    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

    Senior DBA
    Careers South West Ltd
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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.)
  • 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

    Senior DBA
    Careers South West Ltd
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
Sign In or Register to comment.