Tuesday, January 22, 2013

Principal Object Access Table - POA table grows large

In Microsoft Dynamics CRM 2011 there is a known performance issue when the POA table grow large.
Just the other day I was working with a customer to help them pin down why the POA table was so large in their organization database. We were also investigating if they could benefit from the use of Teams in CRM 2011 instead of using “share with users” as their POA table was growing a lot.
If users frequently share records with other users you could benefit from setting teams as owners of those records instead. Or to have the users share with Teams instead of specific set of users.
As we started our investigation we had a look at the POA table to see which objects they were sharing.
The POA table stores information about records in CRM that is shared with other users or teams. The share can be made either by a user, custom code that shares a record, or by the system due to relationship behaviors or system settings.
You can configure system settings to “Share record with the previous owner”. This might not be needed in your system. When this setting 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 relationship behavior cause share records to be created in POA.
Let say User Adam owns an Account named Company A and the relationship between Account and Contact is set to the default. “Parental”, then the following will occur.
If another user creates a contact under Account Company a record in the POA table will be created. This record makes sure that user Adam will have access to the new contact under “his” Account. This might be as you want it to be and this might not be an issue at all.
But if we instead have a custom entity that is a parent of another custom entity that is used for logging or to keep track of something that will generate a lot of records. 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.
You will not be able to see this under the “share” properties in CRM. It´s only added to the POA table in this case. This is probably not something you need. If you instead set the relationship configuration to “Referential” you will not generate any extra record in the POA table.
Here is a SQL Query you can start with to see what is taking up the space in your POA table.
(Only possible in On-Premise installations)
SELECT ObjectTypeCode, COUNT(ObjectTypeCode) AS Total
FROM PrincipalObjectAccess
GROUP BY ObjectTypeCode
To find the name of the entity (ObjectTypeCode) you can use the following select statement in SQL Server.
Change the name of the database “CRMORGDB_MSCRM” to your organization database name.
SELECT DISTINCT name, objecttypecode
FROM [CRMORGDB_MSCRM].[MetadataSchema].[Entity]
I hope this will help you to prevent some unnecessary records in the POA Table
Happy POA record hunting clip_image004