Lessons Learned Deleting 312 Million Records From CRM’s PrincipalObjectAccess Table

Unrestrained growth of the POA (PrincipalObjectAccess) SQL table in CRM is nothing new.  Performance of on premise CRM installations can start to be noticeably affected at 10 million records or so.  You can imagine my surprise when I learned my client had just over 1/3 of a billion records!  Microsoft has a script to clean it up, which probably works great at cleaning up a couple million records, but is not really an option when needing to clean up 312 million. 

After multiple modifications to Microsoft's scripts in our test environments we ended up splitting/modifying the Microsoft into 4 different scripts.

1. Create and populate ToDeletePOAEntries Table.
2. Drop the existing indexes on the POA table, add a single index just with the columns needed for the join
3. Delete the POA records
4. Reset the index changes made in Script 2.

The smartest thing we did was probably sending an e-mail, every time the script deleted a million records.  This allowed us to see trends in how long it was taking to delete and attempt to fix the issue.

Here is a graph of the e-mails received for the first 82 million, which took just over 9 hours:

 

We wrongly assumed that the increase in deletions was due to the indexes needing to be rebuilt, but rebuilding the index didn’t change the trend.  We then decided to try rebuilding the ToDeletePOAEntries table and this is what we saw for the next 48 million over the course of 4 hours:

We then edited the script to make the cleanup of the ToDeletePOAEntries automatic.  If I had to do it over again, I’d add an Identity column to the ToDeletePOAEntries table, and update the where of the join for each loop to start at the next batch of entries.  This is what we then saw with the noticeable peeks required to recreate the ToDeletePOAEntries table every 25 million:

 

Here are the scripts that were used.  Hopefully they’ll be helpful for anyone else out there in the same position we were in. It took just over 30 hours to delete 312 million records with CRM itself completely shutdown.

Script 1: Creates ToDeletePOAEntries table, populating it with all of the records that need to be removed out of the POA table.  This is basically the first part of the Microsoft script,

USE [Contoso_MSCRM]
GO

IF NOT EXISTS (SELECT * FROM sys.sysobjects
WHERE id = object_id(N'[dbo].[ToDeletePOAEntries]')
AND ObjectProperty(id, N'IsUserTable') = 1)

create table ToDeletePoaEntries
(
ObjectId uniqueidentifier,
Otc int
)
IF NOT EXISTS (SELECT * FROM sys.sysindexes si
INNER JOIN sys.sysobjects so ON si.id = so.id
WHERE so.id = OBJECT_ID(N'[dbo].[ToDeletePoaEntries]')
AND OBJECTPROPERTY(so.id, N'IsUserTable') = 1
AND si.name LIKE '%mainindex%')

CREATE UNIQUE NONCLUSTERED INDEX [mainindex] ON [dbo].[ToDeletePoaEntries]
(
[ObjectId] ASC,
[Otc] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

----- Insert records to be deleted in ToDeletePoaEntries

-- go through all user-owned entities which are not replicated and don't support duplicate detection
declare entity_cursor cursor local FORWARD_ONLY READ_ONLY
for select distinct e.ObjectTypeCode, e.BaseTableName, a.PhysicalName from EntityView e
inner join AttributeView a on e.EntityId = a.EntityId and a.IsPKAttribute = 1
where e.IsReplicated = 0 and e.IsDuplicateCheckSupported = 0 and e.OwnershipTypeMask & 1 = 1

open entity_cursor

declare @baseTableName sysname
declare @otc nvarchar(20)
declare @primaryKey sysname
declare @totalCollected int = 0
declare @currentCollected int
declare @tempRowCount int = 0
declare @collectstatement nvarchar(max)fetch next from entity_cursor
into @otc, @baseTableName, @primaryKeywhile @@FETCH_STATUS = 0
begin
print 'Cleaning up POA for ' + @baseTableName

set @currentCollected = 0


set @collectstatement = 'insert into ToDeletePoaEntries(ObjectId, Otc)
select distinct poa.ObjectId, poa.ObjectTypeCode
from PrincipalObjectAccess poa left join ' + @baseTableName + ' e on poa.ObjectId = e.' + @primaryKey +
' where e.' + @primaryKey + ' is null and poa.ObjectTypeCode = ' + @otc;

print @collectstatement

exec(@collectstatement)
set @tempRowCount = @@ROWCOUNT
set @currentCollected = @currentCollected + @tempRowCount
print CAST(@currentCollected as nvarchar(20)) + ' records collected for ' + @baseTableName
set @totalCollected = @totalCollected + @currentCollected

fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey
end

close entity_cursor
deallocate entity_cursor

print CAST(@totalCollected as nvarchar(20)) + ' total records collected'

Script 2: Drop the existing indexes on the POA table, add a single index just with the columns needed for the join.  This isn’t included in Microsoft’s script, and I don’t have any data as far as how helpful this was, but it should result in the least amount of work required by the database in order to delete a record.

USE [Contoso_MSCRM]

go

/****** Object:  Index [ndx_PrimaryKey_PrincipalObjectAccess]    Script Date: 12/18/2013 08:44:05 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'ndx_PrimaryKey_PrincipalObjectAccess')
  ALTER TABLE [dbo].[principalobjectaccess]
    DROP CONSTRAINT [ndx_PrimaryKey_PrincipalObjectAccess]

go

/****** Object:  Index [ndx_Cover]    Script Date: 12/18/2013 08:44:15 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'ndx_Cover')
  DROP INDEX [ndx_Cover] ON [dbo].[PrincipalObjectAccess] WITH ( online = OFF )

go

/****** Object:  Index [fndx_Sync_VersionNumber]    Script Date: 12/18/2013 08:44:33 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'fndx_Sync_VersionNumber')
  DROP INDEX [fndx_Sync_VersionNumber] ON [dbo].[PrincipalObjectAccess] WITH (
  online = OFF )

go

/****** Object:  Index [cndx_PrincipalObjectAccess]    Script Date: 12/18/2013 08:44:45 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'cndx_PrincipalObjectAccess')
  DROP INDEX [cndx_PrincipalObjectAccess] ON [dbo].[PrincipalObjectAccess] WITH
  (
  online = OFF )

go

/****** Object:  Index [UQ_PrincipalObjectAccess]    Script Date: 12/18/2013 08:45:56 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'UQ_PrincipalObjectAccess')
  ALTER TABLE [dbo].[principalobjectaccess]
    DROP CONSTRAINT [UQ_PrincipalObjectAccess]

go

CREATE NONCLUSTERED INDEX [IX_CleanupIndex]
  ON [dbo].[PrincipalObjectAccess] ( [objectid] ASC, [objecttypecode] ASC )
  WITH (statistics_norecompute = OFF, sort_in_tempdb = OFF, ignore_dup_key = OFF
, drop_existing = OFF, online = OFF, allow_row_locks = ON, allow_page_locks = ON
) ON [PRIMARY]

go 

Script 3: Delete the POA records.  This script has the most changes from the Microsoft version.  It wasn’t until we actually started running the scripts in Prod that we finally figured out our main bottle neck, the join of the POA table to the ToDeletePOAEntries.  For every million records deleted, the next time through the loop, the SQL server would attempt to find records that had just been deleted first, before joining on the records not yet deleted.  There is vast rooms for improvement here I’m sure, but this is what we came up with over winter holiday.

USE [Contoso_MSCRM]

go

-- Delete query
-- This scripts cleans up orphaned POA records for selected entities
DECLARE @deleteBatchSize INT = 50000
DECLARE @deleteBatchSizeNVarChar NVARCHAR(10) = Cast(
        @deleteBatchSize AS NVARCHAR(10))
DECLARE @totalDeleted INT = 0
DECLARE @currentDeleted INT
DECLARE @deletestatement NVARCHAR(max)
DECLARE @retry INT = 1
DECLARE @tempRowCount INT = 0
DECLARE @recipientList NVARCHAR(1000) = 'MSmith@Contoso.com;JDoe@Contoso.com'
DECLARE @backupScript NVARCHAR(1000)
DECLARE @backupCount INT = 0
DECLARE @bodyText NVARCHAR(4000)
DECLARE @subjectText NVARCHAR(1000)
DECLARE @baseTableName SYSNAME
DECLARE @otc NVARCHAR(20)
DECLARE @primaryKey SYSNAME
DECLARE @totalCollected INT = 0
DECLARE @currentCollected INT
DECLARE @tempRowCountRecreate INT = 0
DECLARE @collectstatement NVARCHAR(max)

WHILE ( @retry > 0 )
  BEGIN
      BEGIN try
          SET @currentDeleted = 0
          SET @tempRowCount = 0
          -- delete all records of the current entity type which don't have corresponding object in the base table
          SET @deletestatement = 'delete top (' + @deleteBatchSizeNVarChar
                                 +
') from PrincipalObjectAccess from PrincipalObjectAccess poa join ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc'

    PRINT @deletestatement

    -- delete PrincipalObjectAccess records in batches
    EXEC(@deletestatement)

    SET @tempRowCount = @@ROWCOUNT
    SET @currentDeleted = @currentDeleted + @tempRowCount

    WHILE @tempRowCount = @deleteBatchSize
      BEGIN
          EXEC(@deletestatement)

          SET @tempRowCount = @@ROWCOUNT
          SET @currentDeleted = @currentDeleted + @tempRowCount

          PRINT Cast(@currentDeleted AS NVARCHAR(20))
                + ' records deleted '
                + Cast(Getutcdate() AS NVARCHAR(50))

          -- Every 1,000,000 records notify the team that we have deleted another 1,000,000 records, with the total record count
          IF ( @currentDeleted % 1000000 = 0 )
            BEGIN
                PRINT '1,000,000 records deleted'

                SET @bodyText = CONVERT(NVARCHAR(20), @currentDeleted)
                                + ' POA records deleted.';

                EXECUTE msdb.dbo.Sp_send_dbmail
                  @profile_name = 'SQLNotify Profile',
                  @recipients = @recipientList,
                  @body = @bodyText,
                  @subject = 'POA Cleanup Progress Email',
                  @importance = 'High'
            END

          -- Every 25,000,000 records Rebuild ToDeletePoaEntries
          IF ( @currentDeleted % 25000000 = 0
                OR @currentDeleted <= 100000 )
            BEGIN
                PRINT '25,000,000 records deleted'

                -- DROP AND RECREATE TABLE
                DROP INDEX [mainindex] ON [dbo].[ToDeletePoaEntries]

                DROP TABLE todeletepoaentries

                CREATE TABLE todeletepoaentries
                  (
                     objectid UNIQUEIDENTIFIER,
                     otc      INT
                  )

                ----- Insert records to be deleted in ToDeletePoaEntries
                -- go through all user-owned entities which are not replicated and don't support duplicate detection
                DECLARE entity_cursor CURSOR local forward_only read_only FOR
                  SELECT DISTINCT e.objecttypecode,
                                  e.basetablename,
                                  a.physicalname
                  FROM   entityview e
                         INNER JOIN attributeview a
                                 ON e.entityid = a.entityid
                                    AND a.ispkattribute = 1
                  WHERE  e.isreplicated = 0
                         AND e.isduplicatechecksupported = 0
                         AND e.ownershiptypemask & 1 = 1

                OPEN entity_cursor

                SET @totalCollected = 0
                SET @tempRowCountRecreate = 0

                FETCH next FROM entity_cursor INTO @otc, @baseTableName,
                @primaryKey

                WHILE @@FETCH_STATUS = 0
                  BEGIN
                      PRINT 'Cleaning up POA for ' + @baseTableName

                      SET @currentCollected = 0
                      SET @collectstatement = 'insert into ToDeletePoaEntries(ObjectId, Otc)  select distinct poa.ObjectId, poa.ObjectTypeCode  from PrincipalObjectAccess poa left join ' + @baseTableName
                                              + ' e on poa.ObjectId = e.' +
                                              @primaryKey
                                              + ' where e.' + @primaryKey
                                              +
                      ' is null and poa.ObjectTypeCode = '
                                              +
                                              @otc;

                      PRINT @collectstatement

                      EXEC(@collectstatement)

                      SET @tempRowCountRecreate = @@ROWCOUNT
                      SET @currentCollected =
                      @currentCollected + @tempRowCountRecreate

                      PRINT Cast(@currentCollected AS NVARCHAR(20))
                            + ' records collected for ' + @baseTableName

                      SET @totalCollected = @totalCollected + @currentCollected

                      FETCH next FROM entity_cursor INTO @otc, @baseTableName,
                      @primaryKey
                  END

                CLOSE entity_cursor

                DEALLOCATE entity_cursor

                CREATE UNIQUE NONCLUSTERED INDEX [mainindex]
                  ON [dbo].[ToDeletePoaEntries] ( [objectid] ASC, [otc] ASC )
                  WITH (statistics_norecompute = OFF, sort_in_tempdb = OFF,
                ignore_dup_key
                =
                OFF, drop_existing = OFF, online = OFF, allow_row_locks = ON,
                allow_page_locks = ON) ON [PRIMARY]

                PRINT Cast(@totalCollected AS NVARCHAR(20))
                      + ' total records collected'
            END
      END

    -- We are done! Delete the ToDeletePoaTable and Notify the team
    DROP TABLE [dbo].[todeletepoaentries]

    EXECUTE msdb.dbo.Sp_send_dbmail
      @profile_name = 'SQLNotify Profile',
      @recipients = @recipientList,
      @body = 'All POA Records to be deleted have been deleted.',
      @subject = 'POA Cleanup Complete',
      @importance = 'High'

    -- Reset the @retry value to 0, which will allow the code to exit the WHILE loop
    SET @retry = 0
END try

    BEGIN catch
        PRINT 'Retry ' + CONVERT(NVARCHAR(10), @retry)

        SET @retry = @retry + 1
        -- Hit an error - Notify the team
        SET @bodyText = Error_message();
        SET @subjectText = 'POA Cleanup Progress Email - Error '
                           + CONVERT(NVARCHAR(10), @retry);

        EXECUTE msdb.dbo.Sp_send_dbmail
          @profile_name = 'SQLNotify Profile',
          @recipients = @recipientList,
          @body = @bodyText,
          @subject = @subjectText,
          @importance = 'High'

        -- There was an error so let's reindex
        ALTER INDEX ALL ON dbo.principalobjectaccess reorganize;

        UPDATE STATISTICS dbo.principalobjectaccess;
    END catch
END --END OF THE RETRY LOOP
PRINT 'EXECUTION SUCCEED' 

Script 4: Reset the indexes on the POA table to bring them back to their original condition:

USE [Contoso_MSCRM]

go

IF EXISTS (SELECT *
           FROM   sys.sysindexes si
                  INNER JOIN sys.sysobjects so
                          ON si.id = so.id
           WHERE  so.id = Object_id(N'[dbo].[ToDeletePoaEntries]')
                  AND Objectproperty(so.id, N'IsUserTable') = 1
                  AND si.name LIKE '%mainindex%')
  DROP TABLE [dbo].[todeletepoaentries]

go

USE [Contoso_MSCRM]

go

/****** Object:  Index [IX_CleanupIndex]    Script Date: 12/19/2013 16:02:29 ******/
IF EXISTS (SELECT *
           FROM   sys.indexes
           WHERE  object_id = Object_id(N'[dbo].[PrincipalObjectAccess]')
                  AND name = N'IX_CleanupIndex')
  DROP INDEX [IX_CleanupIndex] ON [dbo].[PrincipalObjectAccess] WITH ( online =
  OFF )

go

/****** Object:  Index [cndx_PrincipalObjectAccess]    Script Date: 12/18/2013 08:52:57 ******/
CREATE CLUSTERED INDEX [cndx_PrincipalObjectAccess]
  ON [dbo].[PrincipalObjectAccess] ( [objectid] ASC, [principalid] ASC )
  WITH (pad_index = OFF, statistics_norecompute = OFF, sort_in_tempdb = OFF,
ignore_dup_key = OFF, drop_existing = OFF, online = OFF, allow_row_locks = ON,
allow_page_locks = ON, FILLFACTOR = 100) ON [PRIMARY]

go

/****** Object:  Index [fndx_Sync_VersionNumber]    Script Date: 12/18/2013 08:53:03 ******/
CREATE UNIQUE NONCLUSTERED INDEX [fndx_Sync_VersionNumber]
  ON [dbo].[PrincipalObjectAccess] ( [versionnumber] ASC )
WHERE ([VersionNumber] IS NOT NULL)
WITH (pad_index = OFF, statistics_norecompute = OFF, sort_in_tempdb = OFF,
ignore_dup_key = OFF, drop_existing = OFF, online = OFF, allow_row_locks = ON,
allow_page_locks = ON, FILLFACTOR = 100) ON [PRIMARY]

go

/****** Object:  Index [ndx_Cover]    Script Date: 12/18/2013 08:53:13 ******/
CREATE NONCLUSTERED INDEX [ndx_Cover]
  ON [dbo].[PrincipalObjectAccess] ( [objecttypecode] ASC, [principalid] ASC,
[accessrightsmask] ASC, [inheritedaccessrightsmask] ASC )
  WITH (pad_index = OFF, statistics_norecompute = OFF, sort_in_tempdb = OFF,
ignore_dup_key = OFF, drop_existing = OFF, online = OFF, allow_row_locks = ON,
allow_page_locks = ON, FILLFACTOR = 80) ON [PRIMARY]

go

/****** Object:  Index [ndx_PrimaryKey_PrincipalObjectAccess]    Script Date: 12/18/2013 08:53:21 ******/
ALTER TABLE [dbo].[principalobjectaccess]
  ADD CONSTRAINT [ndx_PrimaryKey_PrincipalObjectAccess] PRIMARY KEY NONCLUSTERED
  ( [principalobjectaccessid] ASC )WITH (pad_index = OFF, statistics_norecompute
  = OFF, sort_in_tempdb = OFF, ignore_dup_key = OFF, online = OFF,
  allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 100) ON [PRIMARY]

go

/****** Object:  Index [UQ_PrincipalObjectAccess]    Script Date: 12/18/2013 08:53:33 ******/
ALTER TABLE [dbo].[principalobjectaccess]
  ADD CONSTRAINT [UQ_PrincipalObjectAccess] UNIQUE NONCLUSTERED ( [principalid]
  ASC, [objectid] ASC, [objecttypecode] ASC )WITH (pad_index = OFF,
  statistics_norecompute = OFF, sort_in_tempdb = OFF, ignore_dup_key = OFF,
  online = OFF, allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 100)
  ON [PRIMARY]

go