Azure SQL Database update performance

Question

We're migrating some databases from an Azure VM running SQL Server to Azure SQL. The current VM is a Standard DS12 v2 with two 1TB SSDs attached.

We are using an elastic pool at the P1 performance level. We're early days in this, so nothing else is really running in the pool.

At any rate, we are doing an ETL process that involves a handful of ~20M row tables. We bulk load these tables and then update some attributes to help with the rest of the process.

For example, I am currently running the following update:

UPDATE A
SET A.CompanyId = B.Id
FROM etl.TRANSACTIONS AS A
LEFT OUTER JOIN dbo.Company AS B
ON A.CO_ID = B.ERPCode

TRANSACTIONS is ~ 20M rows; Company is fewer than 50.

I'm already 30 minutes into running this update which is far beyond what will be acceptable. The usage meter on the Pool is hovering around 40%. For reference, our Azure VM runs this in about 2 minutes.

I load this table via the bulk copy and this update is already beyond what it took to load the entire table.

Any suggestions on speeding up this (and other) updates?

Answer 1

We are using an elastic pool at the P1 performance level.

Not sure ,how this translates your VM performance levels and what criteria you are using to compare both

I would recommend below steps ,since there is no execution plan provided ..

1.Check if there is any wait type ,while the update is running

select 
session_id,
start_time,
command,
db_name(ec.database_id) as dbname,
blocking_session_id,
wait_type,
last_wait_type,
wait_time,
cpu_time,
logical_reads,
reads,
writes,
((database_transaction_log_bytes_used +database_transaction_log_bytes_reserved)/1024)/1024 as logusageMB,
txt.text,
pln.query_plan
 from sys.dm_exec_requests ec
 cross apply
 sys.dm_exec_sql_text(ec.sql_handle) txt
 outer apply
 sys.dm_exec_query_plan(ec.plan_handle) pln
 left join
 sys.dm_tran_database_transactions trn
 on trn.transaction_id=ec.transaction_id

the wait type,provides you lot of info,which can be used to troubleshoot..

2.You can also use below query to see in parallel ,what is happening with the query

set statistics profile on
your update query

then run below query in a seperate window

select 
session_id,physical_operator_name,
row_count,actual_read_row_count,estimate_row_count,estimated_read_row_count,
rebind_count,
rewind_count,
scan_count,
logical_read_count,
physical_read_count,
logical_read_count
 from
sys.dm_exec_query_profiles
where session_id=your sessionid;

as per your question,there don't seems to be an issue with DTU.So i dont see much issue on that front..

Answer 2

Slow performance solved in one case:

I have recently had severe problems with slow Azure updates that made it nearly unusable. It was updating only 1000 rows in 1 second. So 1M rows was 1000 seconds. I believe this is due to logging in Azure, but I haven't done enough research to be certain. Opening a MS support incident went nowhere. I finally solved the issue using two techniques:

1. Copy the data to a temporary table and make updates in the temp table. So in the above case, try copying the 50 rows to a temp table & then back again after updates. No/Minimal logging in this case.

2. My copying back was still slow (I had a few 100K rows), and I create a clustered index on that table. Update duration dropped by a factor of 4-5.

I am using a S1-20DTU database. It is still about 5 times slower than a dedicated instance, but that is fantastic performance for the price.

Answer 3

The real answer to this issue is that SQL Azure will spill to the tempdb much faster than you would expect if you are used to using a well provisioned VM or physical machine.

You can tell that this is happening by recording the actual execution query plan. Look for the warning icon:

The popup will complain about the spill:

At any rate, if you see this, it is likely that you're trying to do too much in the statement.

The Microsoft support person suggested updating the statistics, but this did not change the situation for us.

What seems to be working is the traditional advice to break the inserts up into smaller batches.