SQL Prompt 5 working very slow on SSMS 2012

Provides intelligent code completion for SQL Server editors.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, Mike Upton, justin.caldicott, Aaron Law

SQL Prompt 5 working very slow on SSMS 2012

Postby Ar4i » Mon Jul 08, 2013 9:39 am

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.
Ar4i
 
Posts: 5
Joined: Mon Jul 08, 2013 9:28 am

Postby Manfred.Castro » Tue Jul 09, 2013 6:53 pm

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.
Manfred Castro
Product Support
Red Gate Software
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm

Postby LSZ » Thu Jul 25, 2013 10:33 am

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
LSZ
 
Posts: 7
Joined: Thu Jul 25, 2013 10:02 am

Postby Manfred.Castro » Fri Jul 26, 2013 5:35 pm

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.
Manfred Castro
Product Support
Red Gate Software
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm

Re:

Postby Ar4i » Mon Jul 29, 2013 8:56 am

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.
Ar4i
 
Posts: 5
Joined: Mon Jul 08, 2013 9:28 am

Postby LSZ » Tue Jul 30, 2013 10:43 am

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
 
Posts: 7
Joined: Thu Jul 25, 2013 10:02 am

Postby LSZ » Fri Aug 02, 2013 2:54 pm

Some additional counts:
SQL_SCALAR_FUNCTION = 386
SQL_STORED_PROCEDURE = 7943
SQL_TABLE_VALUED_FUNCTION = 71
LSZ
 
Posts: 7
Joined: Thu Jul 25, 2013 10:02 am

Postby Aaron Law » Mon Aug 05, 2013 11:19 am

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.
Aaron Law
 
Posts: 252
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby LSZ » Mon Aug 05, 2013 12:32 pm

Great news!
I'm really looking forward to downloading this new build.
LSZ
 
Posts: 7
Joined: Thu Jul 25, 2013 10:02 am

Postby Aaron Law » Tue Aug 13, 2013 5:00 pm

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!
Aaron Law
 
Posts: 252
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby LSZ » Wed Aug 14, 2013 12:55 pm

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
LSZ
 
Posts: 7
Joined: Thu Jul 25, 2013 10:02 am

Postby Aaron Law » Thu Aug 15, 2013 10:45 am

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!
Aaron Law
 
Posts: 252
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby Ar4i » Mon Aug 19, 2013 8:47 am

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.
Ar4i
 
Posts: 5
Joined: Mon Jul 08, 2013 9:28 am

Postby Aaron Law » Mon Aug 19, 2013 5:34 pm

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.
Aaron Law
 
Posts: 252
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby Ar4i » Tue Aug 20, 2013 3:29 pm

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.
Ar4i
 
Posts: 5
Joined: Mon Jul 08, 2013 9:28 am

Next

Return to SQL Prompt 5

Who is online

Users browsing this forum: No registered users and 0 guests

cron