8

I host 2 databases on Azure SQL Server Db. Two very small 40Mb databases. Very limited access to them. If I look at my DTU, its like 0 peaking at 5%.

However when ever I open either of the databases in SQL management studio 2016. (v13.0.15600.2) It is very / extreamly slow.

Let me elaborate.

  • Connection To server is not to bad, (18 seconds)
  • Expanding databases ( 10 seconds)
  • Expanding Tables (60 tables) (15 seconds)

Now the worst part. If I edit a table in design view or Database Diagram.

If I click on the Name or Data Type on any column. Takes 30 - 60 seconds, I get not responding for half of that time. And it happens every time I left click or right on a row.

If I make changes to a table schema and have to save them. It can take very long as well. depending on the size of the table.

it is almost as if SMS does some sort of network request that just takes really long on the azure db.

Just some more info.

  • Azure Db is hosted in West Europe,
  • I am based in South Africa,
  • I have 20/20 Mbit/s fiber internet connection.
  • Ping to West Europe is 209ms on Azure Speed Test
  • If I work on locally hosted Standard SQL server instances my speed is 100%

I have just got to the point where I cannot even work on the databases anymore. It is simply unusable.

Zapnologica
  • 211
  • 4
  • 9

3 Answers3

1

I had the same problem and foud a work-around in this post: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8023437c-77e7-4bce-99ae-bfc717079749/slow-ssms-for-sql-azure?forum=ssdsgetstarted

Basically you have to close (not minimize) the property window. As long as the property window is open, SSMS constantly sends the same two sql commands to the DB.

Manu
  • 111
0

Ok, So this is the only solution I can come up with. I know its not a proper solution however it allows me to work.

Get a cheap VM, in the same region as your SQL Server, and then simply remote desktop into that server and use SQL Management Studio on the Virtual Machine.

Latency is still not optimal, however it is a whole lot better than using a local SMS instance.

Zapnologica
  • 211
  • 4
  • 9
0

I had the same issue when using any version of SSMS and connecting to an azure sql database.windows.net

Solution - Go to Options>> and select the Connection Properties tab...

Network protocol: change from default to TCP/IP

Network packet size: change from 4096 to 8192

Trust server certificate: checked

Tung Dang
  • 1