SSMS Slow/Unresponsive

James Hannon 31

SSMS consistently is slow and unresponsive for me. It started with SSMS 2016 and has carried with the same behavior the whole way, no matter what I've tried. Today I tried to completely wipe out my settings in the following locations:

%appdata%\Microsoft\SQL Server Management Studio
%userprofile%\AppData\Local\Microsoft\SQL Server Management Studio

The version I'm currently using is 18.3.1.

The specific 'slow' behavior is seemingly random inside the application where the windows goes grey with an 'Not responding' which also shows in Task Manager. This happens on establishing a new connection in Object explorer, right clicking an object in Object explorer, Disconnecting, opening a new query, etc. We've even tried tracing the Destination SQL Servers are end up not seeing my logout on disconnection for 10+ minutes.

Does anyone have any insight on what I might be able to try next to have a better experience?

Cedric 0

I've identified this query (using profiler) as really slow for "Edit top 200 rows":

exec sp_executesql N'SELECTdb_name() AS [Database_Name],SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],tbl.name AS [Table_Name],i.name AS [Name],i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],i.is_unique AS [IsUnique],i.fill_factor AS [FillFactor],CAST(ISNULL(INDEXPROPERTY(i.object_id, i.name, N''IsPadIndex''), 0) AS bit) AS [PadIndex],CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],CAST(INDEXPROPERTY(i.object_id,i.name,N''IsFulltextKey'') AS bit) AS [IsFullTextKey],

    CASE WHEN ((SELECT tbli.is_memory_optimized FROM sys.tables tbli WHERE tbli.object_id = i.object_id)=1 or

    (SELECT tti.is_memory_optimized FROM sys.table_types tti WHERE tti.type_table_object_id = i.object_id)=1)

    THEN ISNULL((SELECT ds.name FROM sys.data_spaces AS ds WHERE ds.type=''FX''), N'''')

    ELSE CASE WHEN ''FG''=dsi.type THEN dsi.name ELSE N'''' END

    END

   AS [FileGroup],ISNULL(s.no_recompute,0) AS [NoAutomaticRecomputation],CAST(case when i.type=3 then 1 else 0 end AS bit) AS [IsXmlIndex],i.is_disabled AS [IsDisabled],CAST(CASE WHEN ''PS''=dsi.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],CASE WHEN ''PS''=dsi.type THEN dsi.name ELSE N'''' END AS [PartitionScheme]FROMsys.tables AS tblINNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_idLEFT OUTER JOIN sys.stats AS s ON s.stats_id = i.index_id AND s.object_id = i.object_idWHERE(SCHEMA_NAME(tbl.schema_id)=@_msparam_2 and tbl.name=@_msparam_3)ORDER BY[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'dbo',@_msparam_3=N'FileDelimiter'

Accepted answer

Erland Sommarskog 110.2K Reputation points

2020-11-06T22:37:55.84+00:00

I have noticed similar behaviour, but thankfully not that much that it has become a burden. One problem can be if you have lost a connection to an instance, for instance due to a network corruption. Or when I have made the mistake to shutdown a VM, when I have an open query window to an instance on that VM.

When I think of it, I had some problems in Object Explorer at my client, which I have not seen for a while. It could be that they have fixed something in more recent versions of SSMS - the current version is 18.7.1. It could also be that there used to be network issues at my client which they have fixed.
Please sign in to rate this answer.
James Hannon 31 Reputation points

2020-12-17T19:06:51.407+00:00

This ended up getting narrowed down to network restrictions with port ranges. We narrowed this down by testing with a locally installed Instance where we did not have any of the issues. The next steps were to take network traces to isolate what port ranges are being blocked that are being called to resolve the issue in general.

Thanks for your help!

Erland Sommarskog 110.2K Reputation points

2020-12-17T22:01:08.523+00:00

Great to hear that you were able to nail it down!

Interesting enough, I said above that I had not seen those freezes for a while, but it happened to me one occasion today. But that was the first time for a little while.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Cris Zhan-MSFT 6,626 Reputation points

2020-11-09T06:57:32.85+00:00

Hi @James Hannon ,

Is there any update on this case?
Please make sure you're on the latest version of SSMS.

If you are using SSMS to connect to a remote SQL Server instance. As Erland said, network problems may be a cause.And In a high CPU usage on the server may also cause SSMS to become unresponsive.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SSMS Slow/Unresponsive

1 additional answer

Your answer