Performance Problems

finchen

Hello,

i measure times for a delete, insert and select in a database with a c# program (.net). If i measure times for delete 5.000 data records I get very good times but if I delete 100 data records in a other database I get very bad times.

Example:

Statement: Delete from xy where id =1;

data rows delete : Time:
100 73.045
500 332.238
1000 636.075
5000 36.082
50000 308.774

The same time jumps I get for a select. But only for this big select:

for exmaple:

Select id, (Select name from Gemeinden inner join Hochschulen where distance>=Hochschulen.entfernung and distance <Hochschulen.entfernung ), (Select name from Hauser inner join Hochschulen where distance>=Hochschulen.entfernung and distance <Hochschulen.entfernung ) from Hochulen inner Join Uni on Hochschulen.id=Uni.id...

What is the reason for this big time jump If i update the statistic I get better times for a delete but not for this big select.

Can I change I size of a page (8KB)

I hope somebody can help my!!

(I use MS SQL Server 2005)

Greeting finchen

WesleyB

The page size is fixed so you cannot change it from 8KB and there is no reason why you would want to either.

What is the query plan of these queries Have you checked the missing indexes dmv

WesleyB

Visit my SQL Server weblog @ http://dis4ea.blogspot.com

finchen

Do you want to see the query plan On the tables are indexes but I dont know if he use the indexes.

WesleyB

It could help us if we see the query plan, so go ahead if you wish to show it.

WesleyB

Visit my SQL Server weblog @ http://dis4ea.blogspot.com

Jens K. Suessmeyer

If you want to share the Querylan with us, please share it as a SQLPLAN file. SqlPlan Files are XML based queryplans which can be easily imported by any SSMS Studio (No more text debugging :-) )

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

WesleyB

One of the nifty features of SSMS.

WesleyB

Visit my SQL Server weblog @ http://dis4ea.blogspot.com

finchen

The first plan with 500 data records this is the select:

----------------------------------------------------

< 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.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="17.0326" StatementText="SELECT ILI_DATA.ILI_DATA_ID,STATION_POINT_2.MEASURE,ILI_DATA.LENGTH, ILI_DATA.WIDTH, ILI_DATA.Nominal_WALL_THICKNESS, ILI_DATA.PIPE_SMYS, ILI_DATA.B31G_MAOP, ILI_DATA.ORIENTATION_DEG,
(SELECT PIPE_SEGMENT.NOMINAL_DIAMETER_GCL
FROM STATION_POINT as s1 INNER JOIN
 EVENT_RANGE ON s1.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN
 STATION_POINT AS s2 ON EVENT_RANGE.STATION_ID_END = s2.STATION_ID AND 
 s1.ROUTE_ID = s2.ROUTE_ID INNER JOIN
 PIPE_SEGMENT ON EVENT_RANGE.EVENT_ID = PIPE_SEGMENT.EVENT_ID
WHERE (s1.MEASURE < STATION_POINT_2.MEASURE) AND (s2.MEASURE > STATION_POINT_2.MEASURE) AND (s1.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Nominal_Diameter,
(SELECT ILI_PIPE_LENGTH.Measured_Wall_Thickness
FROM STATION_POINT AS s3 INNER JOIN
 EVENT_RANGE ON s3.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN
 STATION_POINT AS s4 ON EVENT_RANGE.STATION_ID_END = s4.STATION_ID AND s3.ROUTE_ID = s4.ROUTE_ID INNER JOIN
 ILI_PIPE_LENGTH ON EVENT_RANGE.EVENT_ID = ILI_PIPE_LENGTH.EVENT_ID
WHERE (s3.MEASURE < STATION_POINT_2.MEASURE) AND (s4.MEASURE > STATION_POINT_2.MEASURE) AND (s3.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Measured_Wall_Thickness
FROM EVENT_RANGE INNER JOIN
 ILI_DATA ON EVENT_RANGE.EVENT_ID = ILI_DATA.EVENT_ID INNER JOIN
 STATION_POINT AS STATION_POINT_2 ON EVENT_RANGE.STATION_ID_BEGIN = STATION_POINT_2.STATION_ID AND 
 EVENT_RANGE.STATION_ID_END = STATION_POINT_2.STATION_ID
WHERE (ILI_DATA.ILI_INSPECTION_ID = 22)" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="153">
<MissingIndexes>
<MissingIndexGroup Impact="57.3934">
<MissingIndex Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[ILI_INSPECTION_ID]" ColumnId="61" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[ILI_DATA_ID]" ColumnId="1" />
<Column Name="[EVENT_ID]" ColumnId="3" />
<Column Name="[LENGTH]" ColumnId="21" />
<Column Name="[WIDTH]" ColumnId="22" />
<Column Name="[ORIENTATION_DEG]" ColumnId="23" />
<Column Name="[NOMINAL_WALL_THICKNESS]" ColumnId="33" />
<Column Name="[PIPE_SMYS]" ColumnId="34" />
<Column Name="[B31G_MAOP]" ColumnId="35" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="66" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="17.0326">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
<ColumnReference Column="Expr1035" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1033" />
<ScalarOperator ScalarString="[Expr1033]">
<Identifier>
<ColumnReference Column="Expr1033" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1035" />
<ScalarOperator ScalarString="[Expr1035]">
<Identifier>
<ColumnReference Column="Expr1035" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="66" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="17.0326">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
<ColumnReference Column="Expr1035" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OuterReferences>
<RelOp AvgRowSize="70" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="17.0194">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OuterReferences>
<RelOp AvgRowSize="65" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="9.90674">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OuterReferences>
<RelOp AvgRowSize="55" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="9.90346">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</OuterReferences>
<RelOp AvgRowSize="65" EstimateCPU="0.172813" EstimateIO="9.65201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="9.82483">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_DATA].[ILI_INSPECTION_ID]=(22.)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_INSPECTION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(22.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_DATA].[EVENT_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_BEGIN]=[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_END]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[STATION_POINT_2]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="STATION_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_END]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="12" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="10" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="7.11266">
<OutputList>
<ColumnReference Column="Expr1033" />
</OutputList>
<Assert StartupExpression="false">
<RelOp AvgRowSize="20" EstimateCPU="0.000560787" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="11" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="7.11266">
<OutputList>
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1033" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1032" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1033" />
<ScalarOperator ScalarString="ANY([Perform_Clustern_PODS_100].[dbo].[PIPE_SEGMENT].[NOMINAL_DIAMETER_GCL])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="12" EstimateCPU="0.0919875" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="933.812" LogicalOp="Inner Join" NodeId="12" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="7.1121">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_END]=[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s2].[STATION_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="21" EstimateCPU="0.32816" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="933.817" LogicalOp="Inner Join" NodeId="13" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="6.03068">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<MemoryFractions Input="0.226601" Output="0.987685" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[EVENT_ID]=[Perform_Clustern_PODS_100].[dbo].[PIPE_SEGMENT].[EVENT_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="25" EstimateCPU="0.0447123" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10696.7" LogicalOp="Inner Join" NodeId="14" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="5.56526">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<NestedLoops Optimized="true" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
<ColumnReference Column="Expr1041" />
</OuterReferences>
<RelOp AvgRowSize="16" EstimateCPU="0.0447123" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10696.7" LogicalOp="Inner Join" NodeId="17" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="3.82487">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</OutputList>
<NestedLoops Optimized="true" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
<ColumnReference Column="Expr1040" />
</OuterReferences>
<RelOp AvgRowSize="16" EstimateCPU="0.770638" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8444.82" LogicalOp="Inner Join" NodeId="21" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.989431">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</OutputList>
<MemoryFractions Input="0.987685" Output="0.615764" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s1].[STATION_ID] = [Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s1].[STATION_ID]">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="16" EstimateCPU="0.0311213" EstimateIO="0.0571991" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="28149.4" LogicalOp="Index Seek" NodeId="22" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0883204">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[IX_STATION_POINT_ROUTE_ID]" Alias="[s1]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="ROUTE_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.0466035" EstimateIO="0.0838657" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="42224.1" LogicalOp="Index Seek" NodeId="23" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.130469">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[STATION_POINT_FK3]" Alias="[s1]" />
<SeekPredicates>
<SeekPredicate>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.000158393" EstimateIO="0.003125" EstimateRebinds="8443.82" EstimateRewinds="0" EstimateRows="1.26666" LogicalOp="Index Seek" NodeId="28" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="2.79073">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[EVENT_RANGE_FK2]" TableReferenceId="2" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s1].[STATION_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="2252.05" EstimateRewinds="8443.68" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="30" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="1.69568">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="-1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[EVENT_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="21" EstimateCPU="0.0126475" EstimateIO="0.124606" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11355" LogicalOp="Clustered Index Scan" NodeId="34" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.137254">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Index="[PK_PIPE_SEGMENT]" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.770638" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8444.82" LogicalOp="Inner Join" NodeId="37" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.989431">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</OutputList>
<MemoryFractions Input="0.987685" Output="0.987685" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s2].[STATION_ID] = [Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[STATION_ID] as [s2].[STATION_ID]">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="16" EstimateCPU="0.0311213" EstimateIO="0.0571991" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="28149.4" LogicalOp="Index Seek" NodeId="38" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0883204">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[IX_STATION_POINT_ROUTE_ID]" Alias="[s2]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="ROUTE_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.0466035" EstimateIO="0.0838657" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="42224.1" LogicalOp="Index Seek" NodeId="39" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.130469">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[STATION_POINT_FK3]" Alias="[s2]" />
<SeekPredicates>
<SeekPredicate>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1032]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1032" />
</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>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="12" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="49" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.013148">
<OutputList>
<ColumnReference Column="Expr1035" />
</OutputList>
<Assert StartupExpression="false">
<RelOp AvgRowSize="20" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="50" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0131478">
<OutputList>
<ColumnReference Column="Expr1034" />
<ColumnReference Column="Expr1035" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1034" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1035" />
<ScalarOperator ScalarString="ANY([Perform_Clustern_PODS_100].[dbo].[ILI_PIPE_LENGTH].[MEASURED_WALL_THICKNESS])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="51" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0131467">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OuterReferences>
<RelOp AvgRowSize="21" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="53" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985854">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</OuterReferences>
<RelOp AvgRowSize="30" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="55" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</OuterReferences>
<RelOp AvgRowSize="21" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="57" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Index="[PK_ILI_PIPE_LENGTH]" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="58" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="3" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_PIPE_LENGTH].[EVENT_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="59" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s3]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="STATION_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_BEGIN]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [s3].[ROUTE_ID]=[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID] AND [Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [s3].[MEASURE]<[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="60" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s4]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="STATION_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[EVENT_RANGE].[STATION_ID_END]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [s4].[ROUTE_ID]=[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID] AND [Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [s4].[MEASURE]>[Perform_Clustern_PODS_100].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1034]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1034" />
</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>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

--------------------------------

the plan with good times and 250.000 data records(select):

-------------------------------

< 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.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="239286" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="60412.5" StatementText="SELECT ILI_DATA.ILI_DATA_ID,STATION_POINT_2.MEASURE,ILI_DATA.LENGTH, ILI_DATA.WIDTH, ILI_DATA.Nominal_WALL_THICKNESS, ILI_DATA.PIPE_SMYS, ILI_DATA.B31G_MAOP, ILI_DATA.ORIENTATION_DEG,
(SELECT PIPE_SEGMENT.NOMINAL_DIAMETER_GCL
FROM STATION_POINT as s1 INNER JOIN
 EVENT_RANGE ON s1.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN
 STATION_POINT AS s2 ON EVENT_RANGE.STATION_ID_END = s2.STATION_ID AND 
 s1.ROUTE_ID = s2.ROUTE_ID INNER JOIN
 PIPE_SEGMENT ON EVENT_RANGE.EVENT_ID = PIPE_SEGMENT.EVENT_ID
WHERE (s1.MEASURE < STATION_POINT_2.MEASURE) AND (s2.MEASURE > STATION_POINT_2.MEASURE) AND (s1.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Nominal_Diameter,
(SELECT ILI_PIPE_LENGTH.Measured_Wall_Thickness
FROM STATION_POINT AS s3 INNER JOIN
 EVENT_RANGE ON s3.STATION_ID = EVENT_RANGE.STATION_ID_BEGIN INNER JOIN
 STATION_POINT AS s4 ON EVENT_RANGE.STATION_ID_END = s4.STATION_ID AND s3.ROUTE_ID = s4.ROUTE_ID INNER JOIN
 ILI_PIPE_LENGTH ON EVENT_RANGE.EVENT_ID = ILI_PIPE_LENGTH.EVENT_ID
WHERE (s3.MEASURE < STATION_POINT_2.MEASURE) AND (s4.MEASURE > STATION_POINT_2.MEASURE) AND (s3.ROUTE_ID = STATION_POINT_2.ROUTE_ID))as Measured_Wall_Thickness
FROM EVENT_RANGE INNER JOIN
 ILI_DATA ON EVENT_RANGE.EVENT_ID = ILI_DATA.EVENT_ID INNER JOIN
 STATION_POINT AS STATION_POINT_2 ON EVENT_RANGE.STATION_ID_BEGIN = STATION_POINT_2.STATION_ID AND 
 EVENT_RANGE.STATION_ID_END = STATION_POINT_2.STATION_ID
WHERE (ILI_DATA.ILI_INSPECTION_ID = 8)" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="153">
<RelOp AvgRowSize="66" EstimateCPU="0.0239286" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="239286" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="60412.5">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
<ColumnReference Column="Expr1035" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1033" />
<ScalarOperator ScalarString="[Expr1033]">
<Identifier>
<ColumnReference Column="Expr1033" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1035" />
<ScalarOperator ScalarString="[Expr1035]">
<Identifier>
<ColumnReference Column="Expr1035" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="66" EstimateCPU="1.00021" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="239286" LogicalOp="Left Outer Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="60412.5">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
<ColumnReference Column="Expr1035" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OuterReferences>
<RelOp AvgRowSize="70" EstimateCPU="1.00021" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="239286" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="60242.8">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
<ColumnReference Column="Expr1033" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OuterReferences>
<RelOp AvgRowSize="65" EstimateCPU="3.13083" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="239286" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="100.874">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OutputList>
<MemoryFractions Input="0.528633" Output="0.891626" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[EVENT_ID]=[Perform_Clustern_PODS_50000_S].[dbo].[ILI_DATA].[EVENT_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="34" EstimateCPU="1.92183" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="42770.6" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="19.0555">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OutputList>
<MemoryFractions Input="0.891626" Output="0.362993" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="STATION_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_END]=[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[STATION_ID] as [STATION_POINT_2].[STATION_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="34" EstimateCPU="0.470634" EstimateIO="12.2061" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="42770.6" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="12.6767">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="1" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_BEGIN]=[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_END]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="34" EstimateCPU="0.154483" EstimateIO="4.0972" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="140296" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="4.25168">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="STATION_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="STATION_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[STATION_POINT_2]" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="65" EstimateCPU="1.56144" EstimateIO="76.4446" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="239650" LogicalOp="Clustered Index Scan" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="78.006">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_DATA_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="LENGTH" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="WIDTH" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ORIENTATION_DEG" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="NOMINAL_WALL_THICKNESS" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="PIPE_SMYS" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="B31G_MAOP" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_DATA].[ILI_INSPECTION_ID]=(8.)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_INSPECTION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(8.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="12" EstimateCPU="0.000258211" EstimateIO="0.003125" EstimateRebinds="239277" EstimateRewinds="8.04378" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="10" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="60141">
<OutputList>
<ColumnReference Column="Expr1033" />
</OutputList>
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
<RelOp AvgRowSize="12" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="11" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="60078.8">
<OutputList>
<ColumnReference Column="Expr1033" />
</OutputList>
<Assert StartupExpression="false">
<RelOp AvgRowSize="20" EstimateCPU="0.00286699" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="12" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="60078.8">
<OutputList>
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1033" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1032" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1033" />
<ScalarOperator ScalarString="ANY([Perform_Clustern_PODS_50000_S].[dbo].[PIPE_SEGMENT].[NOMINAL_DIAMETER_GCL])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="12" EstimateCPU="0.171617" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="4777.49" LogicalOp="Inner Join" NodeId="13" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="60002.9">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[STATION_ID] as [s1].[STATION_ID]=[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_BEGIN]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="21" EstimateCPU="0.232005" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="4850.91" LogicalOp="Inner Join" NodeId="14" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="55174.5">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_END]=[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[STATION_ID] as [s2].[STATION_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="16" EstimateCPU="0.149813" EstimateIO="5.00518" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="8417.76" LogicalOp="Eager Spool" NodeId="15" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="287.418">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</OutputList>
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="ROUTE_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekPredicate>
<RelOp AvgRowSize="34" EstimateCPU="0.154483" EstimateIO="4.0972" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="140296" LogicalOp="Clustered Index Scan" NodeId="16" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="4.25168">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="STATION_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s2]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s2]" />
</IndexScan>
</RelOp>
</Spool>
</RelOp>
<RelOp AvgRowSize="30" EstimateCPU="0.0471295" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="26458.6" EstimateRows="10571.4" LogicalOp="Inner Join" NodeId="17" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="48748.3">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<NestedLoops Optimized="false" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
<ColumnReference Column="Expr1040" />
</OuterReferences>
<RelOp AvgRowSize="21" EstimateCPU="0.0125595" EstimateIO="0.133495" EstimateRebinds="0" EstimateRewinds="26458.6" EstimateRows="11275" LogicalOp="Clustered Index Scan" NodeId="19" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="332.453">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="NOMINAL_DIAMETER_GCL" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Index="[PK_PIPE_SEGMENT]" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="298332000" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="20" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="47168.8">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="2" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[PIPE_SEGMENT].[EVENT_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[PIPE_SEGMENT]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.149813" EstimateIO="5.00518" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="8417.76" LogicalOp="Eager Spool" NodeId="22" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="287.418">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</OutputList>
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="ROUTE_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekPredicate>
<RelOp AvgRowSize="34" EstimateCPU="0.154483" EstimateIO="4.0972" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="140296" LogicalOp="Clustered Index Scan" NodeId="23" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="4.25168">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="STATION_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s1]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s1]" />
</IndexScan>
</RelOp>
</Spool>
</RelOp>
</Hash>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1032]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1032" />
</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>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="12" EstimateCPU="0.000258211" EstimateIO="0.003125" EstimateRebinds="239277" EstimateRewinds="8.04378" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="29" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="168.68">
<OutputList>
<ColumnReference Column="Expr1035" />
</OutputList>
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
<RelOp AvgRowSize="12" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="30" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="106.503">
<OutputList>
<ColumnReference Column="Expr1035" />
</OutputList>
<Assert StartupExpression="false">
<RelOp AvgRowSize="20" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="31" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="106.499">
<OutputList>
<ColumnReference Column="Expr1034" />
<ColumnReference Column="Expr1035" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1034" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1035" />
<ScalarOperator ScalarString="ANY([Perform_Clustern_PODS_50000_S].[dbo].[ILI_PIPE_LENGTH].[MEASURED_WALL_THICKNESS])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="32" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="106.469">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</OuterReferences>
<RelOp AvgRowSize="21" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="34" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="55.3994">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OuterReferences>
<RelOp AvgRowSize="30" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="26458.6" EstimateRows="1" LogicalOp="Inner Join" NodeId="36" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="4.32938">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<NestedLoops Optimized="true">
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</OuterReferences>
<RelOp AvgRowSize="21" EstimateCPU="7.96E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="26458.6" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="38" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0323876">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="MEASURED_WALL_THICKNESS" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Index="[PK_ILI_PIPE_LENGTH]" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="26458.6" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="39" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="4.18639">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Index="[PK_EVENT_RANGE]" TableReferenceId="3" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="EVENT_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_PIPE_LENGTH].[EVENT_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_PIPE_LENGTH]" Column="EVENT_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="40" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="50.9361">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s4]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="STATION_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_END]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_END" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [s4].[ROUTE_ID]=[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID] AND [Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [s4].[MEASURE]>[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s4]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="25" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="26458.6" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="41" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="50.9361">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Index="[PK_STATION_POINT]" Alias="[s3]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="STATION_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[EVENT_RANGE].[STATION_ID_BEGIN]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[EVENT_RANGE]" Column="STATION_ID_BEGIN" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [s3].[ROUTE_ID]=[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[ROUTE_ID] as [STATION_POINT_2].[ROUTE_ID] AND [Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [s3].[MEASURE]<[Perform_Clustern_PODS_50000_S].[dbo].[STATION_POINT].[MEASURE] as [STATION_POINT_2].[MEASURE]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="ROUTE_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[s3]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[STATION_POINT]" Alias="[STATION_POINT_2]" Column="MEASURE" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1034]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1034" />
</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>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

----------------------

the plan for delete and 100 data records (bad times):

------------

< 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.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="11.5922" StatementText="DELETE FROM ILI_CLUSTER FROM ILI_CLUSTER where ILI_CLUSTER.ILI_Inspection_ID =22" StatementType="DELETE">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="27">
<MissingIndexes>
<MissingIndexGroup Impact="85.9117">
<MissingIndex Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[ILI_CLUSTER_ID]" ColumnId="57" />
<Column Name="[ILI_CLUSTER_GUID]" ColumnId="58" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="9" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="1" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="11.5922">
<OutputList />
<Assert StartupExpression="false">
<RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="11.5922">
<OutputList>
<ColumnReference Column="Expr1014" />
<ColumnReference Column="Expr1015" />
</OutputList>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1015" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1015" />
</ProbeColumn>
<RelOp AvgRowSize="17" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="11.1241">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Column="Expr1014" />
</OutputList>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1014" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1014" />
</ProbeColumn>
<RelOp AvgRowSize="54" EstimateCPU="2E-06" EstimateIO="0.02" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Delete" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="1.16113">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OutputList>
<Update>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[PK_ILI_CLUSTER]" />
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[AK_UK_ILI_CLUSTER_ILI_ILI_CLUS]" />
<RelOp AvgRowSize="16" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="5" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.14113">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OutputList>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="25" EstimateCPU="0.037337" EstimateIO="1.08757" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.12491">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OutputList>
<IndexScan Ordered="true" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[PK_ILI_CLUSTER]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_CLUSTER].[ILI_INSPECTION_ID]=(22.)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_INSPECTION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(22.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
<RelOp AvgRowSize="54" EstimateCPU="0.172813" EstimateIO="9.65201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="9.82483">
<OutputList />
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_DATA].[ILI_CLUSTER_ID]=[Perform_Clustern_PODS_100].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_ID] AND [Perform_Clustern_PODS_100].[dbo].[ILI_DATA].[ILI_CLUSTER_GUID]=[Perform_Clustern_PODS_100].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_GUID]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_GUID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="16" EstimateCPU="0.172813" EstimateIO="9.65201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.464606">
<OutputList />
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_100].[dbo].[ILI_DATA].[ILI_CLUSTER_ID]=[Perform_Clustern_PODS_100].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_100]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN NOT [Expr1014] IS NULL THEN (0) ELSE CASE WHEN NOT [Expr1015] IS NULL THEN (1) ELSE NULL END END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1014" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1015" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

----------

the plan for delete and 50.000 data rows

-------

< 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.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="39564.9" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="419.029" StatementText="DELETE FROM ILI_CLUSTER FROM ILI_CLUSTER where ILI_CLUSTER.ILI_Inspection_ID =8" StatementType="DELETE">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="30">
<RelOp AvgRowSize="9" EstimateCPU="0.00712168" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39564.9" LogicalOp="Assert" NodeId="1" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="419.029">
<OutputList />
<Assert StartupExpression="false">
<RelOp AvgRowSize="9" EstimateCPU="0.183757" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="39564.9" LogicalOp="Left Semi Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="419.022">
<OutputList>
<ColumnReference Column="Expr1015" />
</OutputList>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1015" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1015" />
</ProbeColumn>
<RelOp AvgRowSize="54" EstimateCPU="0.043961" EstimateIO="1.9621" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43961" LogicalOp="Delete" NodeId="3" Parallel="false" PhysicalOp="Index Delete" EstimatedTotalSubtreeCost="142.747">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OutputList>
<Update WithOrderedPrefetch="true">
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[AK_UK_ILI_CLUSTER_ILI_ILI_CLUS]" />
<RelOp AvgRowSize="54" EstimateCPU="0.00791298" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43961" LogicalOp="Assert" NodeId="5" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="140.741">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OutputList>
<Assert StartupExpression="false">
<RelOp AvgRowSize="55" EstimateCPU="0.204174" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43961" LogicalOp="Left Semi Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="140.733">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
<ColumnReference Column="Expr1014" />
</OutputList>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1014" />
</DefinedValue>
</DefinedValues>
<OuterReferences>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1014" />
</ProbeColumn>
<RelOp AvgRowSize="54" EstimateCPU="0.0488455" EstimateIO="12.2747" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="48845.5" LogicalOp="Delete" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="17.7008">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</OutputList>
<Update>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[PK_ILI_CLUSTER]" />
<RelOp AvgRowSize="16" EstimateCPU="0.00488455" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="48845.5" LogicalOp="Top" NodeId="8" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="5.37728">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OutputList>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="25" EstimateCPU="0.202986" EstimateIO="5.0809" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="48845.5" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="5.28389">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Index="[PK_ILI_CLUSTER]" />
<Predicate>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_CLUSTER].[ILI_INSPECTION_ID]=(8.)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_INSPECTION_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(8.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="1.41978" EstimateIO="28.6347" EstimateRebinds="48816.9" EstimateRewinds="27.6594" EstimateRows="16.5831" LogicalOp="Eager Spool" NodeId="11" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="122.828">
<OutputList />
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
<RelOp AvgRowSize="16" EstimateCPU="1.56144" EstimateIO="76.4446" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1419340" LogicalOp="Clustered Index Scan" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="78.006">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
</IndexScan>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN NOT [Expr1014] IS NULL THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1014" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</Update>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="1.4196" EstimateIO="73.7834" EstimateRebinds="43960" EstimateRewinds="0" EstimateRows="1" LogicalOp="Eager Spool" NodeId="14" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="276.091">
<OutputList />
<Spool>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_GUID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_ID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Perform_Clustern_PODS_50000_S].[dbo].[ILI_CLUSTER].[ILI_CLUSTER_GUID]">
<Identifier>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_CLUSTER]" Column="ILI_CLUSTER_GUID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
<RelOp AvgRowSize="54" EstimateCPU="1.56144" EstimateIO="76.4446" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1419340" LogicalOp="Clustered Index Scan" NodeId="15" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="78.006">
<OutputList>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_GUID" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Column="ILI_CLUSTER_GUID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Perform_Clustern_PODS_50000_S]" Schema="[dbo]" Table="[ILI_DATA]" Index="[PK_ILI_DATA]" />
</IndexScan>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN NOT [Expr1015] IS NULL THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1015" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

-----

finchen

Is this too much

please help me

finchen

The query with good times create or use a worktable (Index Spool), is this perhaps the reason why I get so good times. Could this adulterate the times

WesleyB

Have you noticed the following missing index warnings:

For the third plan:

For the first plan:

Now I wouldn't blindly create these indexes (especially the second one is quite large but it might make sense depending on your specific situation). I would try to create the first one to start with anyway.

WesleyB

Visit my SQL Server weblog @ http://dis4ea.blogspot.com

finchen

I think that is not the problem, sometimes he messing a index and sometomes he don't missing the index in the database with the bad times. Can there still be present other reasons Do the execution plan use only the statistics to build the best plan Are the four plans are very different

WesleyB

The query optimizer takes into account a lot of factors to create a query plan. Statistics is one the aspects it takes into account.

But nevertheless, bad query may become good queries with the right indexes.

WesleyB

Visit my SQL Server weblog @ http://dis4ea.blogspot.com

Performance Problems

Re: Performance Problems

Re: Performance Problems

Re: Performance Problems

Re: Performance Problems

Re: Performance Problems

Re: Performance Problems

Re: Performance Problems

Re: Performance Problems

Re: Performance Problems

Re: Performance Problems

Re: Performance Problems