Negative effects of reducing the POA Table

(0) Share

Report

Posted on by Community Member

Hi,

I'm not sure if this question has been asked before but I've done online and community searches without finding an answer.

Before I ask I want to make you aware that I've been employed as a developer technician to support our organisations Dynamics CRM 2011 application. Before this my background was in IT Support with no experience of Dynamics. The organisation was looking for a candidate with no experience but could be trained as it's a heavily customised solution.

With the above in mind please forgive any errors or misunderstandings as I still have a lot to learn.

The application we have is grinding to a halt as we currently have over 220 million records in the POA table which was reduced down from 290 million recently. My understanding is the POA table contains entries for permissions (shares) to items within Dynamics. We currently have a third party administering the app and they previously reduced it down to 250+ million from over 700+ million.

The question is can reducing the POA table have an effect on search functionality? Ever since these deletion jobs have been carried out it seems we receive generic error messages when we do any type of search, whether quick or advanced find. Same thing occurs with views which I understand are simply saved advanced find queries.

Is there anything we need to be careful of when deleting records from the POA table? From what I've been advised it's only orphaned records that are being removed and this shouldn't have an effect on search functionality.

Just to add, I've read the AsyncOperationBase and WorkflowLogBase tables can also affect performance if they become very large but they're a fraction the size of the POA table.

Any advise would be much appreciated.

Many thanks

Vincent

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

razdynamics 17,308 User Group Leader on at

Like (0)

Report

RE: Negative effects of reducing the POA Table

Hi Vincent

Since almost every access to CRM data interacts with the POA table, it can become a bottleneck for access. you may struggle with performance as this table crosses the 10, 15 or 20 Million Record threshold. The threshold for performance challenges gets pushed out with better optimized SQL servers. One tip is to make sure your SQL server is right-sized and the indexes are defragmented and maintained.

Another option is to review how your Business Units are structured, as well as review the objects in your POA Table;

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. (These are not visible in the ‘Sharing’ dialog.

So making changes to your POA table can have an impact on your search, and you would need to review what changes were make to your POA table so you can diagnose the cause.

I hope this helps, Kindly tick 'Yes' to verify :)

Best Wishes, Raz

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Negative effects of reducing the POA Table

Hi Raz,

Thanks for your response. I've seen this article through my search efforts and it doesn't answer my question unfortunately.

to clarify, my question is regarding whether making changes to the POA table can cause errors to occur while searching.

If a record in the table is deleted for an object, say a case, can the case still be found but without permissions or will it generate an error?

Was this reply helpful? Yes No
Suggested answer

razdynamics 17,308 User Group Leader on at

Like (0)

Report

RE: Negative effects of reducing the POA Table

The POA is instrumental for search results, I believe if the corresponding permissions are removed it will error as the permissions will be missing when the user submits the search query.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Negative effects of reducing the POA Table

Does anyone know if performance issues due to POA, affect all versions of CRM / 365? Or only the oldest ones?

Was this reply helpful? Yes No
Suggested answer

Wayne Walton 13,728 on at

Like (0)

Report

RE: Negative effects of reducing the POA Table

2011 is the last version the POA table was an issue. That said, Microsoft released official guidance that the "Share" functionality should not be used systemically or programmatically to prevent unneeded growth of the POA table.

In short, the POA table's growth was largely due to people abusing an ad-hoc sharing mechanism to systemically work around security, which was never the point.

Also, there are safe ways to clean up the POA table. The other part of the issue was CRM 2011 not properly cleaning out the table. I would bet this was the initial cleanup that cut down the 700m rows: support.microsoft.com/.../how-to-control-principalobjectaccess-table-growth-in-microsoft-dynamic

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Negative effects of reducing the POA Table

Ok, but the problem in 2011 was that it was not cleaned out properly, just that right?. But regardless the cleaning, if in a newer version you have 100 million records, could it cause slowness?

Was this reply helpful? Yes No
Suggested answer

Ben Thompson 6,350 on at

Like (0)

Report

RE: Negative effects of reducing the POA Table

Yes, it will have a performance hit as every retrieve request will hit that table and even with every appropriate index created querying a table with 100m records takes time. I would describe it as running up a ramp into a brick wall, queries (and updates) eventually start to slow down until the day occurs when things start to grind to a halt.

It's especially true if your security model makes extensive use of teams as that multiples the number of joins into the POA table within the final SQL retrieve statement.

@Wayne, I would argue that the use of sharing is not an ad-hoc mechanism to work round security - there are scenarios (Banks, Government Agencies) where access to every individual records has to be explicitly authorised and in that circumstance the use of sharing has been actively encouraged by the product team (even to the extent of putting all users in a separate user only business unit with every record explicitly shared). One reason why I'm now doing what I'm doing rather than continuing within the Global Domain is to provide configuration only security solutions that address these markets on an ISV basis. Version 9 of Dynamics 365 offers a world of options thanks to architectural changes that simply did not exist in previous version.

Was this reply helpful? Yes No
Suggested answer

Shidin Haridas 3,497 on at

Like (0)

Report

RE: Negative effects of reducing the POA Table

Since we are on the top of performance optimization in 2011, here is the official white paper: https://technet.microsoft.com/en-us/library/hh413200(v=crm.6).aspx

This link https://support.microsoft.com/en-us/help/2664150/how-to-control-principalobjectaccess-table-growth-in-microsoft-dynamic will help to identify the abandonded records / deleted records' POA rows and deletes them

Was this reply helpful? Yes No
Ben Thompson 6,350 on at

Like (0)

Report

RE: Negative effects of reducing the POA Table

There should be no need for that, for versions after 2011 provided the Deletion Service isn't failing with error messages.

If it is my first suggestion would be to check it is scheduled to run outside working hours (and remember if you have scheduled overnight batch jobs you don't want it running then either) and only if that fails run the scripts in the second link on a quiet Sunday morning.

Was this reply helpful? Yes No
Wayne Walton 13,728 on at

Like (0)

Report

RE: Negative effects of reducing the POA Table

@Ben, I definitely agree there are valid business needs for systemic sharing, I was only addressing the technical limitations of Microsoft's implementation at the time, which definitely did not intend for such use-cases.

Was this reply helpful? Yes No