web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Microsoft Dynamics CRM Support Blog / Sharing Considerations

Sharing Considerations

Views (9438)
Erik Hagen Profile Picture Erik Hagen

This post is part of a multi-part blog series regarding RetrieveMultiple performance.  To see a list of the other parts of this series, click here.

The RetrieveMultipleSharingCountThreshold setting is what we use to determine at which point we should utilize a Table Valued Function (TVF) to calculate sharing.  The default value for this setting is 1000, and can be modified via OrgDbOrgSettings.  If the user’s share count is below the value for this setting, we will use TVF to calculate sharing.
 
A user’s share count is determined by retrieving the value from the PrincipalObjectAccessReadSnapshot table.  The user’s share count is broken down by entity in this table.  The table is updated daily by Maintenance Job 46 (RefreshReadSharingSnapshots).  A helpful query that can be manually executed to review the Record Count sizes for every user and every entity is below:
select b.FullName,a.Count,c.Name as EntityName from PrincipalObjectAccessReadSnapshot a
join systemuserbase b on a.principalid=b.systemuserid
join entity c on a.ObjectTypeCode=c.ObjectTypeCode
order by b.FullName asc, a.Count desc
Query examples are below, with the highlighted portion illustrating the differences based on which setting is used:

Query Below RetreiveMultipleSharingCountThreshold Value

exec sp_executesql N'WITH "activitypointer0Security" as (
select Subject as "Subject", PriorityCode as "PriorityCode", RegardingObjectId as "RegardingObjectId", ActivityTypeCode as "ActivityTypeCode", StateCode as "StateCode", ScheduledStart as "ScheduledStart", ScheduledEnd as "ScheduledEnd", InstanceTypeCode as "InstanceTypeCode", ActivityId as "ActivityId", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".OwnerId in (SELECT id from dbo.fn_GetGuidsFromString(@buIds0))
 
UNION
select Subject as "Subject", PriorityCode as "PriorityCode", RegardingObjectId as "RegardingObjectId", ActivityTypeCode as "ActivityTypeCode", StateCode as "StateCode", ScheduledStart as "ScheduledStart", ScheduledEnd as "ScheduledEnd", InstanceTypeCode as "InstanceTypeCode", ActivityId as "ActivityId", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".ActivityId in (select ObjectId from fn_POARetrieveMultiple(@SystemUserId0, @ObjectTypeCode0,@isHierarchicalSecurityModelEnabled0))
UNION
select Subject as "Subject", PriorityCode as "PriorityCode", RegardingObjectId as "RegardingObjectId", ActivityTypeCode as "ActivityTypeCode", StateCode as "StateCode", ScheduledStart as "ScheduledStart", ScheduledEnd as "ScheduledEnd", InstanceTypeCode as "InstanceTypeCode", ActivityId as "ActivityId", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".OwningBusinessUnit in (@buId00)
 
)
select
top 51 "activitypointer0".Subject as "subject"
, "activitypointer0".PriorityCode as "prioritycode"
, "activitypointer0".RegardingObjectId as "regardingobjectid"
, "activitypointer0".ActivityTypeCode as "activitytypecode"
, "activitypointer0".StateCode as "statecode"
, "activitypointer0".ScheduledStart as "scheduledstart"
, "activitypointer0".ScheduledEnd as "scheduledend"
, "activitypointer0".InstanceTypeCode as "instancetypecode"
, "activitypointer0".ActivityId as "activityid"
, "activitypointer0".RegardingObjectIdYomiName as "regardingobjectidyominame"
, "activitypointer0".RegardingObjectTypeCode as "regardingobjecttypecode"
, "activitypointer0".RegardingObjectIdName as "regardingobjectidname"
, "activitypointerowningusersystemusersystemuserid".InternalEMailAddress as "activitypointerowningusersystemusersystemuserid.internalemailaddress"
from
 activitypointer0Security as "activitypointer0" WITH (NOLOCK)  left outer join SystemUser as "activitypointerowningusersystemusersystemuserid" WITH (NOLOCK)  on ("activitypointer0".OwningUser  =  "activitypointerowningusersystemusersystemuserid".SystemUserId)
where
 (("activitypointer0".IsRegularActivity = @IsRegularActivity0)) order by
 "activitypointer0".ScheduledEnd asc
, "activitypointer0".ActivityId asc'

Query Above RetreiveMultipleSharingCountThreshold Value

WITH "activitypointer0Security" as (
select Subject as "Subject", PriorityCode as "PriorityCode", RegardingObjectId as "RegardingObjectId", ActivityTypeCode as "ActivityTypeCode", StateCode as "StateCode", ScheduledStart as "ScheduledStart", ScheduledEnd as "ScheduledEnd", InstanceTypeCode as "InstanceTypeCode", ActivityId as "ActivityId", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".OwnerId in (SELECT id from dbo.fn_GetGuidsFromString(@buIds0))
 
UNION
select Subject as "Subject", PriorityCode as "PriorityCode", RegardingObjectId as "RegardingObjectId", ActivityTypeCode as "ActivityTypeCode", StateCode as "StateCode", ScheduledStart as "ScheduledStart", ScheduledEnd as "ScheduledEnd", InstanceTypeCode as "InstanceTypeCode", ActivityId as "ActivityId", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".ActivityId in (select POA.ObjectId from PrincipalObjectAccess POA  WITH (NOLOCK) join SystemUserPrincipals sup  WITH (NOLOCK) on POA.PrincipalId = sup.PrincipalId where sup.SystemUserId = @SystemUserId00 and POA.ObjectTypeCode = @ObjectTypeCode0 and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1)
UNION
select Subject as "Subject", PriorityCode as "PriorityCode", RegardingObjectId as "RegardingObjectId", ActivityTypeCode as "ActivityTypeCode", StateCode as "StateCode", ScheduledStart as "ScheduledStart", ScheduledEnd as "ScheduledEnd", InstanceTypeCode as "InstanceTypeCode", ActivityId as "ActivityId", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", IsRegularActivity as "IsRegularActivity", OwnerId as "OwnerId", OwningBusinessUnit as "OwningBusinessUnit", OwningUser as "OwningUser" from [ActivityPointer] as "activitypointer0" where "activitypointer0".OwningBusinessUnit in (@buId00)
 
)
select
top 51 "activitypointer0".Subject as "subject"
, "activitypointer0".PriorityCode as "prioritycode"
, "activitypointer0".RegardingObjectId as "regardingobjectid"
, "activitypointer0".ActivityTypeCode as "activitytypecode"
, "activitypointer0".StateCode as "statecode"
, "activitypointer0".ScheduledStart as "scheduledstart"
, "activitypointer0".ScheduledEnd as "scheduledend"
, "activitypointer0".InstanceTypeCode as "instancetypecode"
, "activitypointer0".ActivityId as "activityid"
, "activitypointer0".RegardingObjectIdYomiName as "regardingobjectidyominame"
, "activitypointer0".RegardingObjectTypeCode as "regardingobjecttypecode"
, "activitypointer0".RegardingObjectIdName as "regardingobjectidname"
, "activitypointerowningusersystemusersystemuserid".InternalEMailAddress as "activitypointerowningusersystemusersystemuserid.internalemailaddress"
from
 activitypointer0Security as "activitypointer0" WITH (NOLOCK)  left outer join SystemUser as "activitypointerowningusersystemusersystemuserid" WITH (NOLOCK)  on ("activitypointer0".OwningUser  =  "activitypointerowningusersystemusersystemuserid".SystemUserId)
where
 (("activitypointer0".IsRegularActivity = @IsRegularActivity0)) order by
 "activitypointer0".ScheduledEnd asc
, "activitypointer0".ActivityId asc
 
 
 

SharingLimitForSnapshotTable Note


This setting determines the threshold in which a Principal/ObjectTypeCode entry should be created in the PrincipalObjectAccessReadSnapshot table.  The default value for this setting is 10, and it can be modified via OrgDbOrgSettings.  This value should be increased for customers that have users who are members of a large number of teams, and each team has a small amount of records shared.  The setting increase would allow CRM to better handle this scenario, and better detect whether or not TVF should be used.

 
 
Next post in this blog series: IdsCount* Settings. 

Comments

*This post is locked for comments