Maintain performance by monitoring shared records

Let me just give you a background on Sharing before we go into details.

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.

If we see the FilteredView definition we can find how CRM uses this PincipalObjectAccess table.

-- object shared to the user
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
)

The code snippet above was picked from FilteredAccount’s where condition. Here this view is getting the records by querying the PrincipalObjectAccess table

And it’s a known fact that POA is to cause performance bottlenecks.  POA is one of the reasons why complicated business unit structures and extensive sharing is not recommended.

Refer to Microsoft Support article wherein we get the symptoms of Timeout issues. The large is the POA table size the more is the problems list.

Please refer to a nice article on “Why Large-Scale Sharing is Bad”

Here are some queries by which you can track and monitor how the organization sharing is being handled.

–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

Refer to Support Article on controlling PrincipalObjectAccess table growth and cleaning it up

Update Rollup 6 and later will clean the POA table by deleting the unshared records.

Hope this helps 🙂