2

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

Andrzej Gis
  • 13,706
  • 14
  • 86
  • 130
  • Please provide execution plan and statistics. Are you sure that you are not blocked by another query? – Lukasz Szozda Apr 10 '16 at 11:47
  • @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 Apr 10 '16 at 11:52
  • 10 DTUs are very less.how are you comparing your local configuration with azure 10 DTUS..?That comparison is not valid,you have to increase your tier – TheGameiswar Apr 10 '16 at 12:13
  • 2
    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 Apr 10 '16 at 12:13
  • @TheGameiswar "What DTU really is" was also part of my question. Would I really have buy a plan for like $3k/month to have the performance of a SQL Server **Express** hosted on my laptop? – Andrzej Gis Apr 10 '16 at 12:22
  • 1
    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 Apr 10 '16 at 12:26
  • @TheGameiswar **Express** edition is limited to 1GB of ram. Which Azure plan can be comparable to what can be squeezed from SQL **Express** edition? – Andrzej Gis Apr 10 '16 at 12:29
  • With Sql Azure,you need to get more understanding on DTUS you get and limitations..This link has more info..see table for each service tier max limits.https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers/ – TheGameiswar Apr 10 '16 at 12:35
  • @lad2025 Please see the edit. I added statistics and execution plan. – Andrzej Gis Apr 10 '16 at 13:41
  • 1
    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 Apr 10 '16 at 14:52
  • I would have expected a where clause such as UPDATE Product set IsActive = 'true' where IsActive <> 'true' OR IsActive IS NULL . Another way to say my point is I doubit you would ever run a full table update like that in a normal production environment more than once a year. – Sql Surfer Apr 10 '16 at 15:07
  • See what is the query waiting on while running.you can use sys.dm_exec_query_stats dmv to see that.I guess it may be Page_io_latch since your huge table may not fit in memory.This doesn't mean your onprem server with 1 GB limitation fits into memory,it can also mean your onprem may not be executing queries so often as Azure server – TheGameiswar Apr 10 '16 at 19:22
  • Further try to discard result sets and set statistics io on on both queries and see what is the query execution time ,this gives you true estimation.further paste those in question – TheGameiswar Apr 10 '16 at 19:23
  • Possible duplicate of [Why is running a query on SQL Azure so much slower?](https://stackoverflow.com/questions/31086778/why-is-running-a-query-on-sql-azure-so-much-slower) – Michael Freidgeim Feb 17 '18 at 20:39

2 Answers2

2

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

Community
  • 1
  • 1
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
1

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.

Erkan Demirel
  • 4,302
  • 1
  • 25
  • 43
  • This is a great opinion. But it's not an answer. Just throwing out a random tier ( " *you should use at least S3* ") is not a valid answer at all. – David Makogon Apr 10 '16 at 14:51
  • I meant it you should use s3 and test it if it meets what you need you can make it lower or higher. But s0 it's not enough for product. Anyway I will change it to not redirect people to use s3. – Erkan Demirel Apr 10 '16 at 14:56