Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизация запроса - ошибки оптимизатора  [new]
ээха
Guest
Есть некое представление, в котором выполняется некий select. Если написать запрос из этого представления с t, например:

select top 1000 * from dbo.t


то оптимизатор зачем-то строит такой вот план (план изменен с помощью Anonymizer):

+ Вот такой вот план

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.2100.60">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="Statement1" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="1.30611" StatementEstRows="1000" StatementOptmLevel="FULL" QueryHash="0xA395F946DF2FCCDE" QueryPlanHash="0xC784651E9C31DBFE">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan DegreeOfParallelism="1" MemoryGrant="69320" CachedPlanSize="80" CompileTime="30" CompileCPU="30" CompileMemory="1320">
<Warnings>
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varbinary(16),[Union1029],0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varbinary(16),[Union1028],0)" />
</Warnings>
<MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="69320" RequiredMemory="640" DesiredMemory="69320" RequestedMemory="69320" GrantWaitTime="0" GrantedMemory="69320" MaxUsedMemory="69160" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209703" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.0001" AvgRowSize="227" EstimatedTotalSubtreeCost="1.30611" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Column="Expr1036" />
<ColumnReference Column="Expr1040" />
<ColumnReference Column="Expr1041" />
<ColumnReference Column="Expr1042" />
<ColumnReference Column="Expr1043" />
<ColumnReference Column="Expr1044" />
<ColumnReference Column="Expr1045" />
<ColumnReference Column="Expr1046" />
<ColumnReference Column="Expr1047" />
<ColumnReference Column="Expr1048" />
<ColumnReference Column="Expr1049" />
<ColumnReference Column="Expr1050" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1040" />
<ScalarOperator ScalarString="ScalarString1">
<Convert DataType="uniqueidentifier" Style="0" Implicit="0">
<ScalarOperator>
<Intrinsic FunctionName="hashbytes">
<ScalarOperator>
<Const ConstValue="Value1" />
</ScalarOperator>
<ScalarOperator>
<Arithmetic Operation="ADD">
<ScalarOperator>
<Convert DataType="varbinary" Length="16" Style="0" Implicit="0">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1029" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Convert DataType="varbinary" Length="16" Style="0" Implicit="0">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1028" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1041" />
<ScalarOperator ScalarString="ScalarString2">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table1" Alias="Alias1" Column="Column1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value2" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="Value2" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Convert DataType="uniqueidentifier" Style="0" Implicit="0">
<ScalarOperator>
<Intrinsic FunctionName="hashbytes">
<ScalarOperator>
<Const ConstValue="Value1" />
</ScalarOperator>
<ScalarOperator>
<Arithmetic Operation="ADD">
<ScalarOperator>
<Convert DataType="varbinary" Length="16" Style="0" Implicit="0">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1029" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Convert DataType="varbinary" Length="16" Style="0" Implicit="0">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1028" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1042" />
<ScalarOperator ScalarString="ScalarString3">
<Identifier>
<ColumnReference Column="Expr1034" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1043" />
<ScalarOperator ScalarString="ScalarString4">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1035" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value3" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1044" />
<ScalarOperator ScalarString="ScalarString5">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1031" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value3" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1045" />
<ScalarOperator ScalarString="ScalarString6">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1032" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value3" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1046" />
<ScalarOperator ScalarString="ScalarString7">
<Convert DataType="nvarchar" Length="100" Style="0" Implicit="0">
<ScalarOperator>
<Arithmetic Operation="ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1053" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Convert DataType="nvarchar" Length="22" Style="0" Implicit="0">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1039" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1047" />
<ScalarOperator ScalarString="ScalarString8">
<Const ConstValue="Value4" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1048" />
<ScalarOperator ScalarString="ScalarString9">
<Convert DataType="uniqueidentifier" Style="0" Implicit="0">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1028" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1050" />
<ScalarOperator ScalarString="ScalarString10">
<Const ConstValue="Value2" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="1" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.0001" AvgRowSize="186" EstimatedTotalSubtreeCost="1.30601" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Column="Union1028" />
<ColumnReference Column="Union1029" />
<ColumnReference Column="Expr1031" />
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1034" />
<ColumnReference Column="Expr1035" />
<ColumnReference Column="Expr1036" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table1" Alias="Alias1" Column="Column1" />
<ColumnReference Column="Expr1039" />
<ColumnReference Column="Expr1049" />
<ColumnReference Column="Expr1053" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="0" IsPercent="0" WithTies="0">
<TopExpression>
<ScalarOperator ScalarString="ScalarString11">
<Const ConstValue="Value5" />
</ScalarOperator>
</TopExpression>
<RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.0057549" AvgRowSize="186" EstimatedTotalSubtreeCost="1.30591" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Column="Union1028" />
<ColumnReference Column="Union1029" />
<ColumnReference Column="Expr1031" />
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1034" />
<ColumnReference Column="Expr1035" />
<ColumnReference Column="Expr1036" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table1" Alias="Alias1" Column="Column1" />
<ColumnReference Column="Expr1039" />
<ColumnReference Column="Expr1049" />
<ColumnReference Column="Expr1053" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1049" />
<ScalarOperator ScalarString="ScalarString12">
<Convert DataType="uniqueidentifier" Style="0" Implicit="0">
<ScalarOperator>
<Identifier>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column2" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1053" />
<ScalarOperator ScalarString="ScalarString13">
<Arithmetic Operation="ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column3" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value6" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="3" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.00460392" AvgRowSize="185" EstimatedTotalSubtreeCost="1.30581" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column2" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column3" />
<ColumnReference Column="Union1028" />
<ColumnReference Column="Union1029" />
<ColumnReference Column="Expr1031" />
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1034" />
<ColumnReference Column="Expr1035" />
<ColumnReference Column="Expr1036" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table1" Alias="Alias1" Column="Column1" />
<ColumnReference Column="Expr1039" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<SequenceProject>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1039" />
<ScalarOperator ScalarString="ScalarString14">
<Sequence FunctionName="row_number" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="4" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.00115098" AvgRowSize="185" EstimatedTotalSubtreeCost="1.30573" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column2" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column3" />
<ColumnReference Column="Union1028" />
<ColumnReference Column="Union1029" />
<ColumnReference Column="Expr1031" />
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1034" />
<ColumnReference Column="Expr1035" />
<ColumnReference Column="Expr1036" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table1" Alias="Alias1" Column="Column1" />
<ColumnReference Column="Column4" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Segment>
<GroupBy />
<SegmentColumn>
<ColumnReference Column="Column4" />
</SegmentColumn>
<RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.0057549" AvgRowSize="177" EstimatedTotalSubtreeCost="1.30571" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column2" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column3" />
<ColumnReference Column="Union1028" />
<ColumnReference Column="Union1029" />
<ColumnReference Column="Expr1031" />
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1034" />
<ColumnReference Column="Expr1035" />
<ColumnReference Column="Expr1036" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table1" Alias="Alias1" Column="Column1" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1034" />
<ScalarOperator ScalarString="ScalarString15">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1030" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value3" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1035" />
<ScalarOperator ScalarString="ScalarString16">
<Arithmetic Operation="ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1032" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1031" />
</Identifier>
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1036" />
<ScalarOperator ScalarString="ScalarString17">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1033" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value7" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="Value7" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1033" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value3" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.240555" AvgRowSize="160" EstimatedTotalSubtreeCost="1.30561" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column2" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column3" />
<ColumnReference Column="Union1028" />
<ColumnReference Column="Union1029" />
<ColumnReference Column="Expr1030" />
<ColumnReference Column="Expr1031" />
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1033" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table1" Alias="Alias1" Column="Column1" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Column="Union1028" />
</OuterReferences>
<RelOp NodeId="7" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.195729" AvgRowSize="143" EstimatedTotalSubtreeCost="1.14005" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column2" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column3" />
<ColumnReference Column="Union1028" />
<ColumnReference Column="Union1029" />
<ColumnReference Column="Expr1030" />
<ColumnReference Column="Expr1031" />
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1033" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="0">
<InnerSideJoinColumns>
<ColumnReference Column="Union1029" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column2" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="ScalarString18">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column2" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1029" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp NodeId="8" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="525.604" EstimateIO="0.600903" EstimateCPU="0.0334298" AvgRowSize="47" EstimatedTotalSubtreeCost="0.014228" TableCardinality="30248" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column2" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column3" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1012" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="1" ScanDirection="BACKWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column2" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table2" Alias="Alias2" Column="Column3" />
</DefinedValue>
</DefinedValues>
<Object Database="Database1" Schema="Schema1" Table="Table2" Index="Index1" Alias="Alias2" IndexKind="Clustered" />
</IndexScan>
</RelOp>
<RelOp NodeId="9" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.311894" AvgRowSize="103" EstimatedTotalSubtreeCost="1.11125" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Column="Union1028" />
<ColumnReference Column="Union1029" />
<ColumnReference Column="Expr1030" />
<ColumnReference Column="Expr1031" />
<ColumnReference Column="Expr1032" />
<ColumnReference Column="Expr1033" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1030" />
<ScalarOperator ScalarString="ScalarString19">
<Aggregate Distinct="0" AggType="SUM">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1016" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1031" />
<ScalarOperator ScalarString="ScalarString20">
<Aggregate Distinct="0" AggType="SUM">
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1021" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value8" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1012" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="Value3" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1032" />
<ScalarOperator ScalarString="ScalarString21">
<Aggregate Distinct="0" AggType="SUM">
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1021" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="Value9" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1012" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="Value3" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1033" />
<ScalarOperator ScalarString="ScalarString22">
<Aggregate Distinct="0" AggType="MAX">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Union1013" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<GroupBy>
<ColumnReference Column="Union1029" />
<ColumnReference Column="Union1028" />
</GroupBy>
<RelOp NodeId="10" PhysicalOp="Merge Join" LogicalOp="Concatenation" EstimateRows="8199.36" EstimateIO="0" EstimateCPU="0.0527907" AvgRowSize="79" EstimatedTotalSubtreeCost="1.10583" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Column="Union1029" />
<ColumnReference Column="Union1028" />
<ColumnReference Column="Union1012" />
<ColumnReference Column="Union1013" />
<ColumnReference Column="Union1016" />
<ColumnReference Column="Union1021" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4848" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Merge>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Union1029" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column2" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column2" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1028" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column5" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column5" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1012" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column6" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column6" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1013" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column7" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column7" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1016" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column8" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column8" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1021" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column9" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column9" />
</DefinedValue>
</DefinedValues>
<RelOp NodeId="11" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100079" AvgRowSize="79" EstimatedTotalSubtreeCost="0.0146444" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column6" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column7" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column8" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column9" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column5" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column2" />
</OutputList>
<MemoryFractions Input="1" Output="0.000465929" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="0">
<OrderBy>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column2" />
</OrderByColumn>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column5" />
</OrderByColumn>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column6" />
</OrderByColumn>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column7" />
</OrderByColumn>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column8" />
</OrderByColumn>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column9" />
</OrderByColumn>
</OrderBy>
<RelOp NodeId="12" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="79" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column6" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column7" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column8" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column9" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column5" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column2" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column6" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column7" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column8" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column9" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column5" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table3" Alias="Alias3" Column="Column2" />
</DefinedValue>
</DefinedValues>
<Object Database="Database1" Schema="Schema1" Table="Table3" Index="Index2" Alias="Alias3" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Sort>
</RelOp>
<RelOp NodeId="13" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="8198.36" EstimateIO="0.0112613" EstimateCPU="40.8222" AvgRowSize="79" EstimatedTotalSubtreeCost="1.30611" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column6" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column7" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column8" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column9" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column5" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column2" />
</OutputList>
<Warnings>
<SpillToTempDb SpillLevel="1" />
</Warnings>
<MemoryFractions Input="0.999534" Output="0.999534" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4848" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="0">
<OrderBy>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column2" />
</OrderByColumn>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column5" />
</OrderByColumn>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column6" />
</OrderByColumn>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column7" />
</OrderByColumn>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column8" />
</OrderByColumn>
<OrderByColumn Ascending="1">
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column9" />
</OrderByColumn>
</OrderBy>
<RelOp NodeId="14" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="471864" EstimateIO="18.4135" EstimateCPU="0.519207" AvgRowSize="79" EstimatedTotalSubtreeCost="1.30611" TableCardinality="471864" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column6" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column7" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column8" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column9" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column5" />
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column2" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="471864" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column6" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column7" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column8" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column9" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column5" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table4" Alias="Alias3" Column="Column2" />
</DefinedValue>
</DefinedValues>
<Object Database="Database1" Schema="Schema1" Table="Table4" Index="Index3" Alias="Alias3" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</StreamAggregate>
</RelOp>
</Merge>
</RelOp>
<RelOp NodeId="25" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="23" EstimatedTotalSubtreeCost="0.161383" TableCardinality="25" Parallel="0" EstimateRebinds="417.244" EstimateRewinds="582.755" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table1" Alias="Alias1" Column="Column1" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000" ActualEndOfScans="0" ActualExecutions="1000" />
</RunTimeInformation>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table1" Alias="Alias1" Column="Column1" />
</DefinedValue>
</DefinedValues>
<Object Database="Database1" Schema="Schema1" Table="Table1" Index="Index4" Alias="Alias1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="Database1" Schema="Schema1" Table="Table1" Alias="Alias1" Column="Column10" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="ScalarString23">
<Identifier>
<ColumnReference Column="Union1028" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</Segment>
</RelOp>
</SequenceProject>
</RelOp>
</ComputeScalar>
</RelOp>
</Top>
</RelOp>
</ComputeScalar>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>


Из него видно, что оптимизатор в самом начале пытается отсортировать 5000 записей и подтянуть поля, в следствии чего возникает spill.
Такой запрос отрабатывает около 3 секунд.
29 окт 13, 17:44    [15048205]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
эха
Guest
Spill исчезает, запрос отрабатывает ~ 1 сек (т.е. очень грубо - в 2-3 раза быстрее).

Очень хочется избавиться в этом запросе от spill, но при этом запрос такого рода будет вызываться тоже в представлении и от этого никуда не деться. Соответственно option (force order) я там использовать не смогу. Уважаемые участники, подскажите пожалуйста как вразумить оптимизатор.

Если нужно, могу выложить структуру представления dbo.t

P.S. Очень жду ответа Somewhere somehow :-)

select @@version
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
	Feb 10 2012 19:39:15 
29 окт 13, 17:45    [15048216]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
эхха
Guest
План без spill во вложении

К сообщению приложен файл (plan2.xml - 88Kb) cкачать
29 окт 13, 17:46    [15048224]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
эхха
Guest
Картинкой первый план во вложении

К сообщению приложен файл. Размер - 39Kb
29 окт 13, 17:47    [15048226]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
эхха
Guest
Картинкой второй план (без spill)

К сообщению приложен файл. Размер - 48Kb
29 окт 13, 17:48    [15048231]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
ээхха
Guest
План1

К сообщению приложен файл (select1.sqlplan - 69Kb) cкачать
29 окт 13, 17:53    [15048262]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
ээхха
Guest
План2

К сообщению приложен файл (select2.sqlplan - 88Kb) cкачать
29 окт 13, 17:53    [15048266]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
вы статистику давно обновляли ?
29 окт 13, 17:55    [15048274]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
komrad
Member

Откуда:
Сообщений: 5739
эха,

можно сделать plan guide с опцией optimize for ...
29 окт 13, 18:08    [15048319]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
эха
Guest
Maxx,

обновлял недавно. причем и по таблицам, по которым оптимизатор делает merge, и по другим таблицам из этого запроса.
Не помогло.
29 окт 13, 18:18    [15048358]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Можно начать отсюда
29 окт 13, 18:28    [15048403]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
А еще патчить сервер надо,у вас RTM
29 окт 13, 18:32    [15048427]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
эха,

Привет.
Чтобы дать совет, нужно понять что происходит. Но глядя на план, возникают вопросы:
+ Вопросы к плану
Картинка с другого сайта.

1) Косты (стоимости) узлов не бются. Есть такая известная багофича, когда косты не бъются. Это происходит в случае, если оптимизатор принимает решение, что все необходимое он получит из одной ветки, тогда вторую он не включает в костинг верхнего узла. В данном случае, на это не похоже. Запрашивается 1000 строк, оценка верхней таблицы 1 строка, он должен был включить кост и второй (нижней ветки). Кроме того, видно что расхождение в косте одного узла, слагаемые не совпадают с суммой.
2) Есть пропуски в нумерации узлов. Есть такая багофича, как скрытые операторы, в плане они не показываются но, на самом деле они есть. Их всего несколько, поэтому трудно представить что скрыто 11 узлов.
3) Присутствует top, если он есть, оценки идут немного по-другому - возможно, это вносит какой-то импакт.

В связи с вышесказанным, могу только предположить, что если план не правился руками(?) и ничего не напортачил анонимизатор, то это какой-то баг. В таком случае, анонимных планов мало, чтобы найти причину. Нужно репро - структуры таблиц/индексов, слепок статистик, запросы. Попробуйте накатить сервиспак, как уже посоветовали. Ну а если найдется время, то публикуйте репро - будем ковырять.

Что касается спила - пока так много странностей, трудно сказать в чем именно причина. Видно, что в плане со спилом - сортировка принимает много стобцов, в плане без - всего один. Если среди сортируемых столбцов есть varchar особенно если несколько, то может быть так, что оптимизатор неверно рассчитывает память, отсюда спил. Можно попробовать сделать искусственное преобразование в char, только нужно добиться чтобы оно выполнялось до сортировки. Посмотрите sql workshops ramesh meyappan - там описывается эта техника.
Если бы была возможность хинтов, можно было бы попробовать поиграть с параметром в top, есть такой прием "declare @n int = 1000; select top(@n) .... optimize for @n = 10000" -- <-- значение, которое повысит оценку к требуемой памяти и поможет избежать спила.

Но сперва бы я со странностями в плане разобрался конечно.

З.Ы.
Сейчас на форум времени почти нет, поэтому, просматривать темы я просматриваю, но отвечать особо некогда.
29 окт 13, 22:24    [15049321]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
эха
Spill исчезает, запрос отрабатывает ~ 1 сек (т.е. очень грубо - в 2-3 раза быстрее).

Очень хочется избавиться в этом запросе от spill, но при этом запрос такого рода будет вызываться тоже в представлении и от этого никуда не деться. Соответственно option (force order) я там использовать не смогу. Уважаемые участники, подскажите пожалуйста как вразумить оптимизатор.

Если нужно, могу выложить структуру представления dbo.t

P.S. Очень жду ответа Somewhere somehow :-)

select @@version
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
	Feb 10 2012 19:39:15 
Перечитал 3 раза, но так и не понял, что изменилось в самом запросе что ушел spill?
30 окт 13, 00:45    [15049896]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
эхха
Guest
Mind

[/src]
Перечитал 3 раза, но так и не понял, что изменилось в самом запросе что ушел spill?[/quot]

Сорри, пока разбивал длинное сообщение на несколько, потерялись запросы.

Запрос 1 (со спилом и таким странным поведением):
select top 1000 * from dbo.t



Запрос 2 (который без спила):
select top 1000 * from dbo.t
option (force order) 


Попробовал запустить на другой версии 2012 сервера:
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 


Как раз с накатанным сервис паком, результат тот же (планы те же).


SomewhereSomehow
Есть пропуски в нумерации узлов. Есть такая багофича, как скрытые операторы, в плане они не показываются но, на самом деле они есть. Их всего несколько, поэтому трудно представить что скрыто 11 узлов.

А есть какой-нибудь трэйс, который раскрывает все узлы?


SomewhereSomehow
В связи с вышесказанным, могу только предположить, что если план не правился руками(?) и ничего не напортачил анонимизатор, то это какой-то баг. В таком случае, анонимных планов мало, чтобы найти причину. Нужно репро - структуры таблиц/индексов, слепок статистик, запросы. Попробуйте накатить сервиспак, как уже посоветовали. Ну а если найдется время, то публикуйте репро - будем ковырять.

Анонимайзер не напортачил. Я постараюсь сделать репо, но получится большая структура, боюсь это не для обсуждения на форуме (просто банально в таком большом запросе разбираться никто не будет). Постараюсь ужать и выложить позже.
30 окт 13, 11:35    [15051413]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
эхха
Guest
Интересно, на патченом сервере (2012 SP1) на такой запрос:

select top 100 * from dbo.toption (force order) 


Получил такой вот план.
Косты вроде бьются теперь нормально и время приемлемое - 3 секунды, вместо 43.

К сообщению приложен файл (plan_sp1_without_spill.sqlplan - 71Kb) cкачать
30 окт 13, 11:38    [15051438]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
эха,

Я-таки смог сделать репро по плану.
План получается почти такой же.
Репро данные
create database costtest;
go
use costtest;

create table t3 (c0 int identity primary key, c1 int, c2 int, c3 varchar(40), c4 varchar(40), c5 varchar(40), c6 varchar(40));
create table t4 (c0 int identity primary key, c1 int, c2 int, c3 varchar(40), c4 varchar(40), c5 varchar(40), c6 varchar(40));

with cte as
(
	select top(471864) rn=row_number() over(order by(select 1)) from master..spt_values v1,master..spt_values v2,master..spt_values v3
)
insert t4(c1,c2,c3,c4,c5,c6)
select rn,rn%25+1,replicate('a',10),replicate('a',10),replicate('a',10),replicate('a',10) from cte;

create table t2 (c1 int not null, c2 varchar(40));
create clustered index ix_c1 on t2(c1 desc); 
insert t2(c1)
select top(30248) c1%1124 from t4 order by c1;

create table t1 (c1 int primary key);
insert t1 (c1)
select top(25) c1 from t4 order by c1;


+ Репро скрипт
with cte as
(
select c1,c2,c3,c4,c5,c6 from t3
union all
select c1,c2,c3,c4,c5,c6 from t4
),
cte2 as
(
select cte.c1, cte.c2 from cte group by cte.c1, cte.c2
)
select top(1000)
	*,
	row_number() over(order by cte2.c1)
from 
	t2
	join cte2 on cte2.c1 = t2.c1
	left join t1 on cte2.c2 = t1.c1


План:
Картинка с другого сайта.
Plan Explorer, при этом, прикидывается приличным человеком и показывает похожий на правильный кост (видимо у него свой механизм отображения), хотя уже в следующем операторе сортировке тоже сбивается.
А вот какую прелесть выдает SQL Server 2014 CTP2
Картинка с другого сайта.
Это показывается и в SSMS2012 и в SSMS2014 CTP2.
Единственное что, пропусков в нумерации узлов у меня не получилось. Непонятно, почему они у вас возникли.
То что я написал в предыдущем посте применим - баг с костами, похоже может проявляться и в более общих случаях. Особенно если вовлечен Top (т.е. Row Goal).
Если попробовать увеличить желаемое число строк.
+ Вот так
declare @n int = 1000;
with cte as
(
select c1,c2,c3,c4,c5,c6 from t3
union all
select c1,c2,c3,c4,c5,c6 from t4
),
cte2 as
(
select cte.c1, cte.c2 from cte group by cte.c1, cte.c2
)
select top(@n)
	*,
	row_number() over(order by cte2.c1)
from 
	t2
	join cte2 on cte2.c1 = t2.c1
	left join t1 on cte2.c2 = t1.c1
option(optimize for (@n = 5000))

План получится другой.
Но может быть лучше вообще избавить сервер от необходимости сортировать?
Создать например такой индекс:
create index ix_c1 on t4(c1,c2) include (c3,c4,c5,c6);

Либо рассмотреть возможность кластерного индекса, чтобы не включать много полей в инклуд.
После этого план тоже изменится, сортировки на большой таблице не будет, соответственно спила тоже.

Касательно причины таких странных костов, теперь у меня есть репро, куда копать примерно понятно - поизучаю подробнее, как будет время, и постараюсь, может быть, в блоге что-то написать.
Случай интересный, так что спасибо за вопрос =)
30 окт 13, 11:58    [15051592]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
эхха
А есть какой-нибудь трэйс, который раскрывает все узлы?

Я не знаю о таком, даже в 2014 добавленное представление sys.dm_exec_query_profiles, которое отображает статистику по операторам во время выполнения запроса - не показывает их. Возможно дело тут и не в скрытых узлах, я же говорю, трудно поверить что 11 скрыто - просто я знаю, что пропуски бывают если что-то скрыто, может быть пропуски бывают еще когда-нибудь (может это как-то связано с вьюхами?).

эхха
Анонимайзер не напортачил. Я постараюсь сделать репо, но получится большая структура, боюсь это не для обсуждения на форуме (просто банально в таком большом запросе разбираться никто не будет). Постараюсь ужать и выложить позже.

Репро я вроде сделал, план получился похожий на ваш, главное проблема воспроизводится. А основной виновник это top.
Если интересно, вот нашел сейчас баг на коннекте, про который я вчера говорил.
Закрыт как By Design.
Там есть пример:
CREATE TABLE T1 (X INT)
CREATE TABLE T2 (X INT)
GO
IF EXISTS (SELECT * FROM T1 WHERE X=1)
OR EXISTS (SELECT * FROM T2 WHERE X=1)
PRINT 'A'
GO
DROP TABLE T1,T2

И в случае conditional exists и в случае top действует один и тот же механизм row goal, когда сервер заранее предполагает сколько строк ему понадобится и не собирается читать все строки. Там дано объяснение о том что, оптимизатор предполагает получить из первого условия одну строку и кост второго условия не включает в общий кост поддерева. А также что это скорее артефакт некоторых особенностей плана и никак не влияет на качество запроса:
+
The strange cost percentage values that you observed are an aritifact of the specific structure of the query plan that are a bit confusing but ultimately do make sense. They will not adversely affect the running of the query in any way.

Что-то глядя на этот пример данное высказывание вызывает сомнения. Нужно будет проверить более тщательно.
30 окт 13, 12:27    [15051776]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
эхха
Guest
SomewhereSomehow,

Спасибо за ответ.

SomewhereSomehow
План получится другой.
Но может быть лучше вообще избавить сервер от необходимости сортировать?
Создать например такой индекс:

create index ix_c1 on t4(c1,c2) include (c3,c4,c5,c6);

Очень бы не хотелось еще и такой индекс создавать, он будет очень много места занимать и, главное, мешать в OLTP части (в эту таблицу постоянно вставляются данные).
Тот план, который у меня вышел с Key Lookup, использует индекс ix_c1 on t4(c1) (без include), сейчас пытаюсь принудительно хинтами добиться того же (потому что разница запроса с первоначальным планом и этим в разы).


SomewhereSomehow
Особенно если вовлечен Top (т.е. Row Goal).

К сожалению, я тут ничего сделать тоже не могу, top используется системой, спроектированной не мной :( Даже поменять количество записей тоже не могу.
30 окт 13, 14:06    [15052556]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
эхха
Guest
Еще интересная особенность, подскажите, если кто сталкивался.
Пытаюсь использовать хинт и указать те индексы, которые мне нужны в запросе.

Запрос вида:
select top 10 * from dbo.b
with (nolock, index(idx_cur), index(idx_old))


где dbo.b это вьюха, которая объединяет две таблицы b_cur и b_old:

create view dbo.b
as
select * from dbo.b_cur
union all
select * from dbo.b_old

Соответственно, индекс idx_cur - на таблице dbo.b_cur, а индекс idx_old на таблице dbo.b_old

Если выполнить запрос
select top 10 * from dbo.b
with (nolock, index(idx_cur), index(idx_old))
то несмотря на подсказки будут использованы кластерные индексы.
А вот если вместо вьюхи dbo.b использовать запрос с union, то будут использованы нужные мне индексы:

select top 10 * from
(
select * from dbo.b_cur with (nolock, index(idx_cur))
union
select * from dbo.b_old with (nolock, index(idx_old))
)b


Это нормальное поведение сервера?
30 окт 13, 14:21    [15052706]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса - ошибки оптимизатора  [new]
komrad
Member

Откуда:
Сообщений: 5739
эхха
Это нормальное поведение сервера?


а что даст хинт (noexpand, index ...) при селекте по вьюхе ?
30 окт 13, 14:27    [15052748]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить