A deadlock issue that killing me dead

  • Hi all,

    I'm experiencing a rather annoying deadlock issue with a merge from a temp table.

    What the dev's are trying:

    There is an application that writes to a temp table(#temptable). Each thread accesses its own temp table so there could be up to 8 temp tables(#temptable2,#temptable3,etc) depending on the application load.

    Once the workload is complete in the temp table the application issues a merge into a collective table (sent).

    What is happening:

    The merge is taking an excessive but varying amount of time, ranging from 5ms to 45 seconds.

    At times a deadlock is detected. I added traceflag 1222 and the following log is returned:

    2016-04-13 08:20:55.42 spid29s deadlock-list

    2016-04-13 08:20:55.42 spid29s deadlock victim=process73e3474c8

    2016-04-13 08:20:55.42 spid29s process-list

    2016-04-13 08:20:55.42 spid29s process id=process73e3474c8 taskpriority=0 logused=0 waitresource=KEY: 51:72057594047168512 (d2130c1d3fa3) waittime=3292 ownerId=8370217189 transactionname=MERGE lasttranstarted=2016-04-13T08:20:52.080 XDES=0x73af39950 lockMode=U schedulerid=14 kpid=8588 status=suspended spid=1384 sbid=1 ecid=0 priority=0 trancount=1 lastbatchstarted=2016-04-13T08:20:52.070 lastbatchcompleted=2016-04-13T08:20:52.070 clientapp=GWAPP hostname=server03 hostpid=4588 loginname=nope isolationlevel=read committed (2) xactid=8370217189 currentdb=51 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056

    2016-04-13 08:20:55.42 spid29s executionStack

    2016-04-13 08:20:55.42 spid29s frame procname=adhoc line=1 sqlhandle=0x0200000031db2209ea439da5478a27626f12ff17095fb790

    2016-04-13 08:20:55.42 spid29s MERGE sent WITH (ROWLOCK) AS target USING(SELECT se_ref, se_success, se_smsc_status, se_time_delivered, se_messageref FROM #MyTempTable_2 WITH (ROWLOCK)) AS source(se_ref, se_success, se_smsc_status, se_time_delivered, se_messageref) ON(target.se_ref = source.se_ref) WHEN MATCHED THEN UPDATE SET se_success = COALESCE(source.se_success,target.se_success), se_smsc_status = COALESCE(source.se_smsc_status, target.se_smsc_status), se_time_delivered = COALESCE(source.se_time_delivered,target.se_time_delivered) , se_messageref = COALESCE(source.se_messageref, target.se_messageref)OUTPUT Inserted.se_ref;

    2016-04-13 08:20:55.42 spid29s inputbuf

    2016-04-13 08:20:55.42 spid29s MERGE sent WITH (ROWLOCK) AS target USING(SELECT se_ref, se_success, se_smsc_status, se_time_delivered, se_messageref FROM #MyTempTable_2 WITH (ROWLOCK)) AS source(se_ref, se_success, se_smsc_status, se_time_delivered, se_messageref) ON(target.se_ref = source.se_ref) WHEN MATCHED THEN UPDATE SET se_success = COALESCE(source.se_success,target.se_success), se_smsc_status = COALESCE(source.se_smsc_status, target.se_smsc_status), se_time_delivered = COALESCE(source.se_time_delivered,target.se_time_delivered) , se_messageref = COALESCE(source.se_messageref, target.se_messageref)OUTPUT Inserted.se_ref;

    2016-04-13 08:20:55.43 spid29s process id=process53d8e08 taskpriority=0 logused=0 waitresource=KEY: 51:72057594047168512 (bb3c79607252) waittime=3335 ownerId=8370217230 transactionname=MERGE lasttranstarted=2016-04-13T08:20:52.093 XDES=0x329549950 lockMode=U schedulerid=13 kpid=4148 status=suspended spid=1162 sbid=1 ecid=0 priority=0 trancount=1 lastbatchstarted=2016-04-13T08:20:52.080 lastbatchcompleted=2016-04-13T08:20:52.080 clientapp=GWAPP hostname=Server03 hostpid=4588 loginname=Nope isolationlevel=read committed (2) xactid=8370217230 currentdb=51 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056

    2016-04-13 08:20:55.43 spid29s executionStack

    2016-04-13 08:20:55.43 spid29s frame procname=adhoc line=1 sqlhandle=0x020000002a8bf93ad7ebec868b7317d75acb8fe2eb61d593

    2016-04-13 08:20:55.43 spid29s MERGE sent WITH (ROWLOCK) AS target USING(SELECT se_ref, se_success, se_smsc_status, se_time_delivered, se_messageref FROM #MyTempTable_3 WITH (ROWLOCK)) AS source(se_ref, se_success, se_smsc_status, se_time_delivered, se_messageref) ON(target.se_ref = source.se_ref) WHEN MATCHED THEN UPDATE SET se_success = COALESCE(source.se_success,target.se_success), se_smsc_status = COALESCE(source.se_smsc_status, target.se_smsc_status), se_time_delivered = COALESCE(source.se_time_delivered,target.se_time_delivered) , se_messageref = COALESCE(source.se_messageref, target.se_messageref)OUTPUT Inserted.se_ref;

    2016-04-13 08:20:55.43 spid29s inputbuf

    2016-04-13 08:20:55.43 spid29s MERGE sent WITH (ROWLOCK) AS target USING(SELECT se_ref, se_success, se_smsc_status, se_time_delivered, se_messageref FROM #MyTempTable_3 WITH (ROWLOCK)) AS source(se_ref, se_success, se_smsc_status, se_time_delivered, se_messageref) ON(target.se_ref = source.se_ref) WHEN MATCHED THEN UPDATE SET se_success = COALESCE(source.se_success,target.se_success), se_smsc_status = COALESCE(source.se_smsc_status, target.se_smsc_status), se_time_delivered = COALESCE(source.se_time_delivered,target.se_time_delivered) , se_messageref = COALESCE(source.se_messageref, target.se_messageref)OUTPUT Inserted.se_ref;

    2016-04-13 08:20:55.43 spid29s resource-list

    2016-04-13 08:20:55.43 spid29s keylock hobtid=72057594047168512 dbid=51 objectname=sms_smsgw3.dbo.sent indexname=_index_sent_se_ref_nonclust id=lock4462ee600 mode=U associatedObjectId=72057594047168512

    2016-04-13 08:20:55.43 spid29s owner-list

    2016-04-13 08:20:55.43 spid29s owner id=process53d8e08 mode=U

    2016-04-13 08:20:55.43 spid29s waiter-list

    2016-04-13 08:20:55.43 spid29s waiter id=process73e3474c8 mode=U requestType=wait

    2016-04-13 08:20:55.43 spid29s keylock hobtid=72057594047168512 dbid=51 objectname=sms_smsgw3.dbo.sent indexname=_index_sent_se_ref_nonclust id=lockf1a2c680 mode=U associatedObjectId=72057594047168512

    2016-04-13 08:20:55.43 spid29s owner-list

    2016-04-13 08:20:55.43 spid29s owner id=process73e3474c8 mode=U

    2016-04-13 08:20:55.43 spid29s waiter-list

    2016-04-13 08:20:55.43 spid29s waiter id=process53d8e08 mode=U requestType=wait

    What I have tried:

    Origionally the index on se_ref was a clustered index. I have changed it to a non-clustered index.

    I have also attempted to remove all indexes however this impacted reporting and non inserting queries detrimentally.

    All indexes are reorganised daily and statistics are also updated.

    I have had the devs look into different table hints on the merge however that has also not born any fruit.

    Other random bits of info:

    The TempDB, sms_smsgw3 data file and sms_smsgw3 log file are all on different disk sub-systems.

    The disks have a response time of sub 5ms and a disk queue of < 0.05

    Total CPU usage on the server is <25%

    DB size is 8GB in this instance but in others is upwards of 100GB's

    the _index_sent_se_ref_nonclust index is 96.18% full and 7.25% fragmented

    Let me know if any further info is needed.

    Thanks

  • Try splitting the MERGE out into its component INSERT, UPDATE and DELETEs.

    https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

  • Avoid threading if you're writing to the same table. Leave that option to SQL Server by the use of parallelism.

    Also, align the clustered indexes of temp tables with perm table.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Can you post the execution plans of a couple of those MERGE statements please? Actual, not estimated. If you can, try to get one where the number of rows in the temp table is about as high as it gets.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Unfortunately I cannot get the actual execution plan.

    I have spoken to the dev's and they will be looking to add a "lock" to the secondary threads.

    We will then include a count on how long the locked threads wait for compared to how long some of the queries would normally take to complete.

    Thanks for the input so far.

  • douglasbrown880 (4/13/2016)


    Unfortunately I cannot get the actual execution plan.

    I have spoken to the dev's and they will be looking to add a "lock" to the secondary threads.

    We will then include a count on how long the locked threads wait for compared to how long some of the queries would normally take to complete.

    Thanks for the input so far.

    That's unfortunate - once you have your deadlock output, it's the next place to look. If it's going to be your responsibility to resolve deadlocks in the future then fight for the right to use trace or extended events to capture actual plans.

    An estimated plan and an idea of the rowcounts in those temp tables would help.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (4/13/2016)


    Can you post the execution plans of a couple of those MERGE statements please? Actual, not estimated. If you can, try to get one where the number of rows in the temp table is about as high as it gets.

    I agree with what Ken wrote above... try splitting the MERGE to its separate parts so the that transactions are shorter. When you do, be aware that you're doing a joined update and, if you look carefully in Books Online, you will NEVER find an example of a joined update where the target-table is NOT in the FROM clause. Not doing so is actually an "illegal" form of UPDATE that SQL Server won't warn you about and it can and does lead to a "race condition" similar to that found in "Halloweening". It will frequently work as you expect it to but it will sometimes lead to pegging CPUs and taking a whole lot longer than you could ever imagine. I've troubleshot many instances where a normal 2 second update would slam multiple CPUs into the wall and take 2 hours to complete.

    A good general pattern to follow for joined updates looks like this and, if you use MERGE, still applies...

    UPDATE tgt

    SET yadda-yadda

    FROM <TargetTable> tgt

    JOIN <SourceTable> src

    ON tgt.SomeColumn = src.SomeColumn

    ;

    I'd also remove all the locking hints. SQL Server is normally a whole lot smarter about such things than any of us.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Got the query plan:

    Showplan XML<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2500.0"><BatchSequence><Batch><Statements><StmtSimple><QueryPlan CachedPlanSize="112" CompileTime="10" CompileCPU="10" CompileMemory="752"><RelOp NodeId="2" PhysicalOp="Table Merge" LogicalOp="Merge" EstimateRows="1" EstimateIO="0.05" EstimateCPU="5e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0736757" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_ref"/></OutputList><Update DMLRequestSort="0"><Object Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" IndexKind="Heap"/><Object Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Index="[_index_sent_se_se_messageref]" Alias="[target]" IndexKind="NonClustered"/><Object Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Index="[_index_sent_se_success]" Alias="[target]" IndexKind="NonClustered"/><Object Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Index="[_index_sent_se_time_delivered]" Alias="[target]" IndexKind="NonClustered"/><Object Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Index="[_index_sent_se_phnum_inc]" Alias="[target]" IndexKind="NonClustered"/><SetPredicate><ScalarOperator ScalarString="[sms_smsgw3].[dbo].[sent].[se_success] as [target].[se_success] = [Expr1009],[sms_smsgw3].[dbo].[sent].[se_smsc_status] as [target].[se_smsc_status] = [Expr1010],[sms_smsgw3].[dbo].[sent].[se_messageref] as [target].[se_messageref] = [Expr1011],[sms_smsgw3].[dbo].[sent].[se_time_delivered] as [target].[se_time_delivered] = [Expr1012]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_success"/><ScalarOperator><Identifier><ColumnReference Column="Expr1009"/></Identifier></ScalarOperator></Assign><Assign><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_smsc_status"/><ScalarOperator><Identifier><ColumnReference Column="Expr1010"/></Identifier></ScalarOperator></Assign><Assign><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_messageref"/><ScalarOperator><Identifier><ColumnReference Column="Expr1011"/></Identifier></ScalarOperator></Assign><Assign><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_time_delivered"/><ScalarOperator><Identifier><ColumnReference Column="Expr1012"/></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><ActionColumn><ColumnReference Column="Action1008"/></ActionColumn><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="88" EstimatedTotalSubtreeCost="0.0236707" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Bmk1004"/><ColumnReference Column="Action1008"/><ColumnReference Column="Expr1009"/><ColumnReference Column="Expr1010"/><ColumnReference Column="Expr1011"/><ColumnReference Column="Expr1012"/><ColumnReference Column="Expr1031"/><ColumnReference Column="Expr1032"/><ColumnReference Column="Expr1033"/><ColumnReference Column="Expr1034"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1031"/><ScalarOperator ScalarString="[Expr1031]"><Identifier><ColumnReference Column="Expr1031"/></Identifier></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1032"/><ScalarOperator ScalarString="[Expr1032]"><Identifier><ColumnReference Column="Expr1032"/></Identifier></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1033"/><ScalarOperator ScalarString="[Expr1033]"><Identifier><ColumnReference Column="Expr1033"/></Identifier></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1034"/><ScalarOperator ScalarString="[Expr1034]"><Identifier><ColumnReference Column="Expr1034"/></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="88" EstimatedTotalSubtreeCost="0.0236707" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Bmk1004"/><ColumnReference Column="Action1008"/><ColumnReference Column="Expr1009"/><ColumnReference Column="Expr1010"/><ColumnReference Column="Expr1011"/><ColumnReference Column="Expr1012"/><ColumnReference Column="Expr1031"/><ColumnReference Column="Expr1032"/><ColumnReference Column="Expr1033"/><ColumnReference Column="Expr1034"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1031"/><ScalarOperator ScalarString="CASE WHEN [Action1008] = (1) THEN CASE WHEN [Expr1019] THEN (0) ELSE (1) END ELSE [Action1008] END"><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><IF><Condition><ScalarOperator><Identifier><ColumnReference Column="Expr1019"/></Identifier></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Else></IF></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1032"/><ScalarOperator ScalarString="CASE WHEN [Action1008] = (1) THEN CASE WHEN [Expr1017] THEN (0) ELSE (1) END ELSE [Action1008] END"><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><IF><Condition><ScalarOperator><Identifier><ColumnReference Column="Expr1017"/></Identifier></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Else></IF></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1033"/><ScalarOperator ScalarString="CASE WHEN [Action1008] = (1) THEN CASE WHEN [Expr1020] THEN (0) ELSE (1) END ELSE [Action1008] END"><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><IF><Condition><ScalarOperator><Identifier><ColumnReference Column="Expr1020"/></Identifier></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Else></IF></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1034"/><ScalarOperator ScalarString="CASE WHEN [Action1008] = (1) THEN CASE WHEN [Expr1020] THEN (0) ELSE (1) END ELSE [Action1008] END"><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><IF><Condition><ScalarOperator><Identifier><ColumnReference Column="Expr1020"/></Identifier></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Else></IF></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Assert" LogicalOp="Assert" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-007" AvgRowSize="73" EstimatedTotalSubtreeCost="0.0236706" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Bmk1004"/><ColumnReference Column="Action1008"/><ColumnReference Column="Expr1009"/><ColumnReference Column="Expr1010"/><ColumnReference Column="Expr1011"/><ColumnReference Column="Expr1012"/><ColumnReference Column="Expr1017"/><ColumnReference Column="Expr1019"/><ColumnReference Column="Expr1020"/></OutputList><Assert StartupExpression="0"><RelOp NodeId="6" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="8e-008" AvgRowSize="81" EstimatedTotalSubtreeCost="0.0236701" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Bmk1004"/><ColumnReference Column="Action1008"/><ColumnReference Column="Expr1009"/><ColumnReference Column="Expr1010"/><ColumnReference Column="Expr1011"/><ColumnReference Column="Expr1012"/><ColumnReference Column="Expr1017"/><ColumnReference Column="Expr1019"/><ColumnReference Column="Expr1020"/><ColumnReference Column="Expr1022"/></OutputList><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1022"/><ScalarOperator ScalarString="conditional_row_number"><Sequence FunctionName="conditional_row_number"/></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1" EstimateIO="0" EstimateCPU="2e-008" AvgRowSize="81" EstimatedTotalSubtreeCost="0.02367" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Bmk1004"/><ColumnReference Column="Action1008"/><ColumnReference Column="Expr1009"/><ColumnReference Column="Expr1010"/><ColumnReference Column="Expr1011"/><ColumnReference Column="Expr1012"/><ColumnReference Column="Expr1017"/><ColumnReference Column="Expr1019"/><ColumnReference Column="Expr1020"/><ColumnReference Column="Segment1041"/></OutputList><Segment><GroupBy><ColumnReference Column="Bmk1004"/></GroupBy><SegmentColumn><ColumnReference Column="Segment1041"/></SegmentColumn><RelOp NodeId="8" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100073" AvgRowSize="73" EstimatedTotalSubtreeCost="0.02367" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Bmk1004"/><ColumnReference Column="Action1008"/><ColumnReference Column="Expr1009"/><ColumnReference Column="Expr1010"/><ColumnReference Column="Expr1011"/><ColumnReference Column="Expr1012"/><ColumnReference Column="Expr1017"/><ColumnReference Column="Expr1019"/><ColumnReference Column="Expr1020"/></OutputList><MemoryFractions Input="0.333333" Output="1"/><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Bmk1004"/></OrderByColumn></OrderBy><RelOp NodeId="9" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="73" EstimatedTotalSubtreeCost="0.0123087" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Bmk1004"/><ColumnReference Column="Action1008"/><ColumnReference Column="Expr1009"/><ColumnReference Column="Expr1010"/><ColumnReference Column="Expr1011"/><ColumnReference Column="Expr1012"/><ColumnReference Column="Expr1017"/><ColumnReference Column="Expr1019"/><ColumnReference Column="Expr1020"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1009"/><ScalarOperator ScalarString="CASE WHEN [tempdb].[dbo].[#MyTempTable_2].[se_success] IS NOT NULL THEN [tempdb].[dbo].[#MyTempTable_2].[se_success] ELSE [sms_smsgw3].[dbo].[sent].[se_success] as [target].[se_success] END"><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_success"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1010"/><ScalarOperator ScalarString="CASE WHEN [tempdb].[dbo].[#MyTempTable_2].[se_smsc_status] IS NOT NULL THEN [tempdb].[dbo].[#MyTempTable_2].[se_smsc_status] ELSE [sms_smsgw3].[dbo].[sent].[se_smsc_status] as [target].[se_smsc_status] END"><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_smsc_status"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_smsc_status"/></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_smsc_status"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1011"/><ScalarOperator ScalarString="CASE WHEN [tempdb].[dbo].[#MyTempTable_2].[se_messageref] IS NOT NULL THEN [tempdb].[dbo].[#MyTempTable_2].[se_messageref] ELSE [sms_smsgw3].[dbo].[sent].[se_messageref] as [target].[se_messageref] END"><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_messageref"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1012"/><ScalarOperator ScalarString="CASE WHEN [tempdb].[dbo].[#MyTempTable_2].[se_time_delivered] IS NOT NULL THEN [tempdb].[dbo].[#MyTempTable_2].[se_time_delivered] ELSE [sms_smsgw3].[dbo].[sent].[se_time_delivered] as [target].[se_time_delivered] END"><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_time_delivered"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1017"/><ScalarOperator ScalarString="[Action1008]=(1) AND CASE WHEN [sms_smsgw3].[dbo].[sent].[se_success] as [target].[se_success] = CASE WHEN [tempdb].[dbo].[#MyTempTable_2].[se_success] IS NOT NULL THEN [tempdb].[dbo].[#MyTempTable_2].[se_success] ELSE [sms_smsgw3].[dbo].[sent].[se_success] as [target].[se_success] END THEN (1) ELSE (0) END"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="BINARY IS"><ScalarOperator><Identifier><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_success"/></Identifier></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_success"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(0)"/></ScalarOperator></Else></IF></ScalarOperator></Logical></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1019"/><ScalarOperator ScalarString="[Action1008]=(1) AND CASE WHEN [sms_smsgw3].[dbo].[sent].[se_messageref] as [target].[se_messageref] = CASE WHEN [tempdb].[dbo].[#MyTempTable_2].[se_messageref] IS NOT NULL THEN [tempdb].[dbo].[#MyTempTable_2].[se_messageref] ELSE [sms_smsgw3].[dbo].[sent].[se_messageref] as [target].[se_messageref] END THEN (1) ELSE (0) END"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="BINARY IS"><ScalarOperator><Identifier><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_messageref"/></Identifier></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_messageref"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(0)"/></ScalarOperator></Else></IF></ScalarOperator></Logical></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1020"/><ScalarOperator ScalarString="[Action1008]=(1) AND CASE WHEN [sms_smsgw3].[dbo].[sent].[se_time_delivered] as [target].[se_time_delivered] = CASE WHEN [tempdb].[dbo].[#MyTempTable_2].[se_time_delivered] IS NOT NULL THEN [tempdb].[dbo].[#MyTempTable_2].[se_time_delivered] ELSE [sms_smsgw3].[dbo].[sent].[se_time_delivered] as [target].[se_time_delivered] END THEN (1) ELSE (0) END"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Action1008"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="BINARY IS"><ScalarOperator><Identifier><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_time_delivered"/></Identifier></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_time_delivered"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(0)"/></ScalarOperator></Else></IF></ScalarOperator></Logical></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="10" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="109" EstimatedTotalSubtreeCost="0.0123086" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_smsc_status"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/><ColumnReference Column="Bmk1004"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_time_delivered"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_messageref"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_success"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_smsc_status"/><ColumnReference Column="Action1008"/></OutputList><Top RowCount="1" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"/></ScalarOperator></TopExpression><RelOp NodeId="11" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="109" EstimatedTotalSubtreeCost="0.0123085" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_smsc_status"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/><ColumnReference Column="Bmk1004"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_time_delivered"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_messageref"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_success"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_smsc_status"/><ColumnReference Column="Action1008"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Action1008"/><ScalarOperator ScalarString="ForceOrder((1))"><Intrinsic FunctionName="ForceOrder"><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="12" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="105" EstimatedTotalSubtreeCost="0.0123084" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_smsc_status"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/><ColumnReference Column="Bmk1004"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_time_delivered"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_messageref"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_success"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_smsc_status"/></OutputList><NestedLoops Optimized="1"><OuterReferences><ColumnReference Column="Bmk1004"/></OuterReferences><RelOp NodeId="14" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="6.688e-005" AvgRowSize="67" EstimatedTotalSubtreeCost="0.00902108" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_smsc_status"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/><ColumnReference Column="Bmk1004"/><ColumnReference Column="Expr1040"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1040"/><ScalarOperator ScalarString="BmkToPage([Bmk1004])"><Intrinsic FunctionName="BmkToPage"><ScalarOperator><Identifier><ColumnReference Column="Bmk1004"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="15" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="6.688e-005" AvgRowSize="67" EstimatedTotalSubtreeCost="0.00902108" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_smsc_status"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/><ColumnReference Column="Bmk1004"/></OutputList><NestedLoops Optimized="1"><OuterReferences><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_ref"/></OuterReferences><RelOp NodeId="17" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="16" EstimateIO="0.003125" EstimateCPU="0.0001746" AvgRowSize="63" EstimatedTotalSubtreeCost="0.0032996" TableCardinality="16" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_ref"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_smsc_status"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/></OutputList><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_ref"/></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_success"/></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_smsc_status"/></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_time_delivered"/></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_messageref"/></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" IndexKind="Heap"/></TableScan></RelOp><RelOp NodeId="18" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0056546" TableCardinality="7.28714e+006" Parallel="0" EstimateRebinds="15" EstimateRewinds="0"><OutputList><ColumnReference Column="Bmk1004"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Column="Bmk1004"/></DefinedValue></DefinedValues><Object Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Index="[_index_sent_se_ref_nonclust]" Alias="[target]" IndexKind="NonClustered"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_ref"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[tempdb].[dbo].[#MyTempTable_2].[se_ref]"><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#MyTempTable_2]" Column="se_ref"/></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp><RelOp NodeId="23" PhysicalOp="RID Lookup" LogicalOp="RID Lookup" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="59" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="7.28714e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_time_delivered"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_messageref"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_success"/><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_smsc_status"/></OutputList><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_time_delivered"/></DefinedValue><DefinedValue><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_messageref"/></DefinedValue><DefinedValue><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_success"/></DefinedValue><DefinedValue><ColumnReference Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" Column="se_smsc_status"/></DefinedValue></DefinedValues><Object Database="[sms_smsgw3]" Schema="[dbo]" Table="[sent]" Alias="[target]" TableReferenceId="-1" IndexKind="Heap"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Column="Bmk1004"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Bmk1004]"><Identifier><ColumnReference Column="Bmk1004"/></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Top></RelOp></ComputeScalar></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp><Predicate><ScalarOperator ScalarString="CASE WHEN [Expr1022]>(1) THEN (0) ELSE NULL END"><IF><Condition><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Column="Expr1022"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Else></IF></ScalarOperator></Predicate></Assert></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>GatewayAppUsername193845472016-04-14 07:52:38.3800X0000000051sms_smsgw33255420201Server03110X6C759E78BA4FC4488B32223D3ABA11C30153279281Dynamic SQL20801 - AQ1SQL-Live01Username85165034412349347135247

  • Can you post it as a .sqlplan file attachment please?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Here you go.

    This is from a different app server and database, however the issue is the same

  • douglasbrown880 (4/14/2016)


    the issue is the same

    And it has a name.

    It's name is "MERGE".

    _____________
    Code for TallyGenerator

  • douglasbrown880 (4/14/2016)


    Here you go.

    This is from a different app server and database, however the issue is the same

    It's an estimated plan - can you post an actual plan?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply