5

I have two tables that temporarily hold uploaded data from a web application before it gets processed. This is running on an Azure SQL Database.

UploadFile

Id uniqueidentifier
CustomerId uniqueidentifier
FileName nvarchar(MAX) NULL
UploadDate datetime
UploadedBy nvarchar(MAX) NULL

UploadFileChunk

Id uniqueidentifier
Data varbinary(MAX) NULL
UploadFileId uniqueidentifier
[Index] int

UploadFile has a foreign key with cascade delete to UploadFileChunk. UploadFileChunk has a non-clustered index on UploadFileId. I have been uploading chunks of 1MB each.

Inserting and reading this data is working really well, but deleting a record from UploadFile after its data has been processed is really slow. On an S0 10 DTU testing Azure environment, it took 38 minutes to delete 12 records with ~500 child chunks collectively (just an example - it also performs poorly on our higher powered production environment and a fast local machine.)

How do I make it fast?

There is an index on UploadFileChunk.FileUploadId. Here's the execution plan.

I ran the following query (from Finding Blocking Queries in SQL Azure) several times while the slow delete was mid-execution and it didn't return any results:

SELECT TOP 10
    r.session_id,
    r.plan_handle,
    r.sql_handle,
    r.request_id,
    r.start_time,
    r.status,
    r.command,
    r.database_id,
    r.user_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    r.total_elapsed_time,
    r.cpu_time,
    r.transaction_isolation_level,
    r.row_count,
    st.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE
    r.blocking_session_id = 0
    AND r.session_id IN 
    (
        SELECT DISTINCT (blocking_session_id) FROM sys.dm_exec_requests)
GROUP BY
    r.session_id,
    r.plan_handle,
    r.sql_handle,
    r.request_id,
    r.start_time,
    r.status,
    r.command,
    r.database_id,
    r.user_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    r.total_elapsed_time,
    r.cpu_time,
    r.transaction_isolation_level,
    r.row_count,
    st.text
ORDER BY
    r.total_elapsed_time DESC;

Truncating might be faster [than deleting] but these two tables contain data for multiple files and I must be able to delete one at a time. The nvarchar(max) columns could probably be something else, this is just what Entity Framework generated based on our domain classes. Do you think that would help with this problem?

I confirmed that deleting records directly from FileUploadChunks is just as slow. On my extra slow example (S0 10 DTU) it's around 1 second per row deleted.

  • 2
    James, are you really just deleting everything via delete FileUploads? If so, have you tried deleting by individual UploadFileId? Does that change anything? Have you tried deleting rows in UploadFileChunk individually, just to see if that takes the same amount of time? Also, assuming that you don't really reference UploadFileChunk.Id, you might consider changing the Clustered Index on UploadFileChunk to be UploadFileId, Index (declared as UNIQUE) and making the PK on Id be non-clustered. – Solomon Rutzky Apr 24 '17 at 15:55
  • @srutzky I confirmed that deleting records directly from FileUploadChunks is just as slow. On my extra slow example (S0 10 DTU) it's around 1 second per row deleted. I'll try your index change next. – James Orr Apr 24 '17 at 17:08
  • I can't seem to reproduce the issue on my locally installed version of SQL Server 2016. I'm not an expert but it's not clear to me why these deletes need to be so slow. Here's the code I ran: pastebin.com/45m4ksAn . Did I miss something? – Joe Obbish Jun 3 '17 at 4:03
  • Your sample looks pretty reasonable @JoeObbish. Sorry I can't tell you what the issue is - the support ticket we went through didn't provide us a technical explanation of why, just that we couldn't expect to fix it. – James Orr Jun 4 '17 at 22:09
1

I've just concluded a week of back and forth with Microsoft's gold partner support. They have confirmed that this situation is not getting any better and that these slow deletes are the normal behaviour of SQL Azure/2016.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.