2

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?

Improve this question
  • Well, it is ambiguous, "all databases are not configured with autoclose" means to me that they aren't all set to auto-close, but some might be, so I can understand why RLF suggested it... – Aaron Bertrand Feb 9 '17 at 0:15
  • Have you tried this connect.microsoft.com/SQLServer/feedback/details/768124/… – jesijesi Feb 9 '17 at 8:41
  • Hi @jesijesi. Yes, I've tried this one as well, even saying the problem is related when connecting to remove servers. Thanks! I really believe the problem is related to that function has_dbaccess. Because when I remove it (it doesn't exist on SSMS 2008) it runs instantaneously. – Danilo Braga Feb 9 '17 at 20:18
1

This issue is finally fixed in the new SSMS 17.9.

You can get it here.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.