sql prompt performance bad
ephraim
Posts: 17
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.
Comments
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?
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.
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?
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).
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!
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.
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.
Until now, the setting does not seem to have an effect on my side.
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?
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.
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
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?
Concerning the option to turn synonym support off: They are still displayed in all databases regardless of the option turned off.
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
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.
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!
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.
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).
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?
p.s. that was from me... I was logged in but it didn't stick I guess.
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.