6

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

Execution On Prem SQL

Execution on SQL Azure

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Christian
  • 269
  • 1
  • 10
  • 1
    Post your question on dba.stackexchange.com – sepupic Oct 24 '17 at 13:45
  • Table scan, no index on Id? – Stanislav Kundii Oct 24 '17 at 14:40
  • 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. – Christian Oct 24 '17 at 15:26
  • 2
    What is the service tier and DTU level, and which DTU component is maxed during the transaction? – David Browne - Microsoft Oct 24 '17 at 15:37
  • @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 – Christian Oct 24 '17 at 15:53
  • Basic tier is not suitable for production – TheGameiswar Oct 24 '17 at 15:56
  • @Christian `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. – Panagiotis Kanavos Oct 24 '17 at 16:07
  • @Christian base [on this](https://cbailiss.wordpress.com/2014/11/11/azure-sql-database-memory-limits-by-service-tier/) 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 – Panagiotis Kanavos Oct 24 '17 at 16:13
  • @Christian 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 – Panagiotis Kanavos Oct 24 '17 at 16:16
  • 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! – Christian Oct 25 '17 at 07:32

1 Answers1

6

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.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30