add batch remove batch split batch show hidden batches hide batch
db<>fiddle
feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
EventTime NearestIslandId GapStart GapEnd
10/10/0007 14:31:39 59365 10/10/0007 14:00:00 10/10/0007 16:00:00
22/10/0030 09:38:40 261274 22/10/0030 09:30:00 22/10/0030 10:00:00
08/03/0036 23:43:34 308422 08/03/0036 23:00:00 09/03/0036 01:00:00
09/08/0057 11:10:21 496189 09/08/0057 06:30:00 09/08/0057 13:00:00
01/05/0058 10:14:25 502545 01/05/0058 10:00:00 01/05/0058 10:30:00
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.4001.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH rankIslands AS (&#xa;&#x9;SELECT&#x9; ID&#xa;&#x9;&#x9;&#x9;, WindowStart&#xa;&#x9;&#x9;&#x9;, WindowEnd&#xa;&#x9;&#x9;&#x9;, ROW_NUMBER() OVER (ORDER BY WindowStart) AS rnk&#xa;&#x9;FROM&#x9;#tmp&#xa;), rankGapsJoined AS (&#xa;&#x9;SELECT&#x9; t1.ID AS NearestIslandID_Lower&#xa;&#x9;&#x9;&#x9;, t1.WindowEnd AS GapStart_Lower&#xa;&#x9;&#x9;&#x9;, DATEADD(MINUTE, (DATEDIFF(MINUTE, t1.WindowEnd, t2.WindowStart) / 2), t1.WindowEnd) AS GapEnd_Lower&#xa;&#x9;&#x9;&#x9;, t2.ID AS NearestIslandID_Higher&#xa;&#x9;&#x9;&#x9;, DATEADD(MINUTE, -1 * (DATEDIFF(MINUTE, t1.WindowEnd, t2.WindowStart) / 2), t2.WindowStart) AS GapStart_Higher&#xa;&#x9;&#x9;&#x9;, t2.WindowStart AS GapEnd_Higher&#xa;&#x9;FROM rankIslands t1 INNER JOIN rankIslands t2&#xa;&#x9;&#x9;ON t1.rnk + 1 = t2.rnk&#xa;&#x9;&#x9;&#x9;AND t1.WindowEnd &lt;&gt; t2.WindowStart&#xa;), NearestIsland AS (&#xa;&#x9;SELECT&#x9;xa.*&#xa;&#x9;FROM&#x9;rankGapsJoined t1&#xa;&#x9;&#x9;&#x9;CROSS APPLY ( VALUES (t1.NearestIslandID_Lower, t1.GapStart_Lower, t1.GapEnd_Lower)&#xa;&#x9;&#x9;&#x9;&#x9;&#x9;&#x9;&#x9;&#x9;,(t1.NearestIslandID_Higher, t1.GapStart_Higher, t1.GapEnd_Higher) ) AS xa (NearestIslandId, GapStart, GapEnd)&#xa;)&#xa;-- Only return records that fall into the Gaps&#xa;SELECT e.EventTime, ni.*&#xa;FROM&#x9;#tmpEvent e INNER JOIN NearestIsland ni&#xa;&#x9;&#x9;&#x9;&#x9;ON e.EventTime &gt; ni.GapStart&#xa;&#x9;&#x9;&#x9;&#x9;AND e.EventTime &lt;= ni.GapEnd" StatementId="1" StatementCompId="8" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="250281" StatementEstRows="1.64317e+006" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x39F300EE6333E8CC" QueryPlanHash="0x4A63F93227ACC3E1" CardinalityEstimationModelVersion="130"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="77728" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="56" CompileTime="0" CompileCPU="0" CompileMemory="656"><MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="77728" RequiredMemory="640" DesiredMemory="77728" RequestedMemory="77728" GrantWaitTime="0" GrantedMemory="77728" MaxUsedMemory="69080" MaxQueryMemory="624080"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1442624"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><WaitStats><Wait WaitType="IO_COMPLETION" WaitTimeMs="109" WaitCount="2"></Wait><Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="4734" WaitCount="1129"></Wait></WaitStats><QueryTimeStats ElapsedTime="9484" CpuTime="4641"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1.64317e+006" EstimateIO="0" EstimateCPU="41.8" AvgRowSize="35" EstimatedTotalSubtreeCost="250281" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmpEvent]" Alias="[e]" Column="EventTime"></ColumnReference><ColumnReference Column="Union1010"></ColumnReference><ColumnReference Column="Union1011"></ColumnReference><ColumnReference Column="Union1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="9484" ActualCPUms="4640" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[tempdb].[dbo].[#tmpEvent].[EventTime] as [e].[EventTime]&gt;[Union1011] AND [tempdb].[dbo].[#tmpEvent].[EventTime] as [e].[EventTime]&lt;=[Union1012]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmpEvent]" Alias="[e]" Column="EventTime"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Union1011"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmpEvent]" Alias="[e]" Column="EventTime"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Union1012"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1e+006" EstimateIO="0" EstimateCPU="4.18" AvgRowSize="27" EstimatedTotalSubtreeCost="250142" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1010"></ColumnReference><ColumnReference Column="Union1011"></ColumnReference><ColumnReference Column="Union1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="667350" Batches="0" ActualExecutionMode="Row" ActualElapsedms="3515" ActualCPUms="1187" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.05" AvgRowSize="47" EstimatedTotalSubtreeCost="250137" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="dateadd(minute,datediff(minute,[Expr1014],[Expr1013])/(2),[tempdb].[dbo].[#tmp].[WindowEnd])"><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(7)"></Const></ScalarOperator><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Intrinsic FunctionName="datediff"><ScalarOperator><Const ConstValue="(7)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1014"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1013"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1009"></ColumnReference><ScalarOperator ScalarString="dateadd(minute, -((1)*(datediff(minute,[Expr1014],[Expr1013])/(2))),[tempdb].[dbo].[#tmp].[WindowStart])"><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(7)"></Const></ScalarOperator><ScalarOperator><Arithmetic Operation="MINUS"><ScalarOperator><Arithmetic Operation="MULT"><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Intrinsic FunctionName="datediff"><ScalarOperator><Const ConstValue="(7)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1014"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1013"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Arithmetic></ScalarOperator></Arithmetic></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="500000" EstimateIO="15.9831" EstimateCPU="250025" AvgRowSize="51" EstimatedTotalSubtreeCost="250137" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference><ColumnReference Column="Expr1014"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="333675" Batches="0" ActualExecutionMode="Row" ActualElapsedms="3312" ActualCPUms="1093" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Merge ManyToMany="1"><InnerSideJoinColumns><ColumnReference Column="Expr1007"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Column="Expr1015"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="([Expr1004]+(1))=[Expr1007] AND [tempdb].[dbo].[#tmp].[WindowEnd]&lt;&gt;[tempdb].[dbo].[#tmp].[WindowStart]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="NE"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Residual><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.05" AvgRowSize="45" EstimatedTotalSubtreeCost="48.1448" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1014"></ColumnReference><ColumnReference Column="Expr1015"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1015"></ColumnReference><ScalarOperator ScalarString="[Expr1004]+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.05" AvgRowSize="37" EstimatedTotalSubtreeCost="48.0948" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1014"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1014"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(datetimeoffset(7),[tempdb].[dbo].[#tmp].[WindowEnd],0)"><Convert DataType="datetimeoffset" Scale="7" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.04" AvgRowSize="27" EstimatedTotalSubtreeCost="48.0448" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="1937" ActualCPUms="281" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.01" AvgRowSize="27" EstimatedTotalSubtreeCost="48.0048" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference><ColumnReference Column="Segment1016"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="1843" ActualCPUms="187" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1016"></ColumnReference></SegmentColumn><RelOp NodeId="8" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="500000" EstimateIO="0.0112613" EstimateCPU="43.448" AvgRowSize="27" EstimatedTotalSubtreeCost="47.9948" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></OutputList><MemoryFractions Input="1" Output="0.531652"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="1843" ActualCPUms="187" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="1" InputMemoryGrant="77600" OutputMemoryGrant="41112" UsedMemoryGrant="32472"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="9" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="500000" EstimatedRowsRead="500000" EstimateIO="3.98543" EstimateCPU="0.550079" AvgRowSize="27" EstimatedTotalSubtreeCost="4.5355" TableCardinality="500000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="78" ActualCPUms="46" ActualScans="1" ActualLogicalReads="5397" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="500000" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Index="[PK__#tmp______3214EC27370A41AE]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp><RelOp NodeId="10" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.05" AvgRowSize="37" EstimatedTotalSubtreeCost="48.0948" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1013"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(datetimeoffset(7),[tempdb].[dbo].[#tmp].[WindowStart],0)"><Convert DataType="datetimeoffset" Scale="7" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.04" AvgRowSize="27" EstimatedTotalSubtreeCost="48.0448" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="843" ActualCPUms="281" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="12" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="500000" EstimateIO="0" EstimateCPU="0.01" AvgRowSize="27" EstimatedTotalSubtreeCost="48.0048" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference><ColumnReference Column="Segment1017"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="781" ActualCPUms="218" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1017"></ColumnReference></SegmentColumn><RelOp NodeId="13" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="500000" EstimateIO="0.0112613" EstimateCPU="43.448" AvgRowSize="19" EstimatedTotalSubtreeCost="47.9948" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></OutputList><Warnings><SpillToTempDb SpillLevel="1" SpilledThreadCount="1"></SpillToTempDb><SortSpillDetails GrantedMemoryKb="36608" UsedMemoryKb="36608" WritesToTempDb="1175" ReadsFromTempDb="1175"></SortSpillDetails></Warnings><MemoryFractions Input="0.468348" Output="0.468348"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="781" ActualCPUms="218" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="36608" OutputMemoryGrant="36224" UsedMemoryGrant="36608"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="14" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="500000" EstimatedRowsRead="500000" EstimateIO="3.98543" EstimateCPU="0.550079" AvgRowSize="19" EstimatedTotalSubtreeCost="4.5355" TableCardinality="500000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="500000" Batches="0" ActualExecutionMode="Row" ActualElapsedms="62" ActualCPUms="62" ActualScans="1" ActualLogicalReads="5397" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="500000" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Index="[PK__#tmp______3214EC27370A41AE]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></Merge></RelOp></ComputeScalar></RelOp><RelOp NodeId="15" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="2" EstimateIO="0" EstimateCPU="2.157e-006" AvgRowSize="27" EstimatedTotalSubtreeCost="1" Parallel="0" EstimateRebinds="499999" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1010"></ColumnReference><ColumnReference Column="Union1011"></ColumnReference><ColumnReference Column="Union1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="667350" Batches="0" ActualExecutionMode="Row" ActualElapsedms="31" ActualCPUms="31" ActualEndOfScans="333675" ActualExecutions="333675"></RunTimeCountersPerThread></RunTimeInformation><ConstantScan><Values><Row><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp].[ID]"><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp].[WindowEnd]"><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowEnd"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[Expr1008]"><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator></Row><Row><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp].[ID]"><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="ID"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[Expr1009]"><Identifier><ColumnReference Column="Expr1009"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="[tempdb].[dbo].[#tmp].[WindowStart]"><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmp]" Column="WindowStart"></ColumnReference></Identifier></ScalarOperator></Row></Values></ConstantScan></RelOp></NestedLoops></RelOp><RelOp NodeId="16" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="10" EstimatedRowsRead="10" EstimateIO="0.0032035" EstimateCPU="8.95e-005" AvgRowSize="15" EstimatedTotalSubtreeCost="89.5032" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="999999" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmpEvent]" Alias="[e]" Column="EventTime"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6673500" Batches="0" ActualExecutionMode="Row" ActualElapsedms="3765" ActualCPUms="2015" ActualScans="1" ActualLogicalReads="667350" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6673500" ActualEndOfScans="667350" ActualExecutions="667350"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#tmpEvent]" Alias="[e]" Column="EventTime"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpEvent]" Alias="[e]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>