sql prompt performance bad

ephraimephraim Posts: 17
edited September 24, 2013 10:18AM in SQL Prompt
Hi, I just upgraded to sqlprompt 6, performance is really bad on code suggestions. Serious keyboard lag. Was okay on 5.2. Upgraded to 6.1 beta... slightly better but still unacceptable.
«1

Comments

  • Aaron LAaron L Posts: 596 New member
    Hi,
    One of the things that could be causing problems is partial matching was turned on in 5.3, you could try disabling this to see if the performance improves by setting:
    <PartialMatchingEnabled>False</PartialMatchingEnabled>
    in
    %localappdata%\Red Gate\SQL Prompt 6\RedGate_SqlPrompt_Engine_EngineOptions.xml

    I've been trying to track down the performance problems for the past few weeks so please let me know if this helps. Also, is your database case sensitive?
  • kondrichkondrich Posts: 79 Bronze 2
    We are also suffering from those lags with v6.
    E.g. after typing SELECT * FROM the list of objects appears, but cursor key strokes lag several seconds in suggestion window.

    But neiter setting <PartialMatchingEnabled> to false nor updating to 6.1 beta brought a solution.
    Regards, Klaus
  • Aaron LAaron L Posts: 596 New member
    Hi Klaus,
    I noticed you had reported this problem with 5.3.8.2, did you find the slow down was introduced in that build and it was fine for you in 5.2 and earlier?
  • kondrichkondrich Posts: 79 Bronze 2
    Oh, so long ago? I must admit that I cannot remember the exact/first version anymore.
    Btw, Partial Matching is great! It's ok to turn it off for testing as I did before. But permanently turning it off? That would not be our choice.

    My test for disabled Partial Matching was just a single SELECT * FROM statement in a new query window. The first call of the suggestion objects lagged concerning key strokes. Succeeding calls within the same SELECT query do not lag. Adding another query causes lag again in this particular one (here again: just upon first call of suggestions).
    Regards, Klaus
  • Aaron LAaron L Posts: 596 New member
    Unfortunately we've been unable to recreate this internally which makes it difficult to fix. Would either of you be able to send us a snapshot of your database to test with?
  • kondrichkondrich Posts: 79 Bronze 2
    For compliance reasons: No

    I just tested with various databases and there is a strong evidence that it has to to with synonyms. If there exist (many?) synonyms then I can reproduce those lags (it does not matter whether they point to another database on the local instance or to a linked server).
    If I dropped all synonyms in this database, no lags occur (dropping just synonyms pointing to linked servers does not help). My test database contains 60 synonyms, that is very common in our environment.

    My first guess was the count of objects in the considered databse, but it does not matter. SSISDB or other tool specific repository databases contain many objects and no lags occur (there exist no synonyms in SSISDB).

    On those very few database containing no synonyms, I do not have any lags.

    This is very surprising to me, since I obviously reported this issue since v5.3 that had no synonym resolution at all (though it suggested their names).

    Aaron, could you test and reproduce this issue with databases containing a lot of synonyms?
    I hope this helps!
    Regards, Klaus
  • Aaron LAaron L Posts: 596 New member
    Hi Klaus,
    That narrows it down quite a bit, thank you!

    As a possible workaround for now there’s an option to disable synonyms in the 6.1 Beta, which can be done by setting <SynonymsEnabled>False</SynonymsEnabled> in the engine options xml file.

    I’ll see if I can recreate this today and then look into a proper fix for you.
  • kondrichkondrich Posts: 79 Bronze 2
    Aaron,

    thanks for mentioning this option here. I discovered it by myself and changed it to false. However, synonyms are still in the suggestion lists. Why? Because they were cached?
    This is why I did not write about it in my previous post.
    Regards, Klaus
  • Aaron LAaron L Posts: 596 New member
    Yep, if the synonyms are already cached in memory then that option won’t take effect until the cache is cleared. It works by removing synonyms from the cache query.
  • kondrichkondrich Posts: 79 Bronze 2
    So I keep it set to False. However, synonyms are still suggested (even in databases that I did not access for long time). Is there a way to manually purge this cache? Or is it purged at closing SSMS?
    Until now, the setting does not seem to have an effect on my side.
    Regards, Klaus
  • Aaron LAaron L Posts: 596 New member
    Closing SSMS will clear it as should refreshing suggestions. The option was only added in 6.1.0.27 so if you're on an earlier build it also won't have any effect.

    Unfortunately i'm still unable to recreate the slow down here after creating 400 synonyms (a mixture of table, view, stored proc and dangling synonyms). For your 60 synonyms are they all pointing to different databases or servers?
  • kondrichkondrich Posts: 79 Bronze 2
    6.1.0.27 is currently installed here. I closed SSMS several times now (e.g. during changing this option).

    Our synonyms are pointing to other databases on the same instance as well as pointing to other instances running on different machines.

    I just did a fresh install of SQL Prompt 6.1.0.27 on one of our servers, checked the lags and got lags (shorter, since the server is faster than my client).
    Then I changed to <SynonymsEnabled>False</SynonymsEnabled> and tried another database on another server as before (to exclude possible caching): Lags again and the synonyms are still suggested.
    Regards, Klaus
  • I can ask if we can send you a snapshot, but I'm wondering if you've tried to reproduce this on a large database. This is the result of:
    select type, count (*) from sys.objects as o group by type
    TR 156
    SQ 3
    FN 2
    S 45
    D 23
    IT 7
    F 58
    PK 115
    P 783
    U 244
    TF 7
    R 2
    C 10
    IF 8
    V 10
  • Can't do a snapshot. Possibly I could do a gotomeeting with you. If you want to set that up, send me your contact details.
  • Aaron LAaron L Posts: 596 New member
    It looks like we've got two separate issues here, as you don’t seem to have any synonyms ephraim?
    Unfortunately I’m still unable to recreate either, my test database has a reasonable number of objects and I'm unable to get a slow down:
    TR 36
    SQ 3
    S 41
    D 1
    IT 3
    F 334
    PK 266
    FN 10
    P 5673
    U 268
    C 1
    SN 405
    UQ 1
    V 278
    I'll send you an email with my contact details later today if you'd still be willing to setup a gotomeeting.

    @Klaus: I’m not sure why the option isn't working for you, I'll promote it to an option in the UI and send you another build later today. Is there anything else you could think of that might help me recreate your issue?
  • kondrichkondrich Posts: 79 Bronze 2
    Aaron, would it be possible for you to provide your database to us? I would like to cross-check this issue with your database.

    Concerning the option to turn synonym support off: They are still displayed in all databases regardless of the option turned off.
    Regards, Klaus
  • Right, no synonyms on this database... I use synonmns on another database and it goes fast, but it has much less SQL objects. To me this seems directly related to just the total number of SQL objects that are held in memory that need to be scanned. I'd be happy to host a gotomeeting to demonstrate.
  • kondrichkondrich Posts: 79 Bronze 2
    Database examples with lags:
    TR 3
    SQ 3
    FN 10
    S 67
    D 1301
    IT 6
    PK 50
    P 57
    U 50
    TF 1
    SN 87
    UQ 2
    IF 1
    V 202

    SQ 3
    FN 1
    S 67
    D 450
    IT 6
    PK 7
    P 9
    U 9
    SN 103
    V 86

    TR 10
    SQ 3
    FN 5
    S 67
    D 702
    IT 6
    PK 33
    P 47
    U 33
    C 5
    SN 29
    UQ 2
    V 39

    TR 8
    SQ 3
    FN 5
    S 67
    D 461
    IT 6
    PK 141
    P 418
    U 174
    SN 158
    UQ 5
    IF 4
    V 38

    Database examples without lags:
    SSISDB
    PC 10
    SQ 3
    FN 11
    TT 3
    S 67
    IT 6
    F 28
    PK 30
    P 86
    U 30
    FS 4
    TF 2
    C 4
    FT 2
    UQ 4
    V 33

    ReportServer:
    TR 7
    SQ 3
    S 67
    D 8
    IT 6
    F 26
    PK 31
    P 236
    U 34
    C 3
    UQ 1
    IF 1
    V 5

    SQ 3
    FN 1
    S 67
    D 43
    IT 6
    F 5
    PK 33
    P 42
    U 33
    V 41
    Regards, Klaus
  • Aaron LAaron L Posts: 596 New member
    I've just updated our beta so it has the option to turn off synonyms under the connections page. I'd be very interested to see if you still see a slow down on your databases with this unticked?
  • First of all: The new option for disabling synonym suggestions in GUI is working. When disabled, no synonyms are suggested anymore. Thanks, Aaron!
    However, Beta 6.1.0.27 & .33 both reset various settings to defaults including aliases (all customizations are gone!), swap order in join clauses and others. Is this intended?

    @Lags:
    Honestly, it's driving me nuts.

    When disabling synonyms at the first time, it was lightning fast. Then I came to databases that showed lags again.
    I realized that immediately after the lag my partial object string is red underlined by IntelliSense until I choose a suggestion (SSMS built-in auto-list members is off).
    Hence, I turned off IntelliSense and the lags are gone. Until they reappeared after trying and trying again.
    So I reenabled IntelliSense and synonym support in SQL Prompt and right now I do not have any lags.

    What I also realized: When typing an object name from its beginning, there are never lags. Just when I type a partial match like "SELECT * FROM ite" when I want to get a suggestion for tDim_Item.
    Regards, Klaus
  • I am one of the slowest typers in our company and it can't even keep up with me. The other database developers that upgraded are really screaming or turning off/uninstalling it. Better do something or no one is going to want to renew the license next time.
  • Aaron LAaron L Posts: 596 New member
    I had a gotomeeting with Ephraim which helped show the problem. Reverting to 5.2 seems to eliminate the lag for him too which you can get from here(you'll need to uninstall 6 first)

    Klaus, I think this might be due to the partial matching after all.
    It looks like we had a bug with the options loading in 6.1 which should now be fixed in 6.1.0.40so when we tried disabling it from the xml before it didn't take effect (If you'd like to keep your settings from 5 just delete the prompt 6 engine options xml file and it'll copy them over from the last version)
    After you've got a working xml options file could you try disabling synonyms and partial matching again and see if that eliminates the lag for you.

    As you mentioned before partial matching and synonyms are valuable features so I'm working on getting it running lag-free with these enabled.

    CGLuttrell: Could you also try getting 6.1.0.40 and disabling synonyms and partial matching and let me know if this fixes the issue for you? Thanks!
  • kondrichkondrich Posts: 79 Bronze 2
    Testing with synonyms and partial matching disabled I also get lags - if and only if IntelliSense is enabled as well (this is weird enough, since I wrote yesterday that I did not get lags when typing objects names from their beginning).
    I am able to reproduce this on my client and on a server with SQL Prompt 6.1.0.40.

    After install of 6.1.0.40 I manually copied my aliases from a backupped RedGate_SqlPrompt_Engine_EngineOptions.xml, since they were deleted again.
    Regards, Klaus
  • Aaron LAaron L Posts: 596 New member
    I've just uploaded 6.1.0.42 which includes the work I was doing with offloading the the prompt work to a thread, this should hopefully remove all of the lag you're seeing in ssms.
  • 6.1.0.42 behaves exactly as I described in my last post. Unfortunately still lags with IntelliSense enabled.

    Now I have a guess, why we realized those lags some weeks/months ago: We migrated from SQL Server 2005 to 2012 in April. So we could not use IntelliSense before that, even when using SSMS 2012 with SQL Server 2005 (IntelliSense just works with SQL Server 2008 and above).
    Regards, Klaus
  • Aaron LAaron L Posts: 596 New member
    Hi Klaus,
    Is this the built in SSMS intellisense you're referring to rather than prompt's? (as I think prompt has always worked with sql2005)

    Would you be willing to hold a remote session with us to demo the problem and see if there's anything else we can notice?
  • Aaron LAaron L Posts: 596 New member
    Another thing to try would be updating your display drivers (someone had a similar issue here http://www.red-gate.com/messageboard/vi ... hp?t=10410 )
  • Anonymous wrote:
    Using the version 6.0.0.441 and I have to say it is a major disappointment in performance. I'm on line 385 of a script file and significant lag is experienced when typing.

    Script is made up of creation of 2 temp tables and 5 large queries that insert into real and temp tables.

    I also experience similar slow performance on scripts that had a number of table valued UDF's involved.

    When I get done with a production priority I can do more testing, but right now I think the team has some serious work ahead of them. I have to question the test sets redgate works with. They are obviously woefully insufficient.

    p.s. that was from me... I was logged in but it didn't stick I guess.
  • Aaron Law wrote:
    I've just uploaded 6.1.0.42 which includes the work I was doing with offloading the the prompt work to a thread, this should hopefully remove all of the lag you're seeing in ssms.

    I will try this version when I am not having to use my system and can reinstall. Partial searching is on though I can test with it off.
  • Aaron, I downloaded your 6.1.0.42 version and it does seem better for me so far. I turned off synonyms but did not see the option for partial matches. I will continue to test and monitor for future updates.
Sign In or Register to comment.