Restore Database - Select Backup Devices Slow to Load
I've been working with Matteo_Taveggia@MSFT on the SSMS download page comments on this issue:
Why does SSMS takes so long to bring open the 'Select backup devices' window after clicking the ellipses button when restoring a database? I've installed thousands of SQL Server installs and they all have done this for many years, as far back as I can remember with SQL Server 2005. I want to say it may have something to do with Windows Network Discovery which would be weird since we only care about local drives to the server.
I've used SSMS in many different combinations. Currently restoring a database from SSMS 17.3 running my machine where the SQL server 2016 is installed on a local server, and just noticed it. Sometimes maybe seconds 10-30sec, but yesterday was a good 1-2 minutes before the window popped up which triggered my comment. If you click while it is 'thinking' SSMS will say not responding until it comes back.
That is just one case. I handle the SQL Server installs for all of our clients running our software, so 99% of the time I'm dealing with on-prem local installs of SQL and SSMS on the same machine. Version of SSMS/SQL is irrelevant. Typically only working with 1-2 databases at a time, but in this case, I have a test server with 66 client databases loaded (20 of which are online at the moment), but this does not seem to have an impact.
I gif recorded my screen: https://imgur.com/gallery/UAMYF
This is all local to my machine with SSMS 17.3 with SQL 2017. Created a new database called "Test" left everything default, except I changed the Recovery Model to Simple. Then I started the recording where I'm going to restore it for the very first time.
I've captured a dump of the SSMS.exe process while it is "busy" and put it up on my dropbox https://www.dropbox.com/s/6lnve8879camrjw/Ssms.zip?dl=0
This has to be a super common delay with everyone, no? Here I am restoring a new database that I just created, on a brand new install of SQL Server out at a client that we didn't do the install (no commonality to previous environment), and I'm writing this comment now because I'm waiting for the Select Backup Devices box to pop up. It literally happens every time to every install of SQL that I've ever used. I've rarely seen it where it does not do this.
This is a long outstanding issue (I’m sure we have duplicate reports elsewhere).
We think we found the root cause and hopefully a fix will be made available in the next release of SSMS.