Another experience with cleaning up the PrincipalObjectAccess table - Dialogs

  • Article

 

 

 

Hey! I am back with yet another interesting yet intriguing situation. Without further ado, let me straight away get to explaining the scenario for you all. Happy reading!

Today, I was working with one of my customers and got into cleaning up the PrincipalObjectAccess table cleanup. I started looking at some of the references like: https://support.microsoft.com/kb/2664150?wa=wsignin1.0 , https://blogs.msdn.com/b/crminthefield/archive/2011/06/09/principalobjectaccess-performance-recommendations.aspx and https://blogs.msdn.com/b/crminthefield/archive/2010/08/16/excessive-principalobjectaccess-poa-table-growth-in-crm-4-0.aspx .

These were the best resources I could find in my case to explain and talk about those entries in the POA table. However, I was looking for some action plan on actual possible cleanup of the records from the POA table. Hence, started looking at the number of counts of type of records in the POA table, this would give a good starting point to nail down what is causing the enormous growth of the POA table.

Query to find the number of records with the type in POA table:

USE ORGANIZATION_MSCRM (replace it with your organization database name)

Select e.Name, COUNT(PrincipalObjectAccessId) as 'cnt'

From PrincipalObjectAccess poa with (nolock)

Join EntityLogicalView e with (nolock) on e.ObjectTypeCode=poa.ObjectTypeCode

Where poa.PrincipalTypeCode=8

Group by e.Name

Order by cnt desc

Go

This certainly gave me the record count and that was in millions, as expected :).

Now, I had to plan for cleaning up possible records from the table. During the investigation, I also found that there were lots of entries in the AsynOperationBase table and WorkflowLogBase table marked with completed state. So I knew that in this environment AsyncOpeartionBase and WorkflowLogBase cleanup was never done, hence, had to do that too. So, I followed the KB article: https://support.microsoft.com/kb/968520 which talks about cleaning up the AsyncOperationBase table which also removes entries from the POA table for the completed workflows.

Thankfully, cleanup was successful. But, surprisingly, even now, there were a lot of records for the WorkflowLog in the POA table . Now, I had to investigate and see what is causing these many entries into the POA table.

I found out that every execution of a Dialog (Process of category Dialog) creates a number of entries in the PrincipalObjectAccess table and the WorkflowLogBase table. The entries created in the PrincipalObjectAccess table have objecttypecode corresponding to WorkflowLog. The creation of these entries results in an excessive growth of the PrincipalObjectAccess table, which has a negative performance impact on the CRM overall.

By now it was clear that I had to focus on cleaning up entries for the Dialogs as well. Worked on this further with a couple of support engineers to confirm on the behavior and could understand that:

The creation of these POA entries is expected behavior, as all WorkflowLogBase items also create POA entries. When a dialog is executed, it is similar to a workflow and utilizes the WorkflowLogBase table. When a workflow is fired, it is executed in the AsyncOperationBase table, but entries are also created in WorkflowLogBase and POA. Dialogs work the same way, except that they are executed in the ProcessSessionBase table instead of AsyncOperationBase.

Hence, had an action plan to work on which was to cleanup up the POA table, for which, one of my colleagues Erik suggested to clean it up using Bulk Delete operation as following:

The Bulk Delete job you would create here is very similar to what you would do to proactively maintain the AsyncOperationBase table via Bulk Delete. Specific steps for Process Sessions are below:

1. Go to Settings > Data Management > Bulk Record Deletion, and click New.

2. Click Next, then on the Define Search Criteria page set the Look for selection to Process Sessions (in CRM 2013 & 2015, but in CRM 2011, it should be Dialog Sessions).

3. Define the criteria for you Bulk Delete. For example, you could use Status Reason = Completed; Canceled.

4. After defining your criteria, click Next to get to the Select Options page.

5. Provide a name, start time, and recurrence pattern, and then click Next.

6. Click Submit.

If you already have a bulk delete job for Async Operations, or for anything else, ensure that you do not schedule the jobs to run at the exact same time. For example, if you already have a Bulk Delete job that runs nightly, weekly, etc. at midnight; do not also schedule this one to run at midnight. Specify a different time such as 1:00 am instead.

And, finally, this helped a lot in reducing the size of the POA table. It was also a very good learning:

Dialogs which are a kind of workflows not running under Async do not hold any entry into the AsyncOperationBase but hold an entry into the WorkFlowLogBase table. However, it was understood that Dialogs work the same way, except that they are executed in the ProcessSessionBase table instead of AsyncOperationBase. And, it also holds entries into the POA table for the associated entries.

You can try using the above mentioned steps in your environment but with proper testing and understanding of the issue/scenario. This is completely based on a specific scenario and needs to be verified against the scenario you are analyzing.

Hope this helps.