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.
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
I have once traversed basic item APIs all the way down to data providers and back so I just knew where to look.
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(...)
Every item save will call
RemoveOldBlobs()that will end up running the mentioned SQL query if
RemoveUnusedBlobsOnSaveis set to
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.
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.