Robb Hill
asked on
SQL Improvement ( Speed)
I have a query that after a data increase on the table went from executing in less than 10 seconds to upward 50+ seconds...Please help me address.
In this case I executing the procedure as follows:
exec caDoc_CRM.dbo.spGetTaskLis tForActivi tyPage @SiteCode='Root', @ClientBaseId=3, @OnlyTaskWithClients=0
Here is the excecution plan:
In this case I executing the procedure as follows:
exec caDoc_CRM.dbo.spGetTaskLis
USE [cadoc_crm]
GO
/****** Object: StoredProcedure [dbo].[spGetTaskListForActivityPage] Script Date: 2/9/2017 8:18:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[spGetTaskListForActivityPage]
@SiteCode varchar(20), @ClientBaseId int, @TaskId int = null, @ClientId int = null, @ContactId int = null, @OnlyTaskWithClients bit = 0
as
if IsNull(@TaskId, -1) <= 0
select @TaskId = null
if IsNull(@ClientId, -1) <= 0
select @ClientId = null
if IsNull(@ContactId, -1) <= 0
select @ContactId = null
if object_id('tempdb.dbo.#tmpTaskIds') is not null
drop table #tmpTaskIds
create table #tmpTaskIds(nTaskId int)
create index tmpTaskIds_nTaskId on #tmpTaskIds(nTaskId)
insert into #tmpTaskIds
select nTaskId
from caDoc_CRM.dbo.tTask
where ((cSiteCode = @SiteCode and lCompleted = 0) or (@TaskId is not null and nTaskId = @TaskId))
declare @IdTable table(TaskId int, ClientId int)
insert into @IdTable
select tc.nTaskId, tc.nClientId
from caDoc_CRM.dbo.tTaskXClient tc
inner join #tmpTaskIds t on tc.nTaskId = t.nTaskId
where @ClientId is null or (@ClientId is not null and nClientId = @ClientId)
insert into @IdTable
select distinct tc.nTaskId, cli.nId
from caDoc_CRM.dbo.tTaskXContact tc
inner join caDoc_CRM.dbo.tContact con on tc.nContactId = con.nId
and (@ContactId is null or (@ContactId is not null and con.nId = @ContactId))
inner join caDoc_CRM.dbo.tClient cli on con.nIdClient = cli.nId
and (@ClientId is null or (@ClientId is not null and cli.nId = @ClientId))
inner join #tmpTaskIds t on tc.nTaskId = t.nTaskId
where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)
delete from @IdTable
where ClientId = @ClientBaseId
and TaskId in (select TaskId from @IdTable where ClientId != @ClientBaseId)
if object_id('tempdb.dbo.#tmpTaskIds') is not null
drop table #tmpTaskIds
if object_id('tempdb.dbo.#tmpTaskResults') is not null
drop table #tmpTaskResults
select distinct t.nTaskID
, t.cTask cTask
, ISNULL(c.cName, '[unassigned]') cClientName
, ISNULL(c.nid, 0) nId
into #tmpTaskResults
from caDoc_CRM.dbo.tTask t
left join @IdTable tc on t.nTaskId = tc.TaskId
left join caDoc_CRM.dbo.tClient c on tc.ClientId = c.nID
order by 3, 2
if @OnlyTaskWithClients = 1
delete from #tmpTaskResults where nId <= 0
select * from #tmpTaskResults
Here is the excecution plan:
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.3.1" Build="12.0.5203.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="17" StatementEstRows="15607.9" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="2.75326" StatementText="insert into #tmpTaskIds
 select nTaskId
 from caDoc_CRM.dbo.tTask 
 where ((cSiteCode = @SiteCode and lCompleted = 0) or (@TaskId is not null and nTaskId = @TaskId))" StatementType="INSERT" QueryHash="0xAA97472805FAEE04" QueryPlanHash="0x2EB0DA5521728196" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" MemoryGrant="2336" CachedPlanSize="24" CompileTime="4" CompileCPU="4" CompileMemory="376">
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="2336" RequiredMemory="512" DesiredMemory="2336" RequestedMemory="2336" GrantWaitTime="0" GrantedMemory="2336" MaxUsedMemory="896" MaxQueryMemory="3004400" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10254952" />
<RelOp AvgRowSize="9" EstimateCPU="0.0156079" EstimateIO="0.247837" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15607.9" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Index Insert" EstimatedTotalSubtreeCost="2.75326">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="182" ActualCPUms="182" ActualScans="0" ActualLogicalReads="31258" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update DMLRequestSort="true">
<Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpTaskIds]" Index="[tmpTaskIds_nTaskId]" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[Bmk10001008] = [Bmk1000],[nTaskId1009] = [#tmpTaskIds].[nTaskId]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Column="Bmk10001008" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Bmk1000" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Column="nTaskId1009" />
<ScalarOperator>
<Identifier>
<ColumnReference Table="[#tmpTaskIds]" Column="nTaskId" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="19" EstimateCPU="0.998045" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15607.9" LogicalOp="Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="2.48982">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Table="[#tmpTaskIds]" Column="nTaskId" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="120" ActualCPUms="120" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Table="[#tmpTaskIds]" Column="nTaskId" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Bmk1000" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="19" EstimateCPU="0.0156079" EstimateIO="0.14485" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15607.9" LogicalOp="Insert" NodeId="2" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="1.48051">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Table="[#tmpTaskIds]" Column="nTaskId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="96" ActualCPUms="95" ActualScans="0" ActualLogicalReads="15568" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpTaskIds]" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[#tmpTaskIds].[nTaskId] = [cadoc_crm].[dbo].[tTask].[nTaskID]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Table="[#tmpTaskIds]" Column="nTaskId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="nTaskID" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="11" EstimateCPU="0.00156079" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15607.9" LogicalOp="Top" NodeId="3" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.32005">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="nTaskID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="38" ActualCPUms="38" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="20" EstimateCPU="0.0600146" EstimateIO="1.17794" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15607.9" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.23795" TableCardinality="54416">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="nTaskID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualRowsRead="54416" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="35" ActualCPUms="35" ActualScans="1" ActualLogicalReads="1592" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="nTaskID" />
</DefinedValue>
</DefinedValues>
<Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Index="[PK_tTask]" IndexKind="Clustered" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="[cadoc_crm].[dbo].[tTask].[cSiteCode]=[@SiteCode] AND [cadoc_crm].[dbo].[tTask].[lCompleted]=(0) OR [@TaskId] IS NOT NULL AND [cadoc_crm].[dbo].[tTask].[nTaskID]=[@TaskId]">
<Logical Operation="OR">
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="cSiteCode" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@SiteCode" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="lCompleted" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1005">
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@TaskId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="nTaskID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@TaskId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
</Sort>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="@TaskId" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
<ColumnReference Column="@SiteCode" ParameterCompiledValue="'Root'" ParameterRuntimeValue="'Root'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="19" StatementEstRows="15542.9" StatementId="2" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="1.14791" StatementText="insert into @IdTable
 select tc.nTaskId, tc.nClientId
 from caDoc_CRM.dbo.tTaskXClient tc
 inner join #tmpTaskIds t on tc.nTaskId = t.nTaskId
 where @ClientId is null or (@ClientId is not null and nClientId = @ClientId)" StatementType="INSERT" QueryHash="0xFE2D144169344B3A" QueryPlanHash="0x870FFFDB79F7FBB0" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" MemoryGrant="4544" CachedPlanSize="40" CompileTime="16" CompileCPU="16" CompileMemory="368">
<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="4544" RequiredMemory="1024" DesiredMemory="4544" RequestedMemory="4544" GrantWaitTime="0" GrantedMemory="4544" MaxUsedMemory="1024" MaxQueryMemory="3004400" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10254328" />
<RelOp AvgRowSize="9" EstimateCPU="0.0155429" EstimateIO="0.19554" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15542.9" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="1.14791">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15539" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="72" ActualCPUms="72" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Table="[@IdTable]" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[TaskId] = [cadoc_crm].[dbo].[tTaskXClient].[nTaskID] as [tc].[nTaskID],[ClientId] = [cadoc_crm].[dbo].[tTaskXClient].[nClientID] as [tc].[nClientID]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Column="TaskId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Column="ClientId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="15" EstimateCPU="0.00155429" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15542.9" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.936827">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15539" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="58" ActualCPUms="58" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="15" EstimateCPU="0.606846" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15542.9" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.935273">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15539" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="56" ActualCPUms="56" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="#tmpTaskIds.[nTaskId] as [t].[nTaskId]=[cadoc_crm].[dbo].[tTaskXClient].[nTaskID] as [tc].[nTaskID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="11" EstimateCPU="0.0172554" EstimateIO="0.0209028" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15544" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0381582" TableCardinality="15544">
<OutputList>
<ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualRowsRead="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="4" ActualCPUms="4" ActualScans="1" ActualLogicalReads="25" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpTaskIds]" Alias="[t]" Storage="RowStore" />
</TableScan>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0600036" EstimateIO="0.182384" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54406" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.242388" TableCardinality="54406">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="54406" ActualRowsRead="54406" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="22" ActualCPUms="22" ActualScans="1" ActualLogicalReads="245" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
</DefinedValue>
</DefinedValues>
<Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Index="[PK_tTaskXClient]" Alias="[tc]" IndexKind="Clustered" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="[@ClientId] IS NULL OR [@ClientId] IS NOT NULL AND [cadoc_crm].[dbo].[tTaskXClient].[nClientID] as [tc].[nClientID]=[@ClientId]">
<Logical Operation="OR">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1006">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1007">
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="@ClientId" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="20" StatementEstRows="24196.4" StatementId="3" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="2.64656" StatementText="insert into @IdTable
 select distinct tc.nTaskId, cli.nId 
 from caDoc_CRM.dbo.tTaskXContact tc
 inner join caDoc_CRM.dbo.tContact con on tc.nContactId = con.nId
 and (@ContactId is null or (@ContactId is not null and con.nId = @ContactId))
 inner join caDoc_CRM.dbo.tClient cli on con.nIdClient = cli.nId
 and (@ClientId is null or (@ClientId is not null and cli.nId = @ClientId))
 inner join #tmpTaskIds t on tc.nTaskId = t.nTaskId
 where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)" StatementType="INSERT" QueryHash="0xE8ADD78618D62FAF" QueryPlanHash="0xA4E3A315B761F81B" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" MemoryGrant="8992" CachedPlanSize="88" CompileTime="24" CompileCPU="24" CompileMemory="1288">
<MemoryGrantInfo SerialRequiredMemory="2560" SerialDesiredMemory="8992" RequiredMemory="2560" DesiredMemory="8992" RequestedMemory="8992" GrantWaitTime="0" GrantedMemory="8992" MaxUsedMemory="2288" MaxQueryMemory="3004400" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10254328" />
<RelOp AvgRowSize="9" EstimateCPU="0.0241964" EstimateIO="0.3026" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="24196.4" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="2.64656">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15543" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="140" ActualCPUms="140" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Table="[@IdTable]" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[TaskId] = [cadoc_crm].[dbo].[tTaskXContact].[nTaskID] as [tc].[nTaskID],[ClientId] = [cadoc_crm].[dbo].[tClient].[nid] as [cli].[nid]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Column="TaskId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Column="ClientId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="15" EstimateCPU="0.00241964" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="24196.4" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="2.31977">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15543" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="132" ActualCPUms="132" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="15" EstimateCPU="0.660025" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="24196.4" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.31735">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</OutputList>
<MemoryFractions Input="0.415423" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15543" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="131" ActualCPUms="131" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[cadoc_crm].[dbo].[tTaskXContact].[nTaskID] as [tc].[nTaskID] = [cadoc_crm].[dbo].[tTaskXContact].[nTaskID] as [tc].[nTaskID] AND [cadoc_crm].[dbo].[tClient].[nid] as [cli].[nid] = [cadoc_crm].[dbo].[tClient].[nid] as [cli].[nid]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="15" EstimateCPU="0.798054" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="24196.4" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.65732">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</OutputList>
<MemoryFractions Input="0.749574" Output="0.547264" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="24238" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="110" ActualCPUms="110" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[cadoc_crm].[dbo].[tTaskXContact].[nTaskID] as [tc].[nTaskID]=#tmpTaskIds.[nTaskId] as [t].[nTaskId]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="11" EstimateCPU="0.0172554" EstimateIO="0.0209028" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15544" LogicalOp="Table Scan" NodeId="4" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0381582" TableCardinality="15544">
<OutputList>
<ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualRowsRead="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="4" ActualCPUms="4" ActualScans="1" ActualLogicalReads="25" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpTaskIds]" Alias="[t]" Storage="RowStore" />
</TableScan>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.190786" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="82213" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.821105">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="82213" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="61" ActualCPUms="61" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nContactID" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[cadoc_crm].[dbo].[tTaskXContact].[nContactID] as [tc].[nContactID]=[cadoc_crm].[dbo].[tContact].[nid] as [con].[nid]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nContactID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="15" EstimateCPU="0.035399" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2056" LogicalOp="Sort" NodeId="6" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.358081">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</OutputList>
<MemoryFractions Input="0.0511073" Output="0.0373134" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1862" ActualEndOfScans="0" ActualExecutions="1" ActualElapsedms="23" ActualCPUms="23" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="15" EstimateCPU="0.170723" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2056" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.311421">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</OutputList>
<MemoryFractions Input="0.250426" Output="0.199319" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2056" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="22" ActualCPUms="22" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nidClient" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</HashKeysProbe>
<RelOp AvgRowSize="15" EstimateCPU="0.0024186" EstimateIO="0.00608796" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2056" LogicalOp="Index Scan" NodeId="8" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00850656" TableCardinality="2056">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nidClient" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2056" ActualRowsRead="2056" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="2" ActualCPUms="2" ActualScans="1" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nidClient" />
</DefinedValue>
</DefinedValues>
<Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Index="[IX_tContact_nidClient]" Alias="[con]" IndexKind="NonClustered" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="[@ContactId] IS NULL OR [@ContactId] IS NOT NULL AND [cadoc_crm].[dbo].[tContact].[nid] as [con].[nid]=[@ContactId]">
<Logical Operation="OR">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1008">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ContactId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1009">
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ContactId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ContactId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0308096" EstimateIO="0.0527546" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="27866" LogicalOp="Index Scan" NodeId="9" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0835642" TableCardinality="27866">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="27866" ActualRowsRead="27866" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="11" ActualCPUms="11" ActualScans="1" ActualLogicalReads="70" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</DefinedValue>
</DefinedValues>
<Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Index="[IX_cUDF3]" Alias="[cli]" IndexKind="NonClustered" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="([@ClientId] IS NULL OR [@ClientId] IS NOT NULL AND [cadoc_crm].[dbo].[tClient].[nid] as [cli].[nid]=[@ClientId]) AND ([@ClientId] IS NULL OR [@ClientId] IS NOT NULL AND [cadoc_crm].[dbo].[tClient].[nid] as [cli].[nid]=[@ClientId])">
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1010">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1011">
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1012">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1013">
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0905913" EstimateIO="0.181644" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="82213" LogicalOp="Index Scan" NodeId="11" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.272235" TableCardinality="82213">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nContactID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="82213" ActualRowsRead="82213" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="24" ActualCPUms="24" ActualScans="1" ActualLogicalReads="244" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nContactID" />
</DefinedValue>
</DefinedValues>
<Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Index="[IX_tTaskXContact_nContactID_lPrimary]" Alias="[tc]" IndexKind="NonClustered" Storage="RowStore" />
</IndexScan>
</RelOp>
</Merge>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="@ClientId" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
<ColumnReference Column="@ContactId" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="21" StatementEstRows="1" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0297982" StatementText="delete from @IdTable
 where ClientId = @ClientBaseId
 and TaskId in (select TaskId from @IdTable where ClientId != @ClientBaseId)" StatementType="DELETE" QueryHash="0xC2480033B6C0C59A" QueryPlanHash="0x14853FB2BB609876" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="24" CompileTime="11" CompileCPU="11" CompileMemory="496">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10254952" />
<RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Delete" NodeId="0" Parallel="false" PhysicalOp="Table Delete" EstimatedTotalSubtreeCost="0.0297982">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15538" ActualRowsRead="15538" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="53338" ActualCPUms="53333" ActualScans="0" ActualLogicalReads="15538" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Table="[@IdTable]" Storage="RowStore" />
<RelOp AvgRowSize="15" EstimateCPU="0.00010046" EstimateIO="0.013125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Eager Spool" NodeId="1" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0197972">
<OutputList>
<ColumnReference Column="Bmk1000" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="15538" ActualRowsRead="15538" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="53276" ActualCPUms="53270" ActualScans="1" ActualLogicalReads="31366" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool>
<RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657174">
<OutputList>
<ColumnReference Column="Bmk1000" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15538" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="53150" ActualCPUms="53145" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="15" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657164">
<OutputList>
<ColumnReference Column="Bmk1000" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15538" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="53144" ActualCPUms="53139" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[TaskId]=[TaskId]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="TaskId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="TaskId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="23" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="4" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Column="TaskId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="15540" ActualRowsRead="31082" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="13" ActualCPUms="13" ActualScans="1" ActualLogicalReads="67" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="true" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="TaskId" />
</DefinedValue>
</DefinedValues>
<Object Table="[@IdTable]" TableReferenceId="1" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="[ClientId]=[@ClientBaseId]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ClientId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientBaseId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</TableScan>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="5" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
<OutputList>
<ColumnReference Column="TaskId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="120768792" ActualRowsRead="120799872" ActualEndOfScans="2" ActualExecutions="15540" ActualElapsedms="42020" ActualCPUms="42020" ActualScans="1" ActualLogicalReads="277130" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="TaskId" />
</DefinedValue>
</DefinedValues>
<Object Table="[@IdTable]" TableReferenceId="2" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="[ClientId]<>[@ClientBaseId]">
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ClientId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ClientBaseId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</TableScan>
</RelOp>
</NestedLoops>
</RelOp>
</Top>
</RelOp>
</Spool>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="@ClientBaseId" ParameterCompiledValue="(3)" ParameterRuntimeValue="(3)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="28" StatementEstRows="54416" StatementId="5" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="10.3282" StatementText="select distinct t.nTaskID
 , t.cTask cTask
 , ISNULL(c.cName, '[unassigned]') cClientName
 , ISNULL(c.nid, 0) nId
 into #tmpTaskResults
 from caDoc_CRM.dbo.tTask t
 left join @IdTable tc on t.nTaskId = tc.TaskId 
 left join caDoc_CRM.dbo.tClient c on tc.ClientId = c.nID" StatementType="SELECT INTO" QueryHash="0x74727E7A6C753C8B" QueryPlanHash="0x38D001F58E23B1D4" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="8" MemoryGrant="29000" CachedPlanSize="96" CompileTime="11" CompileCPU="11" CompileMemory="704">
<ThreadStat Branches="2" UsedThreads="16">
<ThreadReservation NodeId="0" ReservedThreads="16" />
</ThreadStat>
<MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="16776" RequiredMemory="13704" DesiredMemory="29000" RequestedMemory="29000" GrantWaitTime="0" GrantedMemory="29000" MaxUsedMemory="12696" MaxQueryMemory="3004400" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10254952" />
<RelOp AvgRowSize="9" EstimateCPU="0.054416" EstimateIO="8.35135" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="10.3282">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="54416" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="311" ActualCPUms="69" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Table="[#tmpTaskResults]" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[#tmpTaskResults].[nTaskID] = [cadoc_crm].[dbo].[tTask].[nTaskID] as [t].[nTaskID],[#tmpTaskResults].[cTask] = [cadoc_crm].[dbo].[tTask].[cTask] as [t].[cTask],[#tmpTaskResults].[cClientName] = [Expr1007],[#tmpTaskResults].[nId] = [Expr1008]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Table="[#tmpTaskResults]" Column="nTaskID" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[#tmpTaskResults]" Column="cTask" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[#tmpTaskResults]" Column="cClientName" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[#tmpTaskResults]" Column="nId" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1008" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="186" EstimateCPU="0.0054416" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.9224">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1008" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="54416" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="274" ActualCPUms="33" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="186" EstimateCPU="0.237968" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Gather Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.91696">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1008" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="54416" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="268" ActualCPUms="27" />
</RunTimeInformation>
<Parallelism>
<RelOp AvgRowSize="186" EstimateCPU="0.982352" EstimateIO="0.00281532" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Distinct Sort" NodeId="3" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1.67899">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1008" />
</OutputList>
<MemoryFractions Input="0.985827" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="5" ActualRebinds="1" ActualRewinds="0" ActualRows="6850" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="74" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="7" ActualRebinds="1" ActualRewinds="0" ActualRows="6764" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="75" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="8" ActualRebinds="1" ActualRewinds="0" ActualRows="6786" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="74" ActualCPUms="9" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="6885" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="74" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="6" ActualRebinds="1" ActualRewinds="0" ActualRows="6765" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="75" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="6862" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="74" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="6812" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="75" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="6692" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="74" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="163" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1007" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1008" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="186" EstimateCPU="0.277484" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Repartition Streams" NodeId="4" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.693826">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1008" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="7" ActualRows="6764" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="1" />
<RunTimeCountersPerThread Thread="5" ActualRows="6850" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="6787" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="0" />
<RunTimeCountersPerThread Thread="6" ActualRows="6766" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="66" ActualCPUms="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="6813" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="0" />
<RunTimeCountersPerThread Thread="3" ActualRows="6885" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="0" />
<RunTimeCountersPerThread Thread="2" ActualRows="6862" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="6692" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="163" ActualCPUms="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1008" />
</PartitionColumns>
<RelOp AvgRowSize="186" EstimateCPU="0.0013604" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Compute Scalar" NodeId="5" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.416342">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1008" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="isnull([cadoc_crm].[dbo].[tClient].[cName] as [c].[cName],'[unassigned]')">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'[unassigned]'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="isnull([cadoc_crm].[dbo].[tClient].[nid] as [c].[nid],(0))">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="168" EstimateCPU="0.166219" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Right Outer Join" NodeId="6" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.414982">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
</OutputList>
<Warnings>
<SpillToTempDb SpillLevel="1" SpilledThreadCount="8" />
<HashSpillDetails GrantedMemoryKb="23040" UsedMemoryKb="9216" WritesToTempDb="512" ReadsFromTempDb="512" />
</Warnings>
<MemoryFractions Input="1" Output="0.0141732" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="5" ActualRows="4890" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="225" ActualCPUms="29" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="4" ActualRows="7954" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="219" ActualCPUms="34" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="7" ActualRows="7937" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="216" ActualCPUms="32" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="3" ActualRows="6842" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="222" ActualCPUms="36" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="6" ActualRows="6450" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="222" ActualCPUms="31" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="2" ActualRows="6167" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="223" ActualCPUms="35" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="1" ActualRows="6648" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="221" ActualCPUms="31" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="8" ActualRows="7531" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="223" ActualCPUms="32" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[cadoc_crm].[dbo].[tTask].[nTaskID] as [t].[nTaskID]=@IdTable.[TaskId] as [tc].[TaskId]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="41" EstimateCPU="0.0285049" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Distribute Streams" NodeId="7" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0350753">
<OutputList>
<ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="5" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="145" ActualCPUms="2" />
<RunTimeCountersPerThread Thread="4" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="144" ActualCPUms="2" />
<RunTimeCountersPerThread Thread="7" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="145" ActualCPUms="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="142" ActualCPUms="2" />
<RunTimeCountersPerThread Thread="6" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="146" ActualCPUms="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="142" ActualCPUms="2" />
<RunTimeCountersPerThread Thread="1" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="145" ActualCPUms="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="147" ActualCPUms="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Broadcast">
<RelOp AvgRowSize="41" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="8" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
<OutputList>
<ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="97" ActualCPUms="90" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Table="@IdTable" Alias="[tc]" Column="ClientId" />
</OuterReferences>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="9" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
<OutputList>
<ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
<ColumnReference Table="@IdTable" Alias="[tc]" Column="ClientId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="15544" ActualRowsRead="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="8" ActualCPUms="8" ActualScans="1" ActualLogicalReads="38" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Table="@IdTable" Alias="[tc]" Column="ClientId" />
</DefinedValue>
</DefinedValues>
<Object Table="[@IdTable]" Alias="[tc]" Storage="RowStore" />
</TableScan>
</RelOp>
<RelOp AvgRowSize="37" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="27866">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="15544" ActualRowsRead="15544" Batches="0" ActualEndOfScans="0" ActualExecutions="15544" ActualExecutionMode="Row" ActualElapsedms="71" ActualCPUms="64" ActualScans="0" ActualLogicalReads="46632" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
</DefinedValue>
</DefinedValues>
<Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Index="[PK_tClient]" Alias="[c]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="@IdTable.[ClientId] as [tc].[ClientId]">
<Identifier>
<ColumnReference Table="@IdTable" Alias="[tc]" Column="ClientId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="140" EstimateCPU="0.0150037" EstimateIO="0.198681" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Index Scan" NodeId="11" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.213684" TableCardinality="54416">
<OutputList>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="5" ActualRows="4890" ActualRowsRead="4890" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="19" ActualCPUms="2" ActualScans="1" ActualLogicalReads="96" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="4" ActualRows="7954" ActualRowsRead="7954" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="22" ActualCPUms="4" ActualScans="1" ActualLogicalReads="156" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="7" ActualRows="7937" ActualRowsRead="7937" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="21" ActualCPUms="4" ActualScans="1" ActualLogicalReads="152" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="3" ActualRows="6842" ActualRowsRead="6842" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="21" ActualCPUms="3" ActualScans="1" ActualLogicalReads="132" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="6" ActualRows="6450" ActualRowsRead="6450" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="21" ActualCPUms="3" ActualScans="1" ActualLogicalReads="124" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="2" ActualRows="6166" ActualRowsRead="6166" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="21" ActualCPUms="3" ActualScans="1" ActualLogicalReads="120" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="1" ActualRows="6646" ActualRowsRead="6646" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="20" ActualCPUms="3" ActualScans="1" ActualLogicalReads="131" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="8" ActualRows="7531" ActualRowsRead="7531" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="23" ActualCPUms="4" ActualScans="1" ActualLogicalReads="148" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
</DefinedValue>
</DefinedValues>
<Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Index="[IX_cTask]" Alias="[t]" IndexKind="NonClustered" Storage="RowStore" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
</Parallelism>
</RelOp>
</Sort>
</RelOp>
</Parallelism>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="32" StatementEstRows="54416" StatementId="6" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.340177" StatementText="select * from #tmpTaskResults" StatementType="SELECT" QueryHash="0x7E910497C022928A" QueryPlanHash="0x2DB185700FE9C196" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="72">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10251024" />
<RelOp AvgRowSize="186" EstimateCPU="0.0599361" EstimateIO="0.280241" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Table Scan" NodeId="0" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.340177" TableCardinality="54416">
<OutputList>
<ColumnReference Table="[#tmpTaskResults]" Column="nTaskID" />
<ColumnReference Table="[#tmpTaskResults]" Column="cTask" />
<ColumnReference Table="[#tmpTaskResults]" Column="cClientName" />
<ColumnReference Table="[#tmpTaskResults]" Column="nId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="54416" ActualRowsRead="54416" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="35" ActualCPUms="35" ActualScans="1" ActualLogicalReads="376" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Table="[#tmpTaskResults]" Column="nTaskID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Table="[#tmpTaskResults]" Column="cTask" />
</DefinedValue>
<DefinedValue>
<ColumnReference Table="[#tmpTaskResults]" Column="cClientName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Table="[#tmpTaskResults]" Column="nId" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpTaskResults]" Storage="RowStore" />
</TableScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
ASKER
it seems query 5 is about 67 % of the batch. thats an insert into a temp that is joining a table to a temp table.
i think the issue is here.
no idea how to speed up that structure.
i think the issue is here.
no idea how to speed up that structure.
ASKER
no code changes, no index changes, no missing indexes in dmv
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pawan..what were your thoughts to speed this up?
ASKER
also if i change the null statements does that provide same logic
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok i will test now. sounds good. hopefully! will respond in 30. minutes
ASKER
Pawan:
That just went from almost a mintute to 3 seconds...Awesome.
To Primoz's point earlier..should we also incorporate the idea here:
Other stuff that may help is reducing the amount of ORs used so try something like:
from
where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)
into
where cli.nId = ISNULL(@ClientId, cli.nId)
will this give same output or potentially have unexpected behavior.
Thanks again!!!!!!
That just went from almost a mintute to 3 seconds...Awesome.
To Primoz's point earlier..should we also incorporate the idea here:
Other stuff that may help is reducing the amount of ORs used so try something like:
from
where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)
into
where cli.nId = ISNULL(@ClientId, cli.nId)
will this give same output or potentially have unexpected behavior.
Thanks again!!!!!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok thanks again.
Glad to help!
ASKER
You are a life saver!!!! Thanks Pawan Kumar and thanks for explaining. Its good to understand the pros and the cons. I have inherited a very old database and there is alot of outdated queries that are just now having noticable performance issues.
Other stuff that may help is reducing the amount of ORs used so try something like:
from
where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)
into
where cli.nId = ISNULL(@ClientId, cli.nId)
from
where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)
into
where cli.nId = ISNULL(@ClientId, cli.nId)
I'm afraid this is NOT a good performance tuning objective.
Adding ISNULL() does NOT improve performance, it potentially makes it much worse because it forces table scans instead of access to indexes
as a general rule AVOID using functions in joins and where clause .... ISNULL() is a function
If your code is not doing something dodgy like loop (which it doesn't appear to), it is almost always related to indexes. I can't read your execution plan, but if you find the bottlenecks, add indexes to the relevant tables - this includes your temporary tables. Basically any 'where' clauses should be indexed (depending on the amount of data).
Other stuff that may help is reducing the amount of ORs used so try something like:
from
where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)
into
where cli.nId = ISNULL(@ClientId, cli.nId)