My 8.2 on-prem deployment has 25 orgs but only 14 orgs use email router. I monitor sql performance with sp_whoisactive and one of the queries that I see a lot is:
<?query --
select
top 5 "email0".Subject as "subject"
, "email0".Description as "description"
, "email0".PriorityCode as "prioritycode"
, "email0".ActivityId as "activityid"
, "email0".ModifiedOn as "modifiedon"
, "email0".StateCode as "statecode"
, "email0".StatusCode as "statuscode"
, "email0".DeliveryAttempts as "deliveryattempts"
, "email0".AttachmentCount as "attachmentcount"
, convert(bigint, "email0".VersionNumber) as "versionnumber"
, N'' as "safedescription"
from
Email as "email0" WITH (NOLOCK) join ActivityParty as "activityparty1" WITH (NOLOCK) on ("email0".ActivityId = "activityparty1".ActivityId and (((("activityparty1".ParticipationTypeMask = @ParticipationTypeMask0 and ("activityparty1".PartyId in (@PartyId0
, @PartyId1
, @PartyId2
, @PartyId3
, @PartyId4)))))))
where
(("email0".StateCode = @StateCode0 and ("email0".StatusCode != @StatusCode0 or "email0".StatusCode is null) and "email0".DirectionCode = @DirectionCode0 and (((((("email0".DeliveryAttempts = @DeliveryAttempts0)))))))) order by
"email0".ActualEnd asc
--?>
My first question is, is this coming from email router? Or is it specifically a workflow that sends out an email? Regardless of which, why and where is the query choosing to select the top 5? When looking at the execution plan, there is no suggested missing index that needs to be created but I'd be willing to if needed. Most of the cost in the execution plan goes to a non-clustered index seek and clustered key lookup to the ActivityPointerBase table. Any insight is appreciated.
