Slowness in Object Explorer's Context Menu

MrBinqui

Hello,

I have SSMS 2014 and downloaded Red Gate tools, I decided to give a try to the Source Control.

However, I noticed that since I installed the Reg Gate tools my context menu on the Object Explorer takes 2 to 5 seconds to open. Meaning, I pick a table, I right click on it to get the CREATE TO submenu, and I have to wait between 2 to 5 seconds for it to pop up and it was not doing that before. It is not consistent Sometimes is 2 others 5 and I cannot spot a pattern.

I decided to run a profiler on my laptop and see if I could find something that could lead me to what is going on. So far I have not seen anything weird, the longest query was like 137 milliseconds.

The only problem I saw was that the code below was being executed every time I was interacting with the Object Explorer, even though I do not have the Object Locking working, and it was giving an error because RedGate DB does not exists in my server.

USE [RedGate]

IF OBJECT_ID('[SQLSourceControl].[LockedObjects]') IS NOT NULL
SELECT CAST(1 AS BIT)
ELSE
SELECT CAST(0 AS BIT)

I end up running the setup files for the Object Locking and then running the scripts here https://documentation.red-gate.com/disp ... ct+locking. I tried that and see if it would do any good and no, nothing changed.

Any ideas?

~AlexV.

Gareth_B

Hi Alex,

If you're not using the Object Locking feature, it may be worth turning it off completely. We recently had another customer with similar performance issues to yours, and this helped them.

To turn the feature off:

1. Close Management Studio.
2. Go to the SQL Source Control config files folder. By default, this is located at %localappdata%Red GateSQL Source Control 4
3. Open RedGate_SQLSourceControl_Engine_EngineOptions.xml in a text editor.
4. Below the EngineOptions version line, add:

<EnableObjectLocking>False</EnableObjectLocking>

The file is case sensitive. Don't change the capitalization of the text.
Ignoring any comments (indicated with <!->), the final file should look like this:

<EngineOptions version="3" type="EngineOptions">
    <EnableObjectLocking>False</EnableObjectLocking>
</EngineOptions>

The example above doesn't include any extra lines you may have included. For example, you may have included additional lines to set up change logging.
5. Save and close the file, and reopen Management Studio.

I hope that helps!

MrBinqui

Hello,

I followed the instructions. Tried the Object Explorer, slowness still present.

I uninstalled Source Control 4, tested and now the slowness is gone.

Any other suggestion?

Regards,
AlexV.

Gareth_B

Hi Alex,

I noticed a copy/paste error in the steps I put before, so they may have been misleading. Can I just confirm that you put the "EnableObjectLocking" setting to "False"?

If so, I'll see what other options we may have for you.

MrBinqui

Hello,

I reinstalled SC4 and modified the XML as instructed above, at the beginning on the first click over an object it would act very very slow, and a couple of times the SSMS bar said (Not Responding).

However, if I keep bugging the object, now it is getting faster even when I right click on it to get the context menu. Which is good, since now is starting to act like before I installed the SC4 :-)

Something interesting I did notice, is that even though we turned off the Object Locking from the XML I still see these:

USE [RedGate]

IF OBJECT_ID('[SQLSourceControl].[LockedObjects]') IS NOT NULL
SELECT CAST(1 AS BIT)
ELSE
SELECT CAST(0 AS BIT)

OR

SET LOCK_TIMEOUT 12500

Are they supposed to still show up in the profiler?

Thanks!