7
  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.

AngryHacker
  • 1,961
  • 5
  • 22
  • 33

4 Answers4

5

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.

Necro
  • 166
  • 1
  • 3
2

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.

user202230
  • 21
  • 2
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.

Ste Bov
  • 2,101
  • 1
  • 14
  • 19
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

Antoine Hernandez
  • 938
  • 6
  • 16