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:
1 | IF EXISTS (SELECT NULL |
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:
1 | public override bool SaveItem(...) |
Every item save will call
RemoveOldBlobs()
that will end up running the mentioned SQL query ifRemoveUnusedBlobsOnSave
is set totrue
.
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.
Do Not Remove Unused Blobs On Save
https://www.pveller.com/sitecore-remove-unused-blobs-on-save/