T-sql – VarBinary(max) updates very slow on SQL Azure

azure-sql-databaseperformancequery-performancet-sql

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

On Prem SQL 2016
enter image description here

Azure SQL database
enter image description here

Best Answer

The on-prem instance was just a local install on my surface. The other is a 50eDTU elastic pool on Basic-tier. After the comment I changed it to a standard tier and the query now takes about 2 seconds instead of more than 15! So I think that's the answer!

I never thought that a local install of SQL on a surface together with all other software like Visual Studio etc running, would out perform SQL Azure by 25 times! (Even on a basic tier). But now I know :-)

Related Question