Skip to content

Instantly share code, notes, and snippets.

@hamid67fathi

hamid67fathi/ExecutionPlan-After.xml

Created December 11, 2016 05:52
Show Gist options
  • Save hamid67fathi/481de9f65965bf3d5275adeebfc93445 to your computer and use it in GitHub Desktop.
Save hamid67fathi/481de9f65965bf3d5275adeebfc93445 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ExecutionPlan-After.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="201.393" StatementText="select top(100)&#xD;&#xA; Batch_Tasks_Queue.id,&#xD;&#xA; btq.id,&#xD;&#xA; Batch_Tasks_Queue.[Parameters], -- this field&#xD;&#xA; btq.[Parameters] -- and this field&#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="0x4ED399E7EB5C7CC2" QueryPlanHash="0x9003CCF53366685F" 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="544">
<ThreadStat Branches="1" />
<MissingIndexes>
<MissingIndexGroup Impact="72.7096">
<MissingIndex Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[Operation_Type]" ColumnId="3" />
</ColumnGroup>
<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="[Parameters]" ColumnId="9" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="1034368" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="384000" EstimatedPagesCached="1536000" EstimatedAvailableDegreeOfParallelism="32" />
<RelOp AvgRowSize="4021" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="201.393">
<OutputList>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Id" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Parameters" />
<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="Parameters" />
</OutputList>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(100)">
<Const ConstValue="(100)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="4029" EstimateCPU="0.0298551" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="201.393">
<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]" Column="Parameters" />
<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="Parameters" />
</OutputList>
<Parallelism>
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="4029" EstimateCPU="19965.6" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Inner Join" NodeId="2" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="201.363">
<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]" Column="Parameters" />
<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="Parameters" />
</OutputList>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[NasimDB].[dbo].[Batch_Tasks_Queue].[Operation_Type]=[NasimDB].[dbo].[Batch_Tasks_Queue].[Operation_Type] as [btq].[Operation_Type] AND [NasimDB].[dbo].[Batch_Tasks_Queue].[Start_Time]&lt;[NasimDB].[dbo].[Batch_Tasks_Queue].[Start_Time] as [btq].[Start_Time] AND [NasimDB].[dbo].[Batch_Tasks_Queue].[Start_Time] as [btq].[Start_Time]&lt;[NasimDB].[dbo].[Batch_Tasks_Queue].[Finish_Time] AND [NasimDB].[dbo].[Batch_Tasks_Queue].[Id]&lt;&gt;[NasimDB].[dbo].[Batch_Tasks_Queue].[Id] as [btq].[Id]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Operation_Type" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Operation_Type" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Start_Time" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Start_Time" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Start_Time" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Finish_Time" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<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>
</Logical>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="2036" EstimateCPU="0.163514" EstimateIO="44.7266" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="93.2791" LogicalOp="Sort" NodeId="3" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="51.4746">
<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" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Parameters" />
</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="2041" EstimateCPU="0.0139939" EstimateIO="6.52757" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="381283" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="6.54156" TableCardinality="406953">
<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" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Parameters" />
</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>
<DefinedValue>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Column="Parameters" />
</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="2033" EstimateCPU="0.447727" EstimateIO="6.52765" EstimateRebinds="0" EstimateRewinds="93.2789" EstimatedExecutionMode="Row" EstimateRows="400875" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="48.7388" TableCardinality="406953">
<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" />
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Parameters" />
</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>
<DefinedValue>
<ColumnReference Database="[NasimDB]" Schema="[dbo]" Table="[Batch_Tasks_Queue]" Alias="[btq]" Column="Parameters" />
</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>
</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