4
  1. Go to a random table in the Object Explorer
  2. Expand it down to indexes
  3. Right-click on an index - it takes around 10 seconds to come up

If I attempt to create a DROP & CREATE INDEX script off the context menu, it takes a similar amount of time.

I've looked at the Profiler trace, and it has about 4 pages of largely repeating queries. If I look at the Performance/Ethernet tab of the Task Manager, i see that it is bringing information at a rate of 800kb-1mb and sending at about 200kb or so.

Is this a bug? Feature? Am I missing some simple setting?

  • Version of SSMS: 13.0.15900.1.
  • Updated SSMS to 13.0.16000.28 with no effect.
  • Version of SQL Server: 2016 with cumulative update 2.
  • I had SQL Prompt 7 installed. Uninstalled it, but nothing changed.
  • SELECT COUNT(*) FROM sys.indexes; returns 1979.
  • SELECT COUNT(*) FROM sys.partitions; returns 1973.

I have the box all to myself, so there is no contention. Plus this box is the fastest, best spec'd box I've ever worked on. Everything is snappy on it except this specific operation.

5
  • 1
    How long does it take to run the queries that you found using profiler?
    – Joe Obbish
    Nov 1 '16 at 18:56
  • 1
    Did you find a solution to this? Context menus in all regards are slow for me. May 25 '17 at 18:47
  • @user1447679 Nope. Still broken with the latest ssms 2016 update. May 25 '17 at 22:26
  • Does it occur for you with SSMS 17.1? May 28 '17 at 4:22
  • @AntoineHernandez I don't have it installed. When v17 was released, the release notes marked it as fixed. The bug on MS Connect has since been reopened as some people are saying that it's not working still. May 29 '17 at 2:43
4

I fixed this by changing my authentication method from "Windows Authentication" to "SQL Server Authentication".

I have no idea why.

Before this change i had to wait up to 1 minute for context menu's to appear.

After change context menus are instant.

2
  • I know this is old, but it worked for me, although I have another server to which I log using windows authentication and don't have any freeze there
    – Rafalon
    Apr 20 at 6:45
  • It's 2021 and this just worked for me. I'd love an explanation of why this worked for me, though.
    – Michael W.
    Jul 26 at 22:52
1

I notice this every now and again after I've had SSMS (2012) open for over a week or more and I've been running things like activity monitor / replication monitor / AO Dashboards

Check opening another instance of SSMS to see if it takes the same length of time, if it is significantly shorter then have both closed (I'm not talking instant, just 2-3 second instead of 9-10)

Then save out and scripts you need and close all copies of SSMS running on that machine and open them, should be back to a normal time.

This has worked for me a few times in the past.

1
  • Its nothing like that. I don't keep SSMS open for that long. Oct 31 '16 at 22:05
1

There is a lot of info out there about this behavior going back years. Here is a thread: SQL Server Management Studio slow opening new windows

Common thread there seems to be SSMS is trying to reach a location in the internet. Another thing there states to change the user feedback Opt In setting.

Maybe one of these options will speed it up

1

In my experience this is because SSMS runs (or checks the authorization to run) certain system stored procedures every time it opens the context menu. I realized this when I went to view a table definition on a server where I didn't have permissions to alter tables, etc. The server response to right-clicking for the context menu was to spit back a long list of system stored procs I didn't have access to - before the context menu even appeared. Once I dismissed the "access denied" dialog, the context menu appeared. This happened every single time I invoked the context menu.

Even if you do have all the needed permissions, SSMS still seems to do this check. If your target sever has a slow connection it will take much longer. It's almost like it reauthenticates for every item on the menu, though this is just a guess. It seems crazy that it doesn't just cache the first response rather than do it every. single. time.

Very poor design for a very frequently used part of the UI.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.