Sitecore Bug - Media Attach Detach Getting Slower

While upgrading to Sitecore 7.2, we faced another big bug regarding slowness. When we attach or detach media file or edit image file online, we found heavy load and slowness on DB server which subsides subsequently, but the browser becomes unresponsive during this time. And slowness is observed across CM platform. (We have not checked but this issue is found from Sitecore 6.5 onwards)

We have New Relic configured on CM server, which shows slow transactions. The stacktrace in all cases has same portion mentioned below:
Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(:0)
Sitecore.Data.DataProviders.Sql.SqlDataProvider.Exists(:0)
Sitecore.Data.DataProviders.Sql.SqlDataProvider.CheckIfBlobShouldBeDeleted(:0)
Sitecore.Data.DataProviders.Sql.SqlDataProvider.RemoveOldBlobs(:0)
Sitecore.Data.DataProviders.Sql.SqlDataProvider.SaveItem(:0)
Alongwith above stacktrace, we also found following as slowest SQL operation which is implemented in CheckIfBlobShouldBeDeleted method, which tells whether blob is used or not:
SELECT tmp.[Id] FROM 
   (SELECT sf.[Id] FROM [SharedFields] sf 
      WHERE sf.[Value] LIKE @blobId 
    UNION 
    SELECT uf.[Id] FROM [UnversionedFields] uf 
      WHERE uf.[Value] LIKE @blobId 
    UNION 
    SELECT vf.[Id] FROM [VersionedFields] vf 
      WHERE vf.[Value] LIKE @blobId 
    UNION 
    SELECT af.[FieldId] [Id] FROM [ArchivedFields] af 
      WHERE af.[Value] LIKE @blobId 
    ) tmp
Credit goes to Muktesh Mehta for drilling down the issue and Sergey Kravchenko from Sitecore Support who gave us solution for it.

We raised this to Sitecore with all above information, Sitecore provided a new config file and assembly for overriding SqlServerDataProvider, which solved our issue. You can get the assembly and config from Sitecore Support using Ticket# 412563.

Hope, this will help you if you are facing the same issue!

Media Performance Related Posts:
- IRequiresSessionState slowing down Sitecore Media Requests
- Improve Sitecore Media Performance using Reverse Proxy
- Upload Sitecore media items in selected languages

Other Sitecore 7.2 Bugs Solved!



Comments