Do Not Remove Unused Blobs On Save

I have not been actively hands-on with Sitecore lately. But once in a while I come across a question that sounds like a good puzzle to roll up my sleeves for, and then I just can’t help it.

Query

One of our engineers posted a question. Their client’s CM instance was running noticabely slow and the users were complaining. They quicky identified the bottleneck with the SQL profiler but the finding puzzled them:

IF EXISTS (SELECT NULL 
           FROM [SharedFields] WITH (NOLOCK) 
           WHERE [SharedFields].[Value] LIKE @blobId)
BEGIN
  SELECT 1
END
ELSE IF EXISTS (SELECT NULL 
                FROM [VersionedFields] WITH (NOLOCK) 
                WHERE [VersionedFields].[Value] LIKE @blobId)
BEGIN
  SELECT 1
END
ELSE IF EXISTS (SELECT NULL 
                FROM [ArchivedFields] WITH (NOLOCK) 
                WHERE [ArchivedFields].[Value] LIKE @blobId)
BEGIN
  SELECT 1
END
ELSE IF EXISTS (SELECT NULL 
                FROM [UnversionedFields] WITH (NOLOCK) 
                WHERE [UnversionedFields].[Value] LIKE @blobId)
BEGIN
  SELECT 1
END

Who Are You

I have once traversed basic item APIs all the way down to data providers and back so I just knew where to look. SqlServerDataProvider in Sitecore.Kernel has a method with a very telling name that runs this query.

The name of the method is - GetCheckIfBlobShouldBeDeletedSql(). Walking up the usages chain I found who runs it:

public override bool SaveItem(...)
{
  // ...
  if (Settings.RemoveUnusedBlobsOnSave)
  {
    ManagedThreadPool.QueueUserWorkItem((state => this.RemoveOldBlobs(changes, context)));
  }
  // ...
}

Every item save will call RemoveOldBlobs() that will end up running the mentioned SQL query if RemoveUnusedBlobsOnSave is set to true.

The method runs asynchronously so it doesn’t directly impact the executing thread, but it does put pressure onto the SQL server. Running LIKE logic looking for GUIDs (even without %) in a non-indexed nvarchar field across mutliple tables will take some cycles.

Recommendation

It’s good that this logic is protected with a feature toggle.

I suggested that the team turns off Settings.RemoveUnusedBlobsOnSave and contacts Sitecore Support.

This behavior was observed in 8.1 Update 2. I opened 8.0 Initial Release just out of curiosity and SaveItem() doesn’t go looking for old BLOBs. I didn’t go through more recent releases but it has got to be a relatively new addition. Probably added for a reason.

---

If we turn off running it on every item save, when should we run it? Maybe it’s missing the ID of the saved item in the WHERE to make it a lot more specific? Don’t know. I will update this post if/when we hear back from the support team.