The odd bit

Once is an accident, twice is a coincidence, three times is an enemy action.

The odd bit - Once is an accident, twice is a coincidence, three times is an enemy action.

Sitecore database cleanup problems

As your Sitecore site grows you’ll need to perform a bit of maintenance on the Sitecore databases. Our experience tells us it’s primarily the ‘master’ database that needs some work. The ‘master’ database is the one that gets all the editing while the ‘web’ database is only used as a publishing target.

Our initial wakeup call was a steadily growing ‘master’ database months after adding a synchronisation with an external system. The synchronisation data contains images and as we updated objects the database would keep growing. It’s enough material to create another article but to cut a long story short: the Blobs table would keep growing without removing records. tl;dr orphaned blobs.

An investigation into the deep, dark dungeons of Sitecore.Kernel.dll hinted at a CleanupDatabase() method on the Database class. It does quite a bit of cleaning including the removal of orphaned blobs. We scheduled a task and Eureka! … until recently.

A performance review of our Sitecore installation revealed sporadic timeouts in the logs. And it wasn’t the more or less normal request timeout but a rather alarming SQL timeout. Luckily, the offending SQL statement was also in the logs.

Exception: System.Data.DataException
Message: Error executing SQL command:  declare @x bigint set @x = 0 DECLARE @item TABLE(ID uniqueidentifier,parentID uniqueidentifier) INSERT INTO @item (ID,parentID)   SELECT  [ID],[ParentID] FROM [Items]  DECLARE @temp TABLE(ID uniqueidentifier) WHILE (SELECT count(id) FROM @item ) <> @x begin set @x = (SELECT count(id) FROM @item ) delete from @temp; insert into @temp (ID)   SELECT  id FROM @item where parentID  = @nullId update @item SET Parentid =@nullId where Parentid  in (select id from @temp) delete from @item where  id  in (select id from @temp) end UPDATE [Items] SET [Parentid] = @nullId where [ID]  in (select id from @item) ; DELETE from [Items] where [ID] in (select id from @item)

Nested Exception

Exception: System.Data.SqlClient.SqlException
Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteNonQuery()

Running the query manually proved that it was right on the limit: 4 minutes and 51 seconds on a database with around 44000 items. The standard SQL timeout in Sitecore is 5 minutes. A bit of load on the database server would cause it to go longer than 5 minutes and trigger the timeout error.

Hey, let’s increase the timeout to 10 minutes and we’re done. Wrong! You’re done until the database grows large enough that 10 minutes are no longer sufficient. So I favour a more structural solution: let’s work on that query!

Diving into Sitecore.Kernel.dll once more showed that it was part of the database cleanup, namely the method CleanupCyclicDependences() implemented in SqlServerDataProvider. There are several problems with the query:

  1. A table variable is a bad idea once you’re working with real data (read: more than a couple of rows). If you don’t believe me take a look at the query execution plan. It’s filled with full table scans because SQL Server does not calculate statistics on table variables. The estimated number of rows will always be 1. The query has 2 table variables.
    The right approach would be temporary tables because SQL Server does calculate statistics for temp tables.
  2. Deleting all rows from a table is slower than just truncating that table.
  3. The last two statements are odd: first records are updated and then the same records are deleted. Why update them if the next step is deleting them?

So this is a much better query which will do the same work in 2-4 seconds (+/- 44000 items, depending on server load):

DECLARE @x BIGINT
SET @x = 0
 
CREATE TABLE #item
  (
     id       UNIQUEIDENTIFIER,
     parentid UNIQUEIDENTIFIER
  )
INSERT INTO #item
            (id,
             parentid)
SELECT {0}ID{1},
       {0}ParentID{1}
FROM   {0}Items{1}
 
CREATE TABLE #temp
  (
     id UNIQUEIDENTIFIER
  )
 
WHILE (SELECT Count(id) FROM   #item) <> @x
  BEGIN
      SET @x = (SELECT Count(id)
                FROM   #item)
 
      TRUNCATE TABLE #temp;
 
      INSERT INTO #temp
                  (id)
      SELECT id
      FROM   #item
      WHERE  parentid = {2}nullId{3}
 
      UPDATE #item
      SET    parentid = {2}nullId{3}
      WHERE  parentid IN (SELECT id
                          FROM   #temp)
 
      DELETE FROM #item
      WHERE  id IN (SELECT id
                    FROM   #temp)
  END
 
DELETE FROM {0}Items{1}
WHERE  {0}ID{1} IN (SELECT id
              FROM   #item)
 
DROP TABLE #item
DROP TABLE #temp

Warning: do not run this query on SQL Server because it won’t work! It’s formatted for Sitecore’s data provider.

So the correct solution is:

  1. Create a custom data provider that inherits from SqlServerDataprovider.
  2. Override the CleanupCyclicDependences method.
  3. In the method body type
    this.Api.Execute(@””, “nullId”, ID.Null);
  4. Paste the above query between the double quotes.
  5. Wire your custom data provider in web.config.

And your database cleanup will be a lot faster.