Slow query in CRM on-premise environment

[hsvensson]

Hi!

This is perhaps posted in the incorrect forum, but my hope is that someone could at least point me in the right direction in this case.

I have a customer that hosts their own on-premise CRM environments (Microsoft Dynamics 365 v9.0.22.8). The customer has several business units in their production environment. For some reason there is a specific SQL query that is run for users that do not have full read privilege (i.e. organization-level read access) on the Case entity (incident) whenever they open a Contact record in the GUI. If the user has full read privilege this query is never executed. The problem is that this query is executed quite a lot and depending on the size of the PrincipalObjectAccess table (which the query is using) this has become a performance issue. Sometimes this query runs for 0.5 to 1 minute. And it is run very often in a typical business day. Below is an example of such a query:

with "incident0Security"
as (
	select Title as "Title"
		,CaseOriginCode as "CaseOriginCode"
		,IncidentId as "IncidentId"
		,ProcessId as "ProcessId"
		,VersionNumber as "VersionNumber"
		,ModifiedOn as "ModifiedOn"
		,StateCode as "StateCode"
		,OwningBusinessUnit as "OwningBusinessUnit"
	from [Incident] as "incident0"
	where "incident0".IncidentId in (
			select ObjectId
			from fn_POARetrieveMultipleNonHierarchy(@SystemUserId0, @ObjectTypeCode0)
			)
	
	union
	
	select Title as "Title"
		,CaseOriginCode as "CaseOriginCode"
		,IncidentId as "IncidentId"
		,ProcessId as "ProcessId"
		,VersionNumber as "VersionNumber"
		,ModifiedOn as "ModifiedOn"
		,StateCode as "StateCode"
		,OwningBusinessUnit as "OwningBusinessUnit"
	from [Incident] as "incident0"
	where "incident0".OwningBusinessUnit in (@buId00)
	)
select top 9 "incident0".Title as "title"
	,"incident0".CaseOriginCode as "caseorigincode"
	,"incident0".IncidentId as "incidentid"
	,"incident0".ProcessId as "processid"
	,convert(bigint, "incident0".VersionNumber) as "versionnumber"
	,"incident0".ModifiedOn as "modifiedon"
	,convert(bigint, "processidworkflowworkflowid".VersionNumber) as "processidworkflowworkflowid.versionnumber"
from incident0Security as "incident0"
left outer join Workflow as "processidworkflowworkflowid" on ("incident0".ProcessId = "processidworkflowworkflowid".WorkflowId)
where ("incident0".StateCode = @StateCode0)
order by "incident0".ModifiedOn desc
	,"incident0".IncidentId asc.

I would like to know why this query needs to be executed whenever the user opens the Contact form and does not have organizational-level read privilege on the Case entity and whether there is a way to avoid this query from being executed without having to change the security settings of the environment (i.e., without giving all users full read privilege on the Case entity). Any ideas or suggestions on what can be done to resolve this issue would be greatly appreciated.

I should also mention that I have looked into the orphan-problem in table PrincipalObjectAccess (identified in CRM 2011), but the script Microsoft provided for that issue does not find any such records in the PrincipalObjectAccess table in my customer's database.

Best regards,

Henrik Svensson

[ChrisPiasecki]

Hi @hsvensson,

As you mentioned, the size of the PrincipalObjectAccess table impacts performance on access checks. Reducing the size of this table is the best way to improve performance. Do you know if there is a lot of ad-hoc record Sharing occurring? Every time someone shares a record with someone, it will create a record in the table, and this is the a well-known performance killer when used in volume. If possible, i'd recommend limiting share privileges and removing the existing ad-hoc shares to reduce the size.

Outside of that, the only thing that could be impacting is database server performance. Make sure the server is tuned and appropriately scaled to the needs.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[hsvensson]

Thank you for the input. I will look into whether there is any unnecessary ad-hoc sharing going on.