SQL Server – SSMS 2016 Context Menu Delay for Indexes

sql serversql-server-2016ssms

  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.

Best Answer

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.

Related Question