Why is SQL Server Object Explorer in Visual Studio

Question

I just created a new SQL Server Database in Azure and then opened it in Visual Studio 2015 using the link in the Azure Portal. I had to add my IP to the firewall but otherwise the process went smoothly.

However, when I am trying to interact with the database server via SQL Server Object Explorer it is painfully slow. Expanding any of the folders in my Database (e.g., Tables folder) takes 10 to 30 seconds. The database is brand new, so the only things it has are whatever Azure creates when it instantiates a new DB.

This is the second Azure DB I have created and tried to view in Visual Studio and both have the same problem. With the first one I thought maybe I did something wrong during setup but this time I made sure to do everything by the book.

Running actual queries against the DB from within Visual Studio (right click the DB, New Query ..., select * from INFORMATION_SCHEMA.TABLES;) is very fast, so it doesn't appear to be a problem with my connection to Azure.

Why is it so painfully slow? What can I do to make it faster?

I am using Visual Studio 2015 Update 1 (14.0.24720.00) on Windows 10 (fully patched) and during database creation I checked the box to use the latest version.

Answer 1

SQL Server Object Explorer (SSDT) in Visual Studio builds a schema model of entire database when you connect a database first time. This is a concept of model based database development which provides benefits such as validating scripts against database without needing to modify the live database etc. When it comes to Azure SQL DB where the resource is limited with your database's pricing tier, the performance of executing queries to fetch database model can vary. Based on our performance tests, pricing tiers above S1/S2 provides a similar interactivity as local or on-prem SQL instance.

More in details, current SSDT's mechanism is to composite minimum number of queries to minimize the number of round-trip operations with SQL server/database hence each query execution time is longer. It affects the interactivity of SQL Server Object Explorer while the query is being executed.

To enhance, SSDT team is revising the design for Azure SQLDB to provide a faster interaction experience while keeping the benefit of model based database development.

Disclosure: I work on the SQL Data Tools team as a PM

Answer 2

Good tip from @eric-kang in his comment to use 'Server Explorer' instead of 'SQL Server Object Explorer' - this is much better for basic data queries as its actually usable in real time