Skip to content

Instantly share code, notes, and snippets.

@hamid67fathi

hamid67fathi/ExecutionPlan-Before.xml

Created December 11, 2016 05:55
Show Gist options
  • Save hamid67fathi/a45cc966ff0c0d5266c89819426d3d6b to your computer and use it in GitHub Desktop.
Save hamid67fathi/a45cc966ff0c0d5266c89819426d3d6b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ExecutionPlan-Before.xml
<?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.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="100" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="29.3247" StatementText="select top(100)&#xD;&#xA; Batch_Tasks_Queue.id,&#xD;&#xA; btq.id&#xD;&#xA;from&#xD;&#xA; Batch_Tasks_Queue with(nolock)&#xD;&#xA; inner join Batch_Tasks_Queue btq with(nolock) on Batch_Tasks_Queue.Start_Time &lt; btq.Start_Time&#xD;&#xA; and btq.Start_Time &lt; Batch_Tasks_Queue.Finish_Time&#xD;&#xA; and Batch_Tasks_Queue.id &lt;&gt; btq.id &#xD;&#xA; and btq.Start_Time is not null&#xD;&#xA; and btq.State in (3, 4) &#xD;&#xA;where&#xD;&#xA; Batch_Tasks_Queue.Start_Time is not null &#xD;&#xA; and Batch_Tasks_Queue.State in (3, 4)&#xD;&#xA; and Batch_Tasks_Queue.Operation_Type = btq.Operation_Type&#xD;&#xA; and Batch_Tasks_Queue.Operation_Type not in (23, 24, 25, 26, 27, 28, 30)&#xD;&#xA; &#xD;&#xA;order by&#xD;&#xA; Batch_Tasks_Queue.Start_Time desc" StatementType="SELECT" QueryHash="0xF44A1E3A62C32D55" QueryPlanHash="0x3DF47AE403DA5461" RetrievedFromCache="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="40" CompileTime="11" CompileCPU="11" CompileMemory="520">
<ThreadStat Branches="1" />
<MissingIndexes>
<MissingIndexGroup Impact="24.1475">
<MissingIndex Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]">
<ColumnGroup Usage="INEQUALITY">
<Column Name="[State]" ColumnId="6" />
<Column Name="[Start_Time]" ColumnId="7" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[Id]" ColumnId="1" />
<Column Name="[Operation_Type]" ColumnId="3" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
<MissingIndexGroup Impact="20.63">
<MissingIndex Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]">
<ColumnGroup Usage="INEQUALITY">
<Column Name="[Operation_Type]" ColumnId="3" />
<Column Name="[State]" ColumnId="6" />
<Column Name="[Start_Time]" ColumnId="7" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[Id]" ColumnId="1" />
<Column Name="[Finish_Time]" ColumnId="8" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="38936" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="384000" EstimatedPagesCached="1536000" EstimatedAvailableDegreeOfParallelism="32" />
<RelOp AvgRowSize="15" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="29.3247">
<OutputList>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Id" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Id" />
</OutputList>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(100)">
<Const ConstValue="(100)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="23" EstimateCPU="0.0287197" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="29.3247">
<OutputList>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Id" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Id" />
</OutputList>
<Parallelism>
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="23" EstimateCPU="281.54" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Inner Join" NodeId="2" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="29.296">
<OutputList>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Id" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Id" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Id" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Finish_Time" />
</OuterReferences>
<RelOp AvgRowSize="32" EstimateCPU="1.01625" EstimateIO="0.000351914" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="93.2894" LogicalOp="Sort" NodeId="3" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="7.61534">
<OutputList>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Id" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Finish_Time" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="37" EstimateCPU="0.0140245" EstimateIO="6.54164" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="382071" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="6.55567" TableCardinality="407843">
<OutputList>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Id" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Finish_Time" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Finish_Time" />
</DefinedValue>
</DefinedValues>
<Object Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Index="[PKBachTskQ]" IndexKind="Clustered" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="[NasimDB].[dbo].[Batch_Tasks_Queue].[Operation_Type]&lt;&gt;(23.) AND [NasimDB].[dbo].[Batch_Tasks_Queue].[Operation_Type]&lt;&gt;(24.) AND [NasimDB].[dbo].[Batch_Tasks_Queue].[Operation_Type]&lt;&gt;(25.) AND [NasimDB].[dbo].[Batch_Tasks_Queue].[Operation_Type]&lt;&gt;(26.) AND [NasimDB].[dbo].[Batch_Tasks_Queue].[Operation_Type]&lt;&gt;(27.) AND [NasimDB].[dbo].[Batch_Tasks_Queue].[Operation_Type]&lt;&gt;(28.) AND [NasimDB].[dbo].[Batch_Tasks_Queue].[Operation_Type]&lt;&gt;(30.) AND [NasimDB].[dbo].[Batch_Tasks_Queue].[Start_Time] IS NOT NULL AND ([NasimDB].[dbo].[Batch_Tasks_Queue].[State]=(3.) OR [NasimDB].[dbo].[Batch_Tasks_Queue].[State]=(4.))">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(23.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(24.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(25.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(26.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(27.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(28.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(30.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="State" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(3.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="State" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.00300864" EstimateIO="0" EstimateRebinds="93.2893" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5641.19" LogicalOp="Filter" NodeId="5" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="21.6145">
<OutputList>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Id" />
</OutputList>
<Filter StartupExpression="false">
<RelOp AvgRowSize="11" EstimateCPU="0.409184" EstimateIO="10.8485" EstimateRebinds="93.2893" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6267.99" LogicalOp="Eager Spool" NodeId="6" Parallel="true" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="21.3308">
<OutputList>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Id" />
</OutputList>
<Spool>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Operation_Type" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[NasimDB].[dbo].[Batch_Tasks_Queue].[Operation_Type]">
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Start_Time" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[NasimDB].[dbo].[Batch_Tasks_Queue].[Start_Time]">
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Start_Time" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[NasimDB].[dbo].[Batch_Tasks_Queue].[Finish_Time]">
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Finish_Time" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
<RelOp AvgRowSize="29" EstimateCPU="0.448784" EstimateIO="6.54164" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="402032" LogicalOp="Clustered Index Scan" NodeId="7" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="6.99043" TableCardinality="407843">
<OutputList>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Id" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Operation_Type" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Start_Time" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Operation_Type" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Start_Time" />
</DefinedValue>
</DefinedValues>
<Object Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Index="[PKBachTskQ]" Alias="[btq]" IndexKind="Clustered" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="[NasimDB].[dbo].[Batch_Tasks_Queue].[Start_Time] as [btq].[Start_Time] IS NOT NULL AND ([NasimDB].[dbo].[Batch_Tasks_Queue].[State] as [btq].[State]=(3.) OR [NasimDB].[dbo].[Batch_Tasks_Queue].[State] as [btq].[State]=(4.))">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Start_Time" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="State" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(3.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="State" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4.)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Spool>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="[NasimDB].[dbo].[Batch_Tasks_Queue].[Id]&lt;&gt;[NasimDB].[dbo].[Batch_Tasks_Queue].[Id] as [btq].[Id]">
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</NestedLoops>
</RelOp>
</Parallelism>
</RelOp>
</Top>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment