Azure SQL Database - Slow

8
votes

I'm currently working with SQL Azure and I realize that it is very slow when I used it with Sql Server Management Studio or Visual Studio 2013 Data Tools for design the database. Is there anyone having the same issue? I tried scaling the database to this configuration but it still working slow :

Server Tier : Standard. Performance level : S2.

Regards,

Rodrigo

5
azure-sql-database
Is your database on a V12 server? – elfisher
No, it's on V11. Do you think that's the problem? Thanks – Rodrigo Longo
V12 should help improve your experience in general as it supports more capabilities than pre-V12. There are some performance tests of Pre-V12 vs V12 you can find here. The main reason I asked is that with V12 servers, there are updates for Microsoft SQL Server Data Tools. You can find them here. – elfisher
I'm going to try that. Thanks. – Rodrigo Longo
Im using v12, and it is awfully slow. Expanding tables takes like 5 minutes. It is unusable in SSMS. I have a tiny 4meg database. – Zapnologica

5 Answers

2
votes

I just solved this and wanted to see if other's have a better solution.

You don't get this 'slow' behaviour in SQL Management Studio if you use the 'servers' SA account.

Other accounts were getting it.

Solution

USE master
GO
CREATE USER usernameYouLogInWith
    FOR LOGIN usernameYouLogInWith
    WITH DEFAULT_SCHEMA = [dbo]

Solved.

Immediately right clicking in Management Studio as that user account is now 'instant'.

PS, I know you can't use 'use' in SQL Azure, but at least you know to run that on the master database. Using V12 DB

1
votes

SQL Azure also supports S3 performance tier which has same DTU as P1 and half the cost of P1. If you experience the performance issues because of load, you can give a shot and of course you can scale down if needed

1
votes

Once I updated to the latest SSMS and client tools all was well again.

1
votes

MY understanding is that Microsoft has not yet optimized SSMS to work properly with Azure. That means the application makes excessive number of queries at all times and that makes the UI super slow.

Until Microsoft starts resolving this (using 2016 here and yet very slow), I found a free tool that works at a decent speed, and it has a free version that works for my needs.

DBForge Studio Express http://www.devart.com/dbforge/sql/studio/download.html

Good luck

0
votes

Here is the Microsoft explanation of this issue:

https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-132-delays-connecting-to-azure-sql-database-from/ba-p/1502030

They describe two types of authenticated users. Ones with instance LOGIN's and ones that have only access to a single database ("contained" database users).

The problem with the slow SSMS connectivity only affects the first type of authentication (instance LOGIN's when they have access to a database but have no rights to the "master" database). If you are able to switch to "contained" database users then you may avoid the problem altogether.