SSMS Object Explorer Right Click Menu Very Slow

KathyG

I'm having problems with the right click menu in SSMS Object Explorer being very slow.  It's fine at hierarchies that are outside of a particular database.  But when I right click on a database or any of the objects within a database (whether I'm linked to the database or not), the menu takes a long time to come up.  First it comes up as an outline, then a black box and finally it fills in.

I have SQL Search, SQL Schema Compare, SQL Data Compare, SQL Prompt and SQL Source Control installed.  I've narrowed it down to SQL Source Control as the problem (uninstalled and performance back to normal the reinstalled).

I've just received a new laptop with factory resolution at 3840 x 2160.  Could this have anything to do with it and if so what resolution would be acceptable.

My SQL Source Control Options are all default except for check for changes to static data which I have unchecked.

thx

Kathy

squigley

We're working on this via the ticket 144335.

DanSac

I'm currently experiencing this exact issue with source control v7.1.12.10363 - has this been addressed yet?

dmo

Same here - right click context menu unusable, will have to uninstall source control - which we hoped to use. Killing productivity.

KathyG

All ... I apologize for not responding to this sooner, but I have good news.  In addition to posting this on the forum, I also opened a support ticket and the support team found a fix for me.

Here's the relevant snippet from the email thread and I hope this helps everyone.

Hello Kathy,
Just wanted to follow you up with a suggested troubleshooting step to see if that helps.
Can you try disabling automatic refreshing on your end to see if that improves performance?
https://documentation.red-gate.com/soc/configuring/disable-automatic-refreshing

Thanks!

Sincerely,
Sean Quigley

TAD458

i have this same issue, i have tried disabling the auto refresh as suggested and it makes no difference (i even tried adding this option to all the GUI options XML files related to Redgate).  Sometimes SSMS even completely crashes but most of the time its painfully slow every time i right-click inside the Object Explorer.  I am running SSMS 17.9.1 and the entire Redgate SQL Developer Bundle (Source Control 7, Prompt 10, ...)

Dtrainer_1969

I am in the same boat as TAD458. I tried the addition to the config file and it did not help. I have the entire SqlToolbelt - Essentials package loaded. Latest versions available. Has anyone found a fix?

Eagle6880

It's been over a year now, is Red Gate going to do anything about this? I would uninstall it, but I like the colored tabs. Will be looking for an alternative to colored tabs and uninstalling SQL Prompt if this bug isn't resolved soon. I'm disappointed, Red Gate used to be reliable and robust :-(

dhardy

Another vote to get this fixed. I did the config change with no improvement. How many users have to report this before RG does something about it? First post was a year and a half ago.

nickfotopoulos

SQL Source Control makes SSMS almost completely unusable. I can work all day using RedGate tool without a single hiccup but after I run SQL Source Control the first time SSMS performance (Not just right click menus, but everything including typing) begins to degrade slowly over the course of an hour of two until I can barely type 1 character every second or two and eventually it SSMS will stop responding.

I've got in the habit of only checking in code once or twice a day and restarting SSMS anytime I use SQL Source Control to avoid the frustration. This obviously comes with it's own set of problems. Sometimes things don't get checked in like they should, or something is modified by two people over that period of time and the history of changes by one of them is lost.

SQL Source Control was and still is the primary reason we bought the Toolbelt Essentials. We occasionally use other stuff, but certainly not enough to justify the cost. It's been like this since we started using it and disabling the options to auto refresh. I've even gone as far as uninstalling all of the tools, SSMS, and Visual Studio and reinstalling everything fresh and using them without any additional extensions or tools and I still experience the problem.

At first I thought it might be related to the fact that i'm regularly connected to 4-5 instances with a couple dozen databases across them, but i've experienced this issue even on days where I spend most of my time in one or two of them.

I really like the tools, but this issue is slowly pushing me closer and closer to evaluating other products and switching.

Kendra_Little

Hi all,

Apologies for the lack of resolution and /or recent updates on this issue.

I'd like to do a quick recap of current existing options for lightning the impact of SQL Source Control on SSMS, then share some additional thoughts.

First up, performance tweaks that may help readers 

1. As mentioned above, one option is to disable automatic refreshing in SQL Source Control. 
2. We also have two additional options which can be toggled that specifically may help speed up  Object Explorer with some environments. You can either lessen the frequency or disable the rate at which changed objects are updated in Object Explorer. You can also test if the object locking feature may be what is causing the latency by disabling that feature.
3. Finally, we have some tips for diagnosing/avoiding general latency here . 

Gathering more info
I am curious to know if your use case specifically includes any of these, and if it might be related to the poor experience:

• Connecting to databases over a slow connection (whether cloud or not)
• Large static data tables
• Many encrypted objects
• Schemas with a large number of objects (this wouldn't just show in object explorer but would also cause a slow refresh time)

We are looking to improve the experience with all of these, but it always helps to understand specific use cases. 

Plans and some background
SQL Source Control is currently implemented as an x86 plugin in SQL Server Management Studio. This means that performance and functionality is always a balance -- and also that we don't have a simple way to resolve performance issues for every use case.

But while this isn't a quick fix for us, this is definitely an area where we would like to improve. We are working on some new functionality which we plan to have available soon for a private preview for customer feedback. 

If you think you might be a good fit for the private preview, I'd love for you to get in touch with our team at databasedevops@red-gate.com. It would help if you could share a bit about your use case / any info related to the "gathering more info" section that you think might be suitable. 

Thanks very much for your feedback in this area.

Cheers,
Kendra
------
Kendra Little
Redgate

KathyG

Hello everyone, as the originator of this post, I thought I'd circle back to let you know what my current experience is. 

I'm running 4 instances of SSMS (v17.9.1) right now on my Windows10 Horizon VM, connecting to mainly 2016 SQL server VMs.  I have a lot of RedGate tools plugged into those instances: Search, Prompt, SourceControl.  

The SSMS add-ins are running just fine (although sometimes SSMS with SourceControl can get flakey and you need to kill/restart it).  Connecting to databases with 1000+ objects can take a little time, as does disconnecting.  However, Object Explorer is running at about 90% of original speed.  

As I remember, I posted this when we got brand new Dell XPS laptops that had 3840x2160 default resolution.  One of the issues with SSMS had to do with screen rendering for a 32bit app.  So I changed my laptop resolution to 1920x1080 and coupled that with the fix provided by SeanQ.

I've never experienced the problem on my VM which autoscales the screen resolution to whatever external monitor I put it on (currently 1920x1017).

Hope this helps!
Kathy

nickfotopoulos

Blinded by my frustration I blamed Red Gate software for a SSMS problem. My apologies! 

After posting this and beginning to look for solutions again, I stumbled upon...

SQL Server Management Studio (SSMS) 2012: Unusually high memory usage (leaking?)

...and remembers that I had actually solved this problem before. The issue basically involved an intellisense memory leak that is triggered when you have a complex query and you modify it in such a way as to break the syntax. Sometimes this triggers a downward spiral in SSMS where it continues to eat memory and degrade in performance until you close it or it crashes.

A few weeks ago, having forgotten about the fix, I noticed that Outlining wasn't working and turned Intellisense back on so I could collapse code sections. This brought the problem back. I think the reason I associated it with SQL Source Control is because i'm far more like to be using it when i'm writing the kind of queries that trigger this issue.

I honestly don't even notice Intellisense being turned off because SQL Prompt does all of that better. The only thing I truly miss with it turned off is code outlining.

0xFFFF_FFFF

Kendra_Little said:

Hi all,

Apologies for the lack of resolution and /or recent updates on this issue.

I'd like to do a quick recap of current existing options for lightning the impact of SQL Source Control on SSMS, then share some additional thoughts.

First up, performance tweaks that may help readers 

1. As mentioned above, one option is to disable automatic refreshing in SQL Source Control. 
2. We also have two additional options which can be toggled that specifically may help speed up  Object Explorer with some environments. You can either lessen the frequency or disable the rate at which changed objects are updated in Object Explorer. You can also test if the object locking feature may be what is causing the latency by disabling that feature.
3. Finally, we have some tips for diagnosing/avoiding general latency here . 
   

Gathering more info
I am curious to know if your use case specifically includes any of these, and if it might be related to the poor experience:

• Connecting to databases over a slow connection (whether cloud or not)
• Large static data tables
• Many encrypted objects
• Schemas with a large number of objects (this wouldn't just show in object explorer but would also cause a slow refresh time)

We are looking to improve the experience with all of these, but it always helps to understand specific use cases. 

Plans and some background
SQL Source Control is currently implemented as an x86 plugin in SQL Server Management Studio. This means that performance and functionality is always a balance -- and also that we don't have a simple way to resolve performance issues for every use case.

But while this isn't a quick fix for us, this is definitely an area where we would like to improve. We are working on some new functionality which we plan to have available soon for a private preview for customer feedback. 

If you think you might be a good fit for the private preview, I'd love for you to get in touch with me at Kendra.Little@red-gate.com. It would help if you could share a bit about your use case / any info related to the "gathering more info" section that you think might be suitable. 

Thanks very much for your feedback in this area.

Cheers,
Kendra
------
Kendra Little
Redgate

Hi Kendra, I appreciate your reply here, but this problem still exists here for me, and I've tried every single one of the workarounds mentioned here and throughout RedGate's website, i.e.:

• Disable automatic refreshing
• Set the snippets folder to a local path (it already was)
  
• Disable Code Analysis
  
• Disable the built-in intellisense
  
• Disable the auto-refresh (the GUI way)
  
• Upgrading to the latest version of SQL Source Control, and
• Disabling object locking

...each one followed by a restart of SQL Server for the change to take effect.

Ultimately, the only troubleshooting step that finally brought responsiveness back to my SQL Server was to uninstall RedGate tools altogether. (And I trust I don't need to explain to anyone here why that's not a real solution!)

Given how many people in this thread are experiencing the issue (and add to that number 3 more at my company, including myself), then I think it's pretty clear that there is some bug in RedGate's software.

It should be fairly straight-forward for someone at RedGate to reproduce this issue on their end.

Regarding your specific questions about how many static tables or encrypted objects I have, I'm afraid you'll just have to tell your engineers to do their own research and get the answers to those questions themselves. I work at a medium-to-large-sized company. Our database is a medium-to-large-sized database. I don't know what else to tell you. It's a real world workplace, with real-world databases. And RedGate's software should work in the real world.

What I do know for sure though, is that when I install a piece of third party software it shouldn't make my mouse clicks freeze for 3–5 seconds.

MarkNZ

This has been an issue for me for quite a while - right-click an object and wait for the menus to build.
Today I installed the new update for SQL Source Control and BOOM! - problem solved!

7.2.32.12853 - June 2nd, 2021

• VT-20 - Improve Object Explorer right click performance.