Azure SQL is extremly slow while operating on multiple rows

2
votes

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

2
sql-serverazureazure-sql-database
Please provide execution plan and statistics. Are you sure that you are not blocked by another query? – Lukasz Szozda
@lad2025 I'm pretty sure, nothing blocks me (nothing else is using this BD). How can I confirm it? I'll drop the execution plan and statistics in a couple minutes. – Andrzej Gis
Azure SQL is severely throttled. They trottle various resources and if you bump into any of them you get delayed. You'll have to pay for one of the more expensive editions. This is not a technical problem, it's a price/performance problem (maybe). – usr
in your local laptop,it uses your laptops ram,say for 4GB or 8 GB.But in Azure ,you get DTUS(blend of CPU,RAM,IO) depending on what you pay.Comparing a SQL server on premise with Azure is not valid.further it is like running a sql server with 1 GB ram on local machine and saying it is not performing well – TheGameiswar
This is not a programming question, and is off-topic for StackOverflow. Meanwhile, you ran your update on the lowest Standard tier available, and made a leap to the conclusion that 1) SQL Database has performance issues and 2) based on your comments, you feel you need to jump to a $3k/month plan. You simply need to benchmark on different tiers to see which one meets your needs, based on the types of queries and updates you'll run in production. – David Makogon

2 Answers

2
votes

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..

1
votes

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.