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?
Best Answer
This issue is finally fixed in the new SSMS 17.9.
You can get it here.