PrincipalObjectAccess table. Clean it up fast
In Dynamics CRM on-premise deployment PrinicipalObjectAccess (POA) table contains implicit and explicit entity sharing. Explicit sharing can be created and checked directly with UI via Share button. Implicit sharing is created during different processes by CRM itself: account merging, emails with users in CC, workflows, etc.
During active CRM usage this table may growth enormous. This way it degrades overall UI performance. In the worst scenarios you even get SQL timeout during activities view loading.
There are several articles explaining and trying to solve this problem:
- PrincipalObjectAccess–Performance Recommendations
- How to control PrincipalObjectAccess table growth in Microsoft Dynamics CRM 2011
- Another experience with cleaning up the PrincipalObjectAccess table - Dialogs
- Improve PrincipalObjectAccess (POA) and AsyncoperationBase table performance in MS Dynamics CRM 2011
- Principal Object Access Table - POA table grows large
- Unmasking CRM's PrincipalObjectAccess table - with a free Secret Decoder Ring!
- How to Delete 312 Million Records From CRM’s Principal Object Access Table
Solution seems to be quite easy and straightforward on the first look - delete excessive rows from the table. But it isn't easy to do it fast and without downtime if you let it growth too much. Check the last link from the list above. SQL delete operation may be tricky. Here is delete operation of large data set explained.
When we tried to use vanilla script from KB2554150, we become extremely pissed off. It was really slow. Roughly extrapolated estimations were about month of round-the-clock script running.
Occasionally we found that we need no more that 10% of the actual data from POA table. And just inserting them into new table should be much faster.
I've created a new table with the exact same schema as original POA table. But couldn't copy original rows. Actually there is a tricky VarsionNumber column of timestamp type or to be more precise rowversion. And you can't directly insert or update it. We decided that we must keep original values. This did the magic:
select poa.* into PrincipalObjectAccess_New from PrincipalObjectAccess poa left join [ToDeletePoaEntries] td on poa.PrincipalObjectAccessId = td.PrincipalObjectAccessId where td.PrincipalObjectAccessId IS null;
After that we renamed original table, indices, constraints. Renamed new table to PrincipalObjectAccess and recreated indices and constraints.
As far as I remember it took less then hour to complete that operation. You can find complete script at gist POA-transfer.sql. In case anything went wrong original data will be untouched in table PrincipalObjectAccess_old.
Note that it's distributed AS IS. Always make backups before running critical tasks.