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 primary setting that is used to determine how to handle the security considerations of a RetrieveMultiple query for non-admin users is EnableRetrieveMultipleOptimization (ERMO).  The following are each of the applicable ERMO settings.  Please note in that almost all cases, setting ERMO to 0 will be the best option.

 

ERMO = 0

 

This should be the most commonly used setting for optimal performance.  This setting allows you to take advantage of the various sub-settings that will be discussed in upcoming posts.  We define this as the best setting to use when you have a wide variety of visibility counts between users, but in general it should be used in most situations.  One example of a query when ERMO is set to 0 is below, but please note that this is not all inclusive since various sub-settings can further alter the query here.
 
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

ERMO = 1

This setting was considered the most optimal in CRM 4.0, as many of the additional settings we have today were not available.  Today we recommend considering this setting when all users have access restricted to just the data they own, and do not have any associated shares.  This would generally be a very uncommon scenario.
 
An example of a query when ERMO is set to 1 is below:
 
create table #ObjectsIds (ObjectId uniqueidentifier);
create NONCLUSTERED index ndx_ObjectsIds_objId on #ObjectsIds (ObjectId ASC);
insert into #ObjectsIds
select POA.ObjectId from PrincipalObjectAccess POA  WITH (NOLOCK) join SystemUserPrincipals sup  WITH (NOLOCK) on POA.PrincipalId = sup.PrincipalId where sup.SystemUserId = @SystemUserId0 and POA.ObjectTypeCode = @ObjectTypeCode and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1;
insert into #ObjectsIds
select ActivityId from [ActivityPointer]  WITH (NOLOCK)
where OwnerId in
(select pem.PrincipalId from PrincipalEntityMap pem  WITH (NOLOCK) join SystemUserPrincipals sup  WITH (NOLOCK) on pem.PrincipalId = sup.PrincipalId where sup.SystemUserId = @UserIdOwnerCommand0 and pem.ObjectTypeCode = @OtcOwnerCommand0)
 
insert into #ObjectsIds
select ActivityId from [ActivityPointer]  WITH (NOLOCK)
where owningbusinessunit in
(select BusinessUnitId from SystemUserBusinessUnitEntityMap WITH (NOLOCK) where SystemUserId = @SystemUserId and ObjectTypeCode = @ObjectTypeCode)
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
 ActivityPointer as "activitypointer0" WITH (NOLOCK)  left outer join SystemUser as "activitypointerowningusersystemusersystemuserid" WITH (NOLOCK)  on ("activitypointer0".OwningUser  =  "activitypointerowningusersystemusersystemuserid".SystemUserId)
where
 (("activitypointer0".IsRegularActivity = @IsRegularActivity0) and ("activitypointer0".ActivityId in (select distinct ObjectId from #ObjectsIds))) order by
 "activitypointer0".ScheduledEnd asc
, "activitypointer0".ActivityId asc;
drop table #ObjectsIds 

ERMO = 2

This was considered the optimal setting in CRM 2011 pre-UR10.  This setting would be considered today when all users have visibility to a large amount of records, while being associated with a very small amount of Principals and Business Units.  This would be another edge scenario.
 
An example of a query when ERMO is set to 2 is below:
 
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
 ActivityPointer as "activitypointer0" WITH (NOLOCK)  left outer join SystemUser as "activitypointerowningusersystemusersystemuserid" WITH (NOLOCK)  on ("activitypointer0".OwningUser  =  "activitypointerowningusersystemusersystemuserid".SystemUserId)
where
 (("activitypointer0".IsRegularActivity = @IsRegularActivity0) and ("activitypointer0".OwnerId in (SELECT id from dbo.fn_GetGuidsFromString(@buIds0))
 or "activitypointer0".ActivityId in (select POA.ObjectId from PrincipalObjectAccess POA
 WITH (NOLOCK) where POA.PrincipalId in (@userPrincipalPOA00,@userPrincipalTeamPOA00,@userPrincipalTeamPOA1100,@userPrincipalTeamPOA2100,
userPrincipalTeamPOA3100,@userPrincipalTeamPOA4100,@userPrincipalTeamPOA538,@userPrincipalTeamPOA610,
@userPrincipalTeamPOA710,@userPrincipalTeamPOA810,@userPrincipalTeamPOA910,@userPrincipalTeamPOA1010,
@userPrincipalTeamPOA1110,@userPrincipalTeamPOA1210,@userPrincipalTeamPOA1310,@userPrincipalTeamPOA1410,
@userPrincipalTeamPOA1510,@userPrincipalTeamPOA1610,etc.etc.) and POA.ObjectTypeCode = @ObjectTypeCode0 and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1)
 or "activitypointer0".OwningBusinessUnit in (@buId00)
)) order by
 "activitypointer0".ScheduledEnd asc
, "activitypointer0".ActivityId asc

ERMO = 3

This is a rarely used, and scarcely documented setting that is generally meant to be implemented only for performance and functional comparisons.  It would be considered optimal in environments where SQL memory is concerning, and all table row counts are small.
 
An example of a query when ERMO is set to 3 is below:
 
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
 ActivityPointer as "activitypointer0" WITH (NOLOCK)  left outer join SystemUser as "activitypointerowningusersystemusersystemuserid" WITH (NOLOCK)  on ("activitypointer0".OwningUser  =  "activitypointerowningusersystemusersystemuserid".SystemUserId)
where
 (("activitypointer0".IsRegularActivity = @IsRegularActivity0) and ("activitypointer0".OwnerId in (select pem.PrincipalId from PrincipalEntityMap pem  WITH (NOLOCK) join SystemUserPrincipals sup  WITH (NOLOCK) on pem.PrincipalId = sup.PrincipalId where sup.SystemUserId = @UserIdOwnerCommand00 and pem.ObjectTypeCode = @OtcOwnerCommand00)
 or "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) or "activitypointer0".OwningBusinessUnit in (select BusinessUnitId from SystemUserBusinessUnitEntityMap WITH (NOLOCK) where SystemUserId = @SystemUserId0 and ObjectTypeCode = @ObjectTypeCode1))) order by
 "activitypointer0".ScheduledEnd asc
, "activitypointer0".ActivityId asc
 
 Next post in this blog series: RecordCountLimitToSwitchToCteSecuritySql.