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:
- Update Rollup 6 auf dem CRM-Server installieren
- 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:
