SQL Prompt 5 working very slow on SSMS 2012

Ar4i

Having code suggestions enabled in SQL Prompt 5 menu makes editing in SSMS work very slow even on a relatively small scripts (like 50-100 lines). On larger scripts it is completely unusable.

It is working the same way on multiple PCs all with a modern hardware i5 CPUs and 8GB RAM.
The DBs we are working are around 20-30K rows, caching is pretty fast, but there is a delay between each keystroke that increases with script size. Even moving around with arrows is slowing down considerably.

Manfred.Castro

Can you check that your graphics card drivers are up to date? This has been known to affect the performance of SQL Prompt.

If you graphics card driver is up to date, It may be a case of SQL Prompt struggling with the number of columns in your databases.

By default SQL prompt will attempt to cache all collumns of databases that you are connected to.

Since the memory available to SQL Prompt is limited by SSMS, If you are connected to a large amount of databases or databases with a large amount of columns or a combination of the two it'll eventually fill up its cache of columns for suggestions, but most users don't ever hit that limit. If it does sound like it's just a case of too many columns, there's a few things you can do

1.) There's a config option in %LOCALAPPDATA%\Red Gate\SQL Prompt 5\RedGate_SqlPrompt_Engine_EngineOptions.xml called <MaxSupportedColumns> - you can use this to limit the size (in columns) of a database that SQL Prompt will load. If you set it, to, say, <MaxSupportedColumns>20000</MaxSupportedColumns>, SQL Prompt will give up on loading columns for databases with more than 20,000 columns.

This is really to stop individual giant databases crashing Prompt.

2.) Sometimes just clearing the cache helps. Restarting SSMS will clear SQL Prompt's cache. So when SQL Prompt becomes sluggish you can try restarting ssms before it runs out of memory.

3.) Another option that may help is disabling linked server support in SQL Prompt.

This will prevent SQL Prompt from attempting to provide suggestions from a linked server whose columns will be loaded into SQL Prompt's memory.

LSZ

Hi Manfred,
I have latest drivers installed, my graphics is Intel HD Graphics 4000.
My laptop has Core i7, 8GB RAM and SSD.

Previously in my company we were using SQL Prompt 3.9 and recently we installed version 5.3 and since then SSMS 2008 and 2012 are extremely sluggish. The response time to keystrokes is not acceptable, we can't work with code suggestions enabled any more. The thing is, that when I change the context of the script to eg. master database, SSMS works great, so evidently there is a problem with databases with complex schemas.

I even installed Prompt version 6.0.0.168 and it is the same, extremely sluggish!

Please do something about it, otherwise we will be forced to go back to version 3.9 or try other products.

--
Regards,
LSZ

Manfred.Castro

We have been unable to replicate this in our test environments.
Would you be able to send us snapshots of the databases that the tool struggles with?
http://documentation.red-gate.com/displ ... +snapshots
Email them to support@red-gate.com
Include F0074789 in the subject of the email message.

Ar4i

Manfred.Castro wrote:

Can you check that your graphics card drivers are up to date? This has been known to affect the performance of SQL Prompt.

Installed the newest version - no change.

Manfred.Castro wrote:

1.) There's a config option in %LOCALAPPDATA%\Red Gate\SQL Prompt 5\RedGate_SqlPrompt_Engine_EngineOptions.xml called <MaxSupportedColumns> - you can use this to limit the size (in columns) of a database that SQL Prompt will load. If you set it, to, say, <MaxSupportedColumns>20000</MaxSupportedColumns>, SQL Prompt will give up on loading columns for databases with more than 20,000 columns.

This is really to stop individual giant databases crashing Prompt.

We have no individual giant databases, we work 99% of the time with databases with exactly the same structure.

Manfred.Castro wrote:

2.) Sometimes just clearing the cache helps. Restarting SSMS will clear SQL Prompt's cache. So when SQL Prompt becomes sluggish you can try restarting ssms before it runs out of memory.

It is extremely "sluggish" right from the start.

Manfred.Castro wrote:

3.) Another option that may help is disabling linked server support in SQL Prompt.

There are no linked servers.

I've tried with a small database (1800 columns) and it runs ok, but with our production databases it is unbearable.
If that has anything to do with the problems - we are using one big schema, because our database structure exists way before schemas were even introduced in SQL Server. The "sluggish behavior is present even when browsing the DB structure in object explorer.

LSZ

Manfred,
I don't think I am allowed to share our company's proprietary database schema, but I can give you some "metadata", such as:
1. count of user tables in dbo schema (this is the only schema that we use) is 2751
2. count of all columns in user tables is 28933

If you need some more information please ask.

Regards,
LSZ

LSZ

Some additional counts:
SQL_SCALAR_FUNCTION = 386
SQL_STORED_PROCEDURE = 7943
SQL_TABLE_VALUED_FUNCTION = 71

Aaron L

Thank you for the stats.
We've been able to recreate a slowdown internally (hopefully the same one you’re seeing) and we’re currently rewriting the offending code. I’ll update this post when we release a new beta build with the rewrite in, which will probably be early next week.

LSZ

Great news!
I'm really looking forward to downloading this new build.

Aaron L

Unfortunately it looks like these changes are more complicated than we anticipated and so they’ll take a little longer to enter our beta.
Just to make sure we’re on the right track would it be possible to give this unstable build a quick try and tell me if the performance issues are fixed for you?
http://download.red-gate.com/EAP/SQLPro ... .0.342.exe

Thanks!

LSZ

Aaron,
It's way better than previous versions (after 3.9).
I gave it a try with the same scripts and database that previous versions were struggling with and now It's not sluggish. Still I wish it could be faster with displaying suggestions, but as I said - it's way better so your team is on the right path.

I think I'll work with this version a little longer and if I encounter any problems I will write about them here.

Thanks for your efforts,
LSZ

Aaron L

Thank you for trying it out.

I’m still not entirely sure why it takes so long to calculate the suggestions for you (to recreate the slowdown internally we created a database with 10x your object counts).
Do all scripts run slow (Even just a select * from)? Or is it only larger ones or ones with specific objects/statements in?
Also, have you tried on a machine that hasn’t previously had prompt 3.9 installed (I’m wondering if some left-over settings could be influencing this)

Thanks for helping us track this down!

Ar4i

We have installed it on several brand new clean PCs (Prompt 5.3.8.2 pro) and our databases are completely unrelated and different than the ones of LSZ (although they seem to be of a similar complexity), still SQL Prompt seems to work the same way. Everyone of my colleagues already uninstalled it.

I'll download and try the above version, although I'm not aware of the licensing policy of your company and I don't know if we are going to able to use it once it is a final product. Unfortunately the version we bought seems to be useless for us at the moment.

Aaron L

Hi Ar4i,
That rules out this being caused by some old settings being left over from prompt 3 at least.
I’m currently looking into optimizing the overall performance of prompt, but I’m still a little concerned that I might be missing the main cause of the slow down for you and LSZ. If you look in your prompt log can you see any errors or warnings logged? (This can be found in the menu SQL Prompt->Help->Show Log)

I’m not sure which license your company currently has but if we can get this fixed I’m sure we can work something out to get you onto a version with a fix once it’s out of beta.

Ar4i

There are only INFO messages in the log, no errors or warnings.

I've uninstalled Prompt 5 and installed the above unstable release and it is now working a lot faster at least during the short test I managed to do. There is a minimal lag when displaying suggestions, but the real big lag on every key press seems to be gone - which is great.
I did notice however that when I open a stored procedure there aren't automatic suggestions ([ctrl+space] works). While if I start a new script, suggestions do pop up automatically.
However there is still lag when browsing the object explorer which is not critical.

It is a bit out of topic, but are functions "expand wildcards" and "qualify object names" supposed to work within stored procedures, functions and triggers and especially with temporary tables and table declarations? They don't seem to work in this version.

I'll try using this version for a few days to see how it performs.

Aaron L

Thanks for the update!

From the sounds of it I should focus on script parsing performance if longer scripts don’t show suggestions immediately but short ones do.

We've offloaded the suggestion processing to a thread which avoids locking up SSMS on more complex scripts but if the thread hasn't completed in time then no suggestions will popup unless you explicitly ask for them with ctrl+space. We’re looking into fixing this at the moment.

Expanding wildcards and qualify object names should be working in stored procedures and could be another bug, which I'll look into.

Ar4i

Not getting a popup immediately is definitely better than everything freezing for a few seconds on every keystroke even when just moving the cursor with arrows and mouse. Maybe you could avoid doing any parsing while just moving around and not typing? With the official beta version it freezes even when clicking to open a menu.
Other than that, so far this version works pretty well.

P.S. Expand wildcards seems to be CASE SENSITIVE thats why it isn't working in some cases, our databases are CI. Thats true for other functions as well and let to the confusion that there is a problem in SPs with declared and temporary tables.

LSZ

Hi,
sorry that I haven't been participating in this thread for a long time, but I was quite busy at work. In fact while working I was testing this unstable release that Aaron posted and I hold the opinion that it's better and even on larger scripts it's not being so sluggish.

I would like to share few of my observations:
1. Suggestions box is still taking some time to show up on my large database, maybe because of the RegEx feature that is searching for the match in table names etc. ?
2. We are using a lot of XML in my company, eg.

;WITH XMLNAMESPACES
(
	'http://tempuri/targetns/v3' AS tns
)
SELECT 
	t_si.position,
	row.value('./something_1', 'VARCHAR(50)'),
	row.value('./something_2', 'DATETIME'),
	row.value('./something_3', 'INT')
FROM
	@someItems t_si
	CROSS APPLY @XMLVar.nodes('/tns:root/list/relatedItem[position()=sql:column("t_si.position")]') T1(row)

maybe this is the case ?
3. The table variables (DECLARE @t_name TABLE...) are not showing up in suggestions box.
4. In my error log I am seeing such entires:

30 Aug 2013 08:09:44,995 [Main] WARN RedGate.SQLQueryKeeper.TabWatcher - Exception trying to log MostRecentView information
System.Runtime.InteropServices.InvalidComObjectException: COM object that has been separated from its underlying RCW cannot be used.
at #B4sb.#D4sb.#gCSb()

Aaron, when you are planning to release the stable version ?

Regards,
LSZ

Aaron L

Hi LSZ,
Unfortunately it's going to be a while before this is made into a stable version as we've found a few problems with these changes that'll require a bit of a restructure of prompt to solve.

I'm still looking into it and will try to release another build with this change and some bug fixes soon.

LSZ

Hi,
All right, I'm looking forward to next build(s), because the build that I'm using now expires 2013-09-30

Regards,
LSZ

Aaron L

I've just finished fixing the expand wildcards and a few other issues and released these changes as part of our 6.1 beta which can be downloaded from http://download.red-gate.com/EAP/SQLPro ... Prompt.zip