query is very slow

eclare @RollupIds table (RollupId uniqueidentifier PRIMARY KEY CLUSTERED) insert into @RollupIds(RollupId) select RollupId from fn_RollupByAccount('26383cf7-fc79-e511-80e5-000c2934b2be', 0, 1) ;WITH "activitypointer0Security" as ( select OwnerId as "OwnerId", CreatedOn as "CreatedOn", StatusCode as "StatusCode", ModifiedOn as "ModifiedOn", RegardingObjectId as "RegardingObjectId", ScheduledStart as "ScheduledStart", ProcessId as "ProcessId", IsBilled as "IsBilled", TransactionCurrencyId as "TransactionCurrencyId", SenderMailboxId as "SenderMailboxId", ScheduledDurationMinutes as "ScheduledDurationMinutes", OwningUser as "OwningUser", OwningBusinessUnit as "OwningBusinessUnit", ModifiedOnBehalfBy as "ModifiedOnBehalfBy", ScheduledEnd as "ScheduledEnd", PostponeActivityProcessingUntil as "PostponeActivityProcessingUntil", CreatedBy as "CreatedBy", DeliveryPriorityCode as "DeliveryPriorityCode", OwningTeam as "OwningTeam", IsMapiPrivate as "IsMapiPrivate", TraversedPath as "TraversedPath", ActualEnd as "ActualEnd", ActualDurationMinutes as "ActualDurationMinutes", ServiceId as "ServiceId", CreatedOnBehalfBy as "CreatedOnBehalfBy", SeriesId as "SeriesId", ActivityId as "ActivityId", Description as "Description", LeftVoiceMail as "LeftVoiceMail", IsWorkflowCreated as "IsWorkflowCreated", SentOn as "SentOn", Community as "Community", ModifiedBy as "ModifiedBy", PriorityCode as "PriorityCode", StateCode as "StateCode", ActivityTypeCode as "ActivityTypeCode", UTCConversionTimeZoneCode as "UTCConversionTimeZoneCode", ActualStart as "ActualStart", InstanceTypeCode as "InstanceTypeCode", Subject as "Subject", DeliveryLastAttemptedOn as "DeliveryLastAttemptedOn", TimeZoneRuleVersionNumber as "TimeZoneRuleVersionNumber", StageId as "StageId", IsRegularActivity as "IsRegularActivity", ExchangeRate as "ExchangeRate", OwnerIdName as "OwnerIdName", OwnerIdYomiName as "OwnerIdYomiName", OwnerIdType as "OwnerIdType", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", TransactionCurrencyIdName as "TransactionCurrencyIdName", SenderMailboxIdName as "SenderMailboxIdName", ModifiedOnBehalfByYomiName as "ModifiedOnBehalfByYomiName", ModifiedOnBehalfByName as "ModifiedOnBehalfByName", CreatedByYomiName as "CreatedByYomiName", CreatedByName as "CreatedByName", ServiceIdName as "ServiceIdName", CreatedOnBehalfByYomiName as "CreatedOnBehalfByYomiName", CreatedOnBehalfByName as "CreatedOnBehalfByName", ModifiedByYomiName as "ModifiedByYomiName", ModifiedByName as "ModifiedByName" 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 = '7d1e8316-8505-e711-80f2-000c2934b2be' and POA.ObjectTypeCode = 4200 and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1) UNION select OwnerId as "OwnerId", CreatedOn as "CreatedOn", StatusCode as "StatusCode", ModifiedOn as "ModifiedOn", RegardingObjectId as "RegardingObjectId", ScheduledStart as "ScheduledStart", ProcessId as "ProcessId", IsBilled as "IsBilled", TransactionCurrencyId as "TransactionCurrencyId", SenderMailboxId as "SenderMailboxId", ScheduledDurationMinutes as "ScheduledDurationMinutes", OwningUser as "OwningUser", OwningBusinessUnit as "OwningBusinessUnit", ModifiedOnBehalfBy as "ModifiedOnBehalfBy", ScheduledEnd as "ScheduledEnd", PostponeActivityProcessingUntil as "PostponeActivityProcessingUntil", CreatedBy as "CreatedBy", DeliveryPriorityCode as "DeliveryPriorityCode", OwningTeam as "OwningTeam", IsMapiPrivate as "IsMapiPrivate", TraversedPath as "TraversedPath", ActualEnd as "ActualEnd", ActualDurationMinutes as "ActualDurationMinutes", ServiceId as "ServiceId", CreatedOnBehalfBy as "CreatedOnBehalfBy", SeriesId as "SeriesId", ActivityId as "ActivityId", Description as "Description", LeftVoiceMail as "LeftVoiceMail", IsWorkflowCreated as "IsWorkflowCreated", SentOn as "SentOn", Community as "Community", ModifiedBy as "ModifiedBy", PriorityCode as "PriorityCode", StateCode as "StateCode", ActivityTypeCode as "ActivityTypeCode", UTCConversionTimeZoneCode as "UTCConversionTimeZoneCode", ActualStart as "ActualStart", InstanceTypeCode as "InstanceTypeCode", Subject as "Subject", DeliveryLastAttemptedOn as "DeliveryLastAttemptedOn", TimeZoneRuleVersionNumber as "TimeZoneRuleVersionNumber", StageId as "StageId", IsRegularActivity as "IsRegularActivity", ExchangeRate as "ExchangeRate", OwnerIdName as "OwnerIdName", OwnerIdYomiName as "OwnerIdYomiName", OwnerIdType as "OwnerIdType", RegardingObjectIdYomiName as "RegardingObjectIdYomiName", RegardingObjectTypeCode as "RegardingObjectTypeCode", RegardingObjectIdName as "RegardingObjectIdName", TransactionCurrencyIdName as "TransactionCurrencyIdName", SenderMailboxIdName as "SenderMailboxIdName", ModifiedOnBehalfByYomiName as "ModifiedOnBehalfByYomiName", ModifiedOnBehalfByName as "ModifiedOnBehalfByName", CreatedByYomiName as "CreatedByYomiName", CreatedByName as "CreatedByName", ServiceIdName as "ServiceIdName", CreatedOnBehalfByYomiName as "CreatedOnBehalfByYomiName", CreatedOnBehalfByName as "CreatedOnBehalfByName", ModifiedByYomiName as "ModifiedByYomiName", ModifiedByName as "ModifiedByName" from [ActivityPointer] as "activitypointer0" where "activitypointer0".OwningBusinessUnit in ('b1c43a79-b7be-e411-80bc-005056aee586')

) select COUNT(*) as [#TotalRecordCount] from (select DISTINCT top 5001 "activitypointer0".OwnerId as "ownerid" , "activitypointer0".CreatedOn as "createdon" , "activitypointer0".StatusCode as "statuscode" , "activitypointer0".ModifiedOn as "modifiedon" , "activitypointer0".RegardingObjectId as "regardingobjectid" , "activitypointer0".ScheduledStart as "scheduledstart" , "activitypointer0".ProcessId as "processid" , "activitypointer0".IsBilled as "isbilled" , "activitypointer0".TransactionCurrencyId as "transactioncurrencyid" , "activitypointer0".SenderMailboxId as "sendermailboxid" , "activitypointer0".ScheduledDurationMinutes as "scheduleddurationminutes" , "activitypointer0".OwningUser as "owninguser" , "activitypointer0".OwningBusinessUnit as "owningbusinessunit" , "activitypointer0".ModifiedOnBehalfBy as "modifiedonbehalfby" , "activitypointer0".ScheduledEnd as "scheduledend" , "activitypointer0".PostponeActivityProcessingUntil as "postponeactivityprocessinguntil" , "activitypointer0".CreatedBy as "createdby" , "activitypointer0".DeliveryPriorityCode as "deliveryprioritycode" , "activitypointer0".OwningTeam as "owningteam" , "activitypointer0".IsMapiPrivate as "ismapiprivate" , "activitypointer0".TraversedPath as "traversedpath" , "activitypointer0".ActualEnd as "actualend" , "activitypointer0".ActualDurationMinutes as "actualdurationminutes" , "activitypointer0".ServiceId as "serviceid" , "activitypointer0".CreatedOnBehalfBy as "createdonbehalfby" , "activitypointer0".SeriesId as "seriesid" , "activitypointer0".ActivityId as "activityid" , "activitypointer0".Description as "description" , "activitypointer0".LeftVoiceMail as "leftvoicemail" , "activitypointer0".IsWorkflowCreated as "isworkflowcreated" , "activitypointer0".SentOn as "senton" , "activitypointer0".Community as "community" , "activitypointer0".ModifiedBy as "modifiedby" , "activitypointer0".PriorityCode as "prioritycode" , "activitypointer0".StateCode as "statecode" , "activitypointer0".ActivityTypeCode as "activitytypecode" , "activitypointer0".UTCConversionTimeZoneCode as "utcconversiontimezonecode" , "activitypointer0".ActualStart as "actualstart" , "activitypointer0".InstanceTypeCode as "instancetypecode" , "activitypointer0".Subject as "subject" , "activitypointer0".DeliveryLastAttemptedOn as "deliverylastattemptedon" , "activitypointer0".TimeZoneRuleVersionNumber as "timezoneruleversionnumber" , "activitypointer0".StageId as "stageid" , "activitypointer0".IsRegularActivity as "isregularactivity" , "activitypointer0".ExchangeRate as "exchangerate" , "activitypointer0".OwnerIdName as "owneridname" , "activitypointer0".OwnerIdYomiName as "owneridyominame" , "activitypointer0".OwnerIdType as "owneridtype" , "activitypointer0".RegardingObjectIdYomiName as "regardingobjectidyominame" , "activitypointer0".RegardingObjectTypeCode as "regardingobjecttypecode" , "activitypointer0".RegardingObjectIdName as "regardingobjectidname" , "activitypointer0".TransactionCurrencyIdName as "transactioncurrencyidname" , "activitypointer0".SenderMailboxIdName as "sendermailboxidname" , "activitypointer0".ModifiedOnBehalfByYomiName as "modifiedonbehalfbyyominame" , "activitypointer0".ModifiedOnBehalfByName as "modifiedonbehalfbyname" , "activitypointer0".CreatedByYomiName as "createdbyyominame" , "activitypointer0".CreatedByName as "createdbyname" , "activitypointer0".ServiceIdName as "serviceidname" , "activitypointer0".CreatedOnBehalfByYomiName as "createdonbehalfbyyominame" , "activitypointer0".CreatedOnBehalfByName as "createdonbehalfbyname" , "activitypointer0".ModifiedByYomiName as "modifiedbyyominame" , "activitypointer0".ModifiedByName as "modifiedbyname" from activitypointer0Security as "activitypointer0" WITH (NOLOCK)
where (("activitypointer0".ActivityId in (select activitypointer0.ActivityId from ActivityPointer as activitypointer0 WITH (NOLOCK) where exists (select RollupId from @RollupIds where RollupId = activitypointer0.ActivityId)) and ((("activitypointer0".ActivityTypeCode != 4401 or "activitypointer0".ActivityTypeCode is null) and ("activitypointer0".ActivityTypeCode != 4402 or "activitypointer0".ActivityTypeCode is null) and ("activitypointer0".ActivityTypeCode != 4406 or "activitypointer0".ActivityTypeCode is null)))))) as "#SubQuery"

avatar image By omranabd 0 asked Apr 10 at 03:36 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Apr 11 at 07:53 PM

This is Dynamic, yes? Is it possible you can collect an actual plan from within Plan Explorer, and not bother anonymizing it? (Not sure what you're hiding, we're all familiar with the Dynamics schema, but anonymizing disables other, important features.)

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x650
x463
x119

asked: Apr 10 at 03:36 PM

Seen: 59 times

Last Updated: Apr 11 at 07:53 PM