CleanUp PrincipalObjectAccess after installing Update Rollup 6

In der Tabelle PrincipalObjectAccess (Organisations-Datenbank) speichert das CRM sämtliche Informationen über Sicherheitsfreigaben aller Objekte. Durch einen Fehler im CRM wächst diese Tabelle schnell auf mehrere Millionen Datensätze an, was zu Performance-Problemen führen kann.

Dieser Fehler wurde mit Update Rollup 6 behoben. Allerdings nur für neu angelegte Objekte. Um die Tabelle zu säubern und von Artefakten zu befreien stellt Microsoft in KB2664150 ein SQL-Script bereit.

Vor Ausführung des SQL-Scriptes:

1. Update Rollup 6 auf dem CRM-Server installieren
2. Organization_MSCRM in Zeile 1 Datenbank Namen ersetzen 

Laufzeit:
Datensätze vor dem Script: 3.000.000+
Datensätze nach dem Script: 200.000+
Laufzeit: ~5:00 min

USE [Organization_MSCRM]
GO


BEGIN TRY
 BEGIN TRAN t1 
 

  create table #ToDeletePoaEntries
  (
   ObjectId uniqueidentifier,
   Otc int
  )

  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]

  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 @totalDeleted int = 0

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

  while @@FETCH_STATUS = 0
  begin
   print 'Cleaning up POA for ' + @baseTableName


   declare @deletestatement nvarchar(max)

   -- Insert records to be deleted in [#ToDeletePoaEntries]
   set @deletestatement = '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 @deletestatement

   exec(@deletestatement)
   set @totalCollected = @@ROWCOUNT
   print CAST(@totalCollected as nvarchar(20)) + ' records collected for deletion for ' + @baseTableName

   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'

  -- Delete query


  -- delete all records of the current entity type which don't have corresponding object in the base table
  delete from PrincipalObjectAccess
  from PrincipalObjectAccess poa
  join #ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc

  -- delete PrincipalObjectAccess records 

  set @totalDeleted = @@ROWCOUNT
  print CAST(@totalDeleted as nvarchar(20)) + ' records deleted for ' + @baseTableName

 COMMIT TRAN t1
 PRINT 'EXECUTION SUCCEED'
END TRY
BEGIN CATCH
 ROLLBACK TRAN t1
 PRINT 'EXECUTION FAILED :' + ERROR_MESSAGE()
END CATCH

--
:: Links / Weiterführende Informationen:

• Controlling Principal Object Access Growth in CRM 2011
• PrincipalObjectAccess – Performance Recommendations