VarBinary(max) updates very slow on SQL Azure

Question

We store some documents in a SQL Server database VarBinary(Max) column. Most documents will be a few KB, but sometimes it maybe a couple of MB.

We run into an issue when the file becomes bigger than about 4MB.

When updating the VarBinary column in a on-prem SQL Server, it is very fast (0.6 seconds for a 8MB file).

When doing the same statement on a identical database on SQL Azure, it takes more than 15 seconds!

Also if the code is running from an Azure App Service it is very slow. So it's not our Internet connection that is the problem.

I know storing files in SQL Server is not the preferred way of storing and Blob storage would normally the best solution, but we have special reasons we need to do this, so I want to leave that out of the discussion ;-)

When investigating the execution plans, I see a "Table Spool" taking all the time and I'm not sure why. Below are the execution plans for on prem and Azure.

Identical databases and data. If someone can help, that would be great.

Thanks Chris

Unfriendly

Post your question on dba.stackexchange.com – Eagan

Table scan, no index on Id? – Leduc

Hi @TapakahUa, Thank you for your comment. It's table with only 5 records, so the missing index should not matter. And because I wanted to rule out that the updating of the index (and it's statistics) would be the cause, I removed the index. It's there on the real table. The table scan is only 0.004 seconds. – Unfriendly

What is the service tier and DTU level, and which DTU component is maxed during the transaction? – Austrasia

@david This test database is on a 50 eDTU elastic databasepool. Basic-tier. Didn't think of that! When I get back to the office I'll try the same on a standard-tier and let you know the results. Thank you – Unfriendly

Basic tier is not suitable for production – Karli

@Unfriendly varbinary(max) is used for blob storage. Azure Blob storage is a far cheaper and probably faster alternative for file storage. Just because you can use varbinary(max) to store blobs in Azure SQL doesn't mean you should. – Option

@Unfriendly base on this Basic Tier has 250BM of RAM. That's rather ... small. Storing 4MB blobs is going to eat quite a bit of that RAM and spill out to TempDB, as you see in your execution plan. On an on-prem database you'd use FILESTREAM instead. This isn't available on Azure SQL. It would be a lot cheaper if you used Blob storage for files – Option

@Unfriendly finally, moving 4MB buffers is a bad idea, both for the database and your web site. Both would have to buffer the entire 4MBs before it can be sent to the end user. It's better to use streaming IO and copy eg, the blob's stream to the WebResponse stream without buffering – Option

I added the database to a Standard pool and time dropped from 15.2s to 2.0s! Because all other querying was performing pretty well, I never thought of the pricing tier. So thank you very much @DavidBrowne-Microsoft !! Saved my day. @ TheGameiswar we do use "standard-tier" on production, this was a dev/test environment @ PanagiotisKanavos, thank you for your comments, but as stated in my question, I know blob storage will be perfect for most solutions. But we have a perfectly valid scenario where we want to use this, and as it turns out, it works well on the standard-tier. Thank you all! – Unfriendly

Answer 1

The table spool operator is caching the row to be updated (In tempdb) and then feeds it to the Table Update operator, Spool operators are a sign that the database engine is performing a large number of writes (8 KB pages) to TempDB.

For I/O intensive workloads you need to scale to Premium tiers. On Basic and Standard tiers those updates won’t have a good performance.

Hope this helps.

Thou

Thank you @Alberto. Marked it as answer – Unfriendly

Have a great day! Thank you! – Thou