Improve PrincipalObjectAccess (POA) and AsyncoperationBase table performance in MS Dynamics CRM 2011

PrincipalObjectAccess (POA) table

Grant permission for a user to see/edit/delete a record in CRM.

Permissions are granted is through either Direct or Inherited shares. 

Direct Shares are the user shares a record to another user or team through the 'share' button in the ribbon. These records will have a value in the AccessRightsMask colum of the POA table.

Inherited Shares are the result of a number of different configuration rules – such as the shares that cascade to an object based on the ownership of its parent record, Custom code that shares a record, or by the system due to relationship behaviors or system settings. These records will have a value in the InheritedAccessRightsMask colum of the POA table.

The shares granted on objects in CRM are stored in the PrincipalObjectAccess (POA) system table. Since almost every access to CRM data interacts with the POA table.
PrincipalObjectAccess Table:

SELECT TOP 100
    [PrincipalObjectAccessId]
    ,[PrincipalId]
    ,[PrincipalTypeCode]
    ,[ObjectId]
    ,[ObjectTypeCode]
    ,[AccessRightsMask]
    ,[InheritedAccessRightsMask]
FROM
    [PrincipalObjectAccess] WITH (NOLOCK)

PrincipalObjectAccessId – The GUID of share record.
PrincipalId         – The GUID of the User or Team receiving the Share.
PrincipalTypeCode     – indicates whether it’s a User or Team.
ObjectId         – The GUID of the specific object being shared.
ObjectTypeCode         – The object type of the record being shared.
AccessRightsMask     – This field stores the rights granted directly via the ‘Sharing’ dialog.
InheritedAccessRightsMask – The rights applied by the system through cascading or reassignment processes are stored in this field.

1 User

2 Business Unit

4 Parent: Child

8 Organization


Decoding the RightsMask Fields (AccessRightsMask and InheritedAccessRightsMask)

0               No permission
1                  Read
2                  Write
4                  Append
16                AppendTo
32                Create
65536           Delete
262144          Share
524288          Assign
134217728    Undocumented

for example
852023 (1+2+4+16+32+65536+262144+524288) - ALL documneted permissions

Whenever a record is shared against a User or a Team, CRM will be tracking those entries in PincipalObjectAccess table and same will be queried using FilteredView.

FilteredAccount View part of code

or
[Account].[AccountId] in
(
select  POA.ObjectId from PrincipalObjectAccess POA
join SystemUserPrincipals sup (NOLOCK) on POA.PrincipalId = sup.PrincipalId
where sup.SystemUserId = u.SystemUserId and
POA.ObjectTypeCode = 1 and
((POA.AccessRightsMask | POA.InheritedAccessRightsMask) & 1)=1
)

---Get the total number of shared records
SELECT COUNT(0) FROM PrincipalObjectAccess

--Get the total number of shared records grouped by Entity
SELECT  EV.NAME AS [ENTITY NAME],COUNT(POA.OBJECTTYPECODE) AS [RECORDS COUNT]
FROM PRINCIPALOBJECTACCESS POA
INNER JOIN ENTITYLOGICALVIEW EV ON EV.OBJECTTYPECODE = POA.OBJECTTYPECODE
GROUP BY EV.NAME
ORDER BY 2 DESC

--Get the total number of shared records grouped by User
SELECT  SU.FULLNAME AS [USER NAME],COUNT(POA.OBJECTTYPECODE) AS [RECORDS COUNT]
FROM PRINCIPALOBJECTACCESS POA
INNER JOIN SYSTEMUSER SU ON POA.PRINCIPALID= SU.SYSTEMUSERID
GROUP BY SU.FULLNAME
ORDER BY 2 DESC

--Get the total number of shared records grouped by Entity and User
SELECT  SU.FULLNAME AS [USER NAME],EV.NAME AS [ENTITY NAME],COUNT(POA.OBJECTTYPECODE) AS [RECORDS COUNT]
FROM PRINCIPALOBJECTACCESS POA
INNER JOIN SYSTEMUSER SU ON POA.PRINCIPALID= SU.SYSTEMUSERID
INNER JOIN ENTITYLOGICALVIEW EV ON EV.OBJECTTYPECODE = POA.OBJECTTYPECODE
GROUP BY SU.FULLNAME,EV.NAME
ORDER BY 1

SELECT DISTINCT name, objecttypecode
FROM [CRMORGDB_MSCRM].[MetadataSchema].[Entity]
ORDER BY name ASC

Reason to grow POA table large

In system settings to “Share reassinged records with original owner" (ie shared record with the previous owner), If it is activated, all records that are assigned to another user will be automatically shared with the original user. And an entry in the POA table will also be created for that share.

The (Entity Relationship) "Relationship Behavior" cause share records to be created in POA.
Type of Behavior is "Parental" (default), for example X user owns an Account('Company MS'), the relationship between Account and Contact is set to Parental. If another user creates a contact under Account ('Company MS'), a record in the POA table will be created. This record makes sure that user X will have access to the new contact under his Account.

If the parent record is owned by user Adam and a plugin or some import job creates the child records under a system account (integration / admin account) CRM will create a record for each child record in the POA table. This might result in a POA table growing out of control.
If you instead set the relationship configuration to “Referential” you will not generate any extra record in the POA table.

AsyncoperationBase table

Every async operation happens in Dynamics CRM 2011, a record gets saved in Asyncoperationbase table. This table grows very fast

Workflows - In the Administration tab, Check the 'Workflow Job Retention' (Automatically delete completed workflow jobs (to save disk space)) option. It means that every time this workflow job is executed AND if it succeeds, the system job will be deleted.  But this will not keep the workflow job history.

Plugins - Check the 'Delete AsyncOperation if StatusCode = Successful' in the Step.