SSMS 2017 becomes sluggish over time since installing SQL Prompt 9

DBAJohnny

I have been on SSMS 2017 (build 14.0.17199.0) since before the release of SQL Prompt 9. I often work on a given script over several days, and have found that, since installing SQL Prompt 9, SSMS becomes increasingly sluggish over time.

When the response time becomes unbearable, I can close SSMS and reopen, and SSMS behaves normally again, at least for a time.

But I'm having to do this restart every three-four days. Is this a known issue?

torsten.strauss

Hi!

I raised a ticket regarding CPU allocation which describes the same behavior. So it would be interesting to know if you can see a massive CPU allocation when this issue comes up.

Torsten

DBAJohnny

I downloaded Microsoft's ProcessExplorer and am running it. I don't see a huge CPU imprint (0.5 - 0.6). I do notice that the memory imprint of SSMS rose overnight from 840M to 970M. The sluggishness has started again, so I'm going to restart SSMS again (last

Tianjiao_Li

Hi @DBAJohnny

Thanks for posting.

Our development team has lined up some work to improve Prompt in term of slowness issue. It would be very helpful if you could:
1- Confirm how many tabs do you have open when the issue started
2- Confirm if disable Code Analysis function make any difference
3- Share a sample script with us in order to reproduce the issue locally here
4- Confirm the full version number of your SQL Prompt

Thanks for your assistance and sorry for the inconvenience caused.

DBAJohnny

torsten.strauss: I just experienced SSMS going wild on the CPU usage (pegged at 25% on each of my 4 processors). This happened even though I had restarted SSMS this morning.

Tianjiao_Li: Thank you for getting back to me. Here's a first pass at answers:

1. I typically operate with 4-8 tabs open, sometimes more, but rarely. One of the tabs is always SQL Source Control. I also typically have the Object Explorer open.
2. I have turned off the Code Analysis functionality and will post back if the sluggishness continues.
3. So far, the places I've hit the sluggishness would not make sense absent the data in our database. The scripts are also typically sensitive enough that I don't want to upload them to a forum. Please advise further.
4. 9.0.3.3299

Tianjiao_Li

Hi @DBAJohnny

Thanks for your reply.

We appreciate your concern of publishing your scripts here. We'll be in touch through email shortly.

Thanks.

DBAJohnny

Tianjiao_Li:

Since my last posting (Dec 22nd), I have been running with Code Analysis turned off. I have not noticed any sluggishness since then. I have been away from my desk for extended times in the interim, however, due to the holidays, so I'll post an update toward the end of the week.

Thanks again for your help,


Jonathan

torsten.strauss

Turning off Code Analysis will definitely solve the issue of cpu allocation here as well.
I turned off code analysis for a few weeks now and did not encounter any cpu problems anymore.
Maybe it is worthwhile considering to disable code analysis by default but check the code on demand using a shortcut or context menu instead.

Torsten

DBAJohnny

As requested offline by Tianjiao Li, I have installed the latest version of SQL Prompt and the minor release. I am currently running 9.0.5.3501.

I appreciate the help towards clean code that the Code Analysis feature gives, and would really like to work with it turned on, if the excessive CPU usage could be resolved. It's interesting that I could work a couple days with Code Analysis turned on before the excessive CPU issues started.

I'll turn on Code Analysis again and see if the new build makes any difference.

Tianjiao_Li

Hi @DBAJohnny

Thanks for your update and we've logged this issue as SP-6767 in our internal bug tracking system.

It would be very helpful if you can enable verbose logging, reproduce the sluggishness (with Code Analysis on apparently) and send in the log file.

Thanks!

DBAJohnny

Thanks. I've been operating with Code Analysis turned on now for about a week and haven't discerned any sluggishness. However, it's too early to tell as I've had to reboot every day or two in the interim due to other issuese

How do I enable verbose logging?

Tianjiao_Li

Hi @DBAJohnny

Thanks for the update.

Please go to SQL Prompt- Help -Enable verbose logging.

Thanks.

DBAJohnny

Cool. Got it.

mbray

FYI I had noticed the UI acting slowly, so I turned off code analysis, which definitely seemed to help. Then a few minutes later, I turned it on again, expecting it to slow down. At first, it locked up the UI for maybe a minute or so, but after that I didn't see any of the sluggishness. Maybe there are some data structures that are getting out of whack that get rebuilt when turning code analysis on.

DBAJohnny

Since upgrading SQL Prompt, I've been running most of a week with code analysis turned on. I also noticed the initial hit from turning Code Analysis back on.

However, my machine has been up with scripts open for most of a week and no noticeable sluggishness yet.

Tianjiao_Li

Hi @DBAJohnny, Thanks for the update and do let us know if issue reoccurs!

Tianjiao_Li

Hi @mbray , Thanks for posting and I'll update the bug report with your comments! Thanks!

Tianjiao_Li

Hi @DBAJohnny and @mbray ,

Thanks for your patience with this issue.

Our development team has done some work relating to this issue in the latest release.

Can you please upgrade or use this installer and let us know if it helps?

DBAJohnny

Thank you, I am now running 9.0.8.3873. I have verbose logging turned on. Should the problem reoccur, I'll contact you regarding uploading the log file.

MGH_Admin

Greetings,

My team and I upgraded to Prompt 9.1.2.4363 with SSMS 14.0.17224.0. We have been having the same issues described above. It gets to the point we are trying to add square brackets and the UI will freeze for 5-10 seconds. Have there been any efforts to resolve this issue or any other configuration settings we should consider?

Tianjiao_Li

Hi @MGH_Admin

Can you please try:

• Disable the built-in intellisense under Options->Text Editor->Transact-SQL->Intellisense (this has been known to clash with Prompt in the past and cause slowdowns due to excessive memory usage)
• Go to SQL Prompt>Options, click on Export to save your settings, and then try a "Restore all defaults" to see if that makes any difference? (You can then import the original settings back.)
  
• Disable the auto-refresh experimental feature (SQL Prompt ->Options->Labs-> Experimental features) (it might be kicking in at the wrong time and causing the slow down)

DBAJohnny

@MGH_Admin - You may find it worthwhile upgrading to SSMS to 2017.  I have had no further problems with sluggishness on it since my posting a while back.  I've appreciated the changes to the environment made since 2016 (my prior version).  All the best!

MGH_Admin

We will try your suggestions and get back to you. Thanks!

Lynchie435

Hi,

I seem to be experiencing similar issues to this with my SQL Prompt 9 on SSMS 17.5.

I have tried all of the above and still have issues.

The sluggish occurs when doing simple things as typing a 'REPLACE' it the Prompt tries to pickup what I am typing to provide suggestions.

I have turned code analysis off also and it still occurs, when I turn the suggestions off for SQL Prompt the issue goes away.

Not going to lie the speed at which I type queries it really slows you down when you type a word and the SQLP suggestions takes longer to 'check' each letter than the next 2 words I type.

For the time being I am going to have to turn SQLP, I only recently purchased the upgrade also, wish I hadn't of done so now.

Cheers

DBAJohnny

@Lynchie435 - see the postings from @Tianjiao_Li on 11th and 12 Jan, and 16th March.  If you can run with verbose logging and get Redgate the log file, it may help them get this one properly sorted out.  Wish I could help more!

DBAJohnny

@Tianjiao_Li - The issue is now reoccuring with me.  I've attached the query I was working with and the log file.

• The log file captioned itself "SQL Test," although I generated it from the help submenu under SQL Prompt.  
• It also seems that the verbose setting got turned off at some point.

I'm not on the latest version of SQL prompt, but will be installing that shortly.

RichardL

Hi @Lynchie435

Thanks for your post. 

Can you try the latest version to see if the problem still exists? It is available for direct download in my link below. 

ftp://support.red-gate.com/patches/SQLPrompt/28Mar2018/SQLPrompt_9.1.5.4619.exe

Do let us know how you get on with it and please send us a ticket if you are still experiencing the problem and are within a support and upgrades contract. 

Kind regards

Richard Lynch. 

subhash

Was on SSMS17.4 and SQL Prompt 9 and issue was getting to the point of not being able to work at all. Found a solution that seem to work for me.

What didn't work: 
- Tried suggestions above (disabling code analysis etc). Didn't help
- Reinstalled latest SSMS (17.8.1) and SQL Prompt (9.2.2). Still same "Not responding" on right-click 10-15sec delays each time.
- Uninstalled only SQL prompt. Worryingly SSMS still slow 

What worked:
Tried option from another redgate thread to disable all redgate addons 

After removing all the redgate extensions, SSMS snapped back to responsive and how it should be.
Reinstalled SQL Prompt. SSMS continues to work properly no delays (suggests/code analysis are enabled) 

So am assuming it must be one of the other addons (SQLSearch, SQLDocs ..) causing the delays. Haven't tried putting back to see which one did it.

eq2home

I have several areas in SSMS that are sluggish after installing SQL Prompt.  I have the SQL Prompt test change management system running and that may account for most of the problem.  I installed SSD drives and that improved performance by about 20%.  It isn't unacceptable but it is very noticeable.  Every time I open a new or existing SP, View, or enter a database I receive the following prompt.  It displays the same message twice.

DBAJohnny

Interesting.  Since I recently made several changes, I have been noticing the same behavior (SSMS v17.8.1):

• Installed SQL Prompt 9.4.6.7396
• Installed SQL Source Control 7.0.4.8725
• Switched SSMS to use the (officially unsupported) dark theme..

Per Tianjiao_Li's March 2018 post (above), I've now turned off SSMS' native intellisense.  I've also turned on verbose logging.  If I have to crash out SSMS again, I'll upload the log file..

eq2home

Per DBAJohnny's message, I have installed:  SQL Prompt 9.4.4.719 and SQL SC 7.0.2.8677