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.

Improve this question
3
  • 1
    Same problem. Also based in South Africa and hosting in North EU.
    – Dave New
    Oct 20, 2016 at 10:23
  • I have had people suggest that I need to Up the DTU of the database. But I am running on S0 and if you watch the monitor it doesn't ever go past 50% DTU. So I doubt its that.
    – Zapnologica
    Oct 26, 2016 at 18:54
  • I can confidently say that it is NOT DTU. I have even upped my server to 50 DTU and it is as slow.
    – Zapnologica
    Nov 18, 2016 at 6:56

3 Answers 3

Reset to default
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.

Improve this answer
1
  • 1
    Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.
    – Kalle Richter
    May 22, 2017 at 12:54
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.

Improve this answer
1
  • 1
    I have the same problem. I live in the selected region (W-Europe) and never had a property window open. It can take up to 30 seconds to simply display the context menu (right click menu) of a table or other database objects.
    – Wout
    Feb 19, 2018 at 7:32
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

Improve this answer
1
  • Welcome to SuperUser! Please add sources to your answers so that other community members can verify your answer. This way we improve the knowledgebase for everyone.
    – DarkDiamond
    Jan 19, 2022 at 23:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .