I am having problems with SQL Server performance in remote access.
When I use him through a local net
with an application VB is satisfactory, but when
I try to access through a connection ADSL
of 512KB (speedy) it is slow. Former. to open
a screen is long 5 minutes.
The same application with PostgreSql delays nemos of 15 seconds..
It will be that some exists configuration in SQL Server to optimize a remote
access for certain IPS
I thank allI made some simulations with the cursor on the customer's side,
I increased salary size, but didn't give difference none.
Obs.: the application is already optimized to obtain only
the necessary columns, it always accesses for the key it would excel
or indexes, etc...
The interesting is that for PostgreSQL the time of answer
it is very good in accesses through the internet. Perhaps
be for him to have the native access for IP.
I thank the collaboration
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> escreveu na mensagem
news:eVcCdGOYDHA.888@.TK2MSFTNGP10.phx.gbl...
> Make sure you are using a client side cursor - check out my presentation
for
> more information on application cursors and their performance
> comparisons...
> http://www.sqlserverfaq.com?eid=20
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
Showing posts with label net. Show all posts
Showing posts with label net. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Performance Question
Hi, I have a sql2000 server running and I have a asp.net app that accesses this server. At the moment I am using 2 different databases to get information from and such, one database I have a bit of control over the other I am not allowed to touch without special permissions due to the sesitivity of the data. The issue is that my app is not going very fast when it comes to database calls.
I need to index some tables but I am unsure what actually happens to the data when I do this, I have one table that has ~600,000 rows and that is alot to sort through as you can imagine. This table is on the sensitive database, so I am hesitant to touch it, but I really need a performance boost somehow, can anyone suggest something to help me solve my performance problem?
I have posted around on the asp.net forums but the help is limited and most people say 99% of the time its the database calls that are the problem.
Thanks for the time,
MarkIts hard to just suggest anything without knowing your physical and logical design, but if you have many sort operations, try to put indexes on the sorted columns. If you are joining a lot of data, do also put an index on the joined columns. 600.000 is not that much data, perhaps you might also have an issue on your physical design ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the reply Jens, I am quite new to Indexes in SQL Server 2000, I suppose it is always best to use them no matter what database you have, but is there any risk to the data by putting them on? I just dont want to corrupt anything. And as far as I can tell, there are no indexes on any tables in the database that I am allowed to touch.I should mention too, that I am using views to look onto the sensitive database, will this affect permormance?
thanks
Mark|||Actually there is no risks using indexes. If you use them on a separate physical storage location you can also put the load of the indexes from the database (but you don′t have to) The best way would be to evaluate the uqery plans of your queries fired against the database. They should tell you if data is retrieved by using cost extensive scans etc.
HTH, Jens Sü?meyer.
http://www.sqlserver2005.de|||Ok thats good advice thanks. When you say that I should evaluate the query plans, is that using the feature in the management studio that shows the execution plan of a given query? If so what should I be looking for? I dont really understand the details I am being shown
Thanks Jens|||Many sites already discussed about this topic, it would be too much describing the different symbols in here, but have a look on www.sqlserverperformance.com this iste can be a good starting point for finding out ways to tweak the performance of the database.
HTH, Jens Suessmeyer.http://www.sqlserver2005.de
sql
Wednesday, March 21, 2012
Performance Problems
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 H?user 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
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
|||Do you want to see the query plan? On the tables are indexes but I dont know if he use the indexes.
|||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
|||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
|||
One of the nifty features of SSMS.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||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>
--
|||Is this too much?
please help me
|||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?|||Have you noticed the following missing index warnings:
For the third plan:
<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>
For the first plan:
<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>
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
|||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 ?|||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
Subscribe to:
Posts (Atom)