Hi,
I am seeing a huge amount of SQL activity caused by a query from the CRM email router. This query is one of the highest CPU / IO consumers on the database. I can't find any posts from other CRM users relating to this issue so I assume it is not common. The tables read by this query are very large as we are running large marketing campaigns (500K+ are quite common).
Does anyone else see this query high in their sys.dm_exec_query_stats?
ActivityPointerBase 26,837,481 rows
ActivityPartyBase 113,692,839 rows
EmailBase 19,593,214 rows
)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"
from Email as "email0" (NOLOCK)
join ActivityParty as "activityparty1" (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 as
