SSMS 2012 / 2014 / 2016 Object Explorer hangs when Expanding Databases list on a server with many DBs

Question

When accessing SQL Server with SSMS versions (2012/2014 and 2016), opening the databases list from the object explorer is really slow. If I try it using SSMS 2008 R2 on the same instance the result is => instant display.

Note : The account is sysadmin, all databases have autoclose disabled, and I am not connecting to a remote server. It's on my local machine!

I noticed that there is a small difference comparing the queries generated from SSMS 2008 and 2012/2014/2016 when opening the databases list.

All of them are using has_dbaccess function, except SSMS 2008.

For instance:

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

There is a huge performance difference if I remove the function. I guess they should add a condition in the begging of the query where in case it's a sysadmin account, just don't check permissions in every single database. Does it make any sense?

Answer 1

This issue is finally fixed in the new SSMS 17.9.

You can get it here.

Answer 2

I solved this by changing from Windows Authentication to SQL Server authentication, as proposed here. (I realize not everyone can do that.)

The problem is still there in SSMS 18.9 and SQL Server 2019.

The has_dbaccess function definitely seems to be the problem.