CODEWITHSUNDEEP
sql-serverazureazure-sql-database
Andrzej Gis
Andrzej Gis

Reputation: 14316

Azure SQL is extremly slow while operating on multiple rows

In my Azure SQL database (S0 10 DTUs) I have Products table with 80k rows.

From Management Studio I'm running the following query:

UPDATE Product 
set IsActive = 'true'

It runs for almost 3 minutes utilizing nearly 100% of the DB resources.

The same query executes in less than 10 seconds on my local machine (SQL Server Express) on the table which is 100 times larger.

What's wrong with Azure SQL? Is it not suitable for operations on multiple rows? My local configuration is very poor but yet it outperforms Azure SQL by far.

What really is a DTU? From this link I can't really tell what they mean by transaction. Is it a single row read/write acccess?

Should I move to a VM with SQL Sever Express if I need to perform multi-rows operations on my database?

EDIT

Statistics

SQL Server Express

Execution time: 26 seconds

enter image description here enter image description here

Azure SQL

Execution time: 1m 23s

enter image description here enter image description here

Upvotes: 2

Views: 2092

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28940

Few things here..

1.First your comparison with AZure DB with local DB is not valid

2.Secondly your execution plan may change depending on various factors and the client statistics wont help..

For a true comparsion ,do below..

a.)Set SSMS option to discard result sets on both instances

b.)set statistics IO on

Now with discarding result sets,network latency is eliminated and with statistics io on ,we can see whether Azure DB is reading from Disk due to memory pressure

Once you have the true comparison ,you can check few more things to see what are the wait types when the query is running..

select * from sys.dm_exec_query_stats st
cross apply
sys.dm_exec_sql_text(st.sql_handle) txt

Based on wait type,you can troubleshoot further,say for example ,if you are seeing high page Io Latch wait types ,then that means Pages are not in memory for that table..

Upvotes: 2

Erkan Demirel
Erkan Demirel

Reputation: 4382

What really is a DTU?

This is good question. They have explained here. It's still not clear. But they have dtu calculator, with this calculator you measure your current sql server 1 hour and upload result. After that, they suggest you which tier you should use for same performance.

We use Azure Sql more than 6 months. In my opinion, DTU is marketting term more than technical term.

You should use DTU Calculator and/or increase the tier. Then, test it again to find best price/performance for your needs. In my experience S3 it's good tier to start test which tier you need for product dbs.

And don't compare Saas with your local computer. You can compare hiring a virtual machine cost + manage sql and virtual machine cost + backups, geo locations + etc.. with Azure Sql. Then it will be more fair.

Upvotes: 1

Related Questions