Red Gate forums :: View topic - SQL Prompt 5 working very slow on SSMS 2012
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 5
SQL Prompt 5 forum

SQL Prompt 5 working very slow on SSMS 2012

Search in SQL Prompt 5 forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2  Next
Author Message
Ar4i



Joined: 08 Jul 2013
Posts: 5

PostPosted: Mon Jul 08, 2013 9:39 am    Post subject: SQL Prompt 5 working very slow on SSMS 2012 Reply with quote

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.
Back to top
View user's profile Send private message
Manfred.Castro



Joined: 23 Apr 2012
Posts: 209

PostPosted: Tue Jul 09, 2013 6:53 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
LSZ



Joined: 25 Jul 2013
Posts: 7

PostPosted: Thu Jul 25, 2013 10:33 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Manfred.Castro



Joined: 23 Apr 2012
Posts: 209

PostPosted: Fri Jul 26, 2013 5:35 pm    Post subject: Reply with quote

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/display/SC104/Working+with+snapshots
Email them to support@red-gate.com
Include F0074789 in the subject of the email message.
_________________
Manfred Castro
Product Support
Red Gate Software
Back to top
View user's profile Send private message
Ar4i



Joined: 08 Jul 2013
Posts: 5

PostPosted: Mon Jul 29, 2013 8:56 am    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
LSZ



Joined: 25 Jul 2013
Posts: 7

PostPosted: Tue Jul 30, 2013 10:43 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
LSZ



Joined: 25 Jul 2013
Posts: 7

PostPosted: Fri Aug 02, 2013 2:54 pm    Post subject: Reply with quote

Some additional counts:
SQL_SCALAR_FUNCTION = 386
SQL_STORED_PROCEDURE = 7943
SQL_TABLE_VALUED_FUNCTION = 71
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 219
Location: Red Gate Software

PostPosted: Mon Aug 05, 2013 11:19 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
LSZ



Joined: 25 Jul 2013
Posts: 7

PostPosted: Mon Aug 05, 2013 12:32 pm    Post subject: Reply with quote

Great news!
I'm really looking forward to downloading this new build.
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 219
Location: Red Gate Software

PostPosted: Tue Aug 13, 2013 5:00 pm    Post subject: Reply with quote

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/SQLPromptUnstable/SQLPromptUnstable_6.0.0.342.exe

Thanks!
Back to top
View user's profile Send private message
LSZ



Joined: 25 Jul 2013
Posts: 7

PostPosted: Wed Aug 14, 2013 12:55 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 219
Location: Red Gate Software

PostPosted: Thu Aug 15, 2013 10:45 am    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
Ar4i



Joined: 08 Jul 2013
Posts: 5

PostPosted: Mon Aug 19, 2013 8:47 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 219
Location: Red Gate Software

PostPosted: Mon Aug 19, 2013 5:34 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Ar4i



Joined: 08 Jul 2013
Posts: 5

PostPosted: Tue Aug 20, 2013 3:29 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2  Next
Page 1 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group