April 6, 2021 at 11:59 am
All,
I'm working on improving my knowledge of query optimisation and reading execution plans. I'm using this table:
/****** Object: Table [dbo].[CustTravelReqLocPlan] Script Date: 06/04/2021 12:40:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustTravelReqLocPlan](
[uniqueref] [uniqueidentifier] NOT NULL,
[CustTravelReqRef] [uniqueidentifier] NULL,
[LocationTypeRef] [uniqueidentifier] NULL,
[AllocatedFlightRef] [uniqueidentifier] NULL,
[AllocatedAreaRef] [uniqueidentifier] NULL,
[RequiredArrivalTime] [datetime] NULL,
[ActualArrivalTime] [datetime] NULL,
[Duration] [int] NULL,
[Place] [int] NULL,
CONSTRAINT [PK__CustTrav__401DC63546C0E5F6] PRIMARY KEY CLUSTERED
(
[uniqueref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustTravelReqLocPlan] ADD CONSTRAINT [DF__CustTrave__uniqu__67A95F59] DEFAULT (newid()) FOR [uniqueref]
GO
ALTER TABLE [dbo].[CustTravelReqLocPlan] WITH CHECK ADD CONSTRAINT [FK__CustTrave__Alloc__6A85CC04] FOREIGN KEY([AllocatedAreaRef])
REFERENCES [dbo].[Location] ([uniqueref])
GO
ALTER TABLE [dbo].[CustTravelReqLocPlan] CHECK CONSTRAINT [FK__CustTrave__Alloc__6A85CC04]
GO
ALTER TABLE [dbo].[CustTravelReqLocPlan] WITH CHECK ADD CONSTRAINT [FK__CustTrave__Locat__6991A7CB] FOREIGN KEY([LocationTypeRef])
REFERENCES [dbo].[LocationType] ([uniqueref])
GO
ALTER TABLE [dbo].[CustTravelReqLocPlan] CHECK CONSTRAINT [FK__CustTrave__Locat__6991A7CB]
GO
Below are two queries, and their execution plans, which produce the same output:
CTE version:
with plancount(planref, itemcount) as
(select CustTravelReqref, count(uniqueref) from custtravelreqlocplan
group by CustTravelReqref)
select CustTravelReqLocPlan.CustTravelReqRef, place,itemcount from custtravelreqlocplan
inner join plancount on CustTravelReqLocPlan.CustTravelReqRef=plancount.planref
order by CustTravelReqLocPlan.CustTravelReqRef, place
<?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.481" Build="14.0.3370.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="2702" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.159029" StatementText="with plancount(planref, itemcount) as
(select CustTravelReqref, count(uniqueref) from custtravelreqlocplan
group by CustTravelReqref)
select CustTravelReqLocPlan.CustTravelReqRef, place,itemcount from custtravelreqlocplan
inner join plancount on CustTravelReqLocPlan.CustTravelReqRef=plancount.planref
order by CustTravelReqLocPlan.CustTravelReqRef, place" StatementType="SELECT" QueryHash="0x8CAFEB69074E9CAB" QueryPlanHash="0xD592D8B85D905967" RetrievedFromCache="true" SecurityPolicyApplied="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 DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="32" CompileTime="14" CompileCPU="14" CompileMemory="392">
<MissingIndexes>
<MissingIndexGroup Impact="25.1412">
<MissingIndex Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]">
<ColumnGroup Usage="INEQUALITY">
<Column Name="[CustTravelReqRef]" ColumnId="2" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[Place]" ColumnId="9" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="880" RequiredMemory="512" DesiredMemory="880" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="184" MaxQueryMemory="207032" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104857" EstimatedPagesCached="13107" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="655440" />
<OptimizerStatsUsage>
<StatisticsInfo Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Statistics="[_WA_Sys_00000002_6B79F03D]" ModificationCount="776" SamplingPercent="100" LastUpdate="2021-03-02T12:07:11.35" />
</OptimizerStatsUsage>
<WaitStats>
<Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="49" WaitCount="10" />
</WaitStats>
<QueryTimeStats CpuTime="33" ElapsedTime="83" />
<RelOp AvgRowSize="31" EstimateCPU="0.0412307" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2702" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.159029">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
<ColumnReference Column="Expr1004" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2702" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="32" ActualCPUms="31" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="283" LogicalOp="Lazy Spool" NodeId="2" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.116192">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="408" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="23" ActualCPUms="23" ActualScans="1" ActualLogicalReads="5405" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool>
<RelOp AvgRowSize="27" EstimateCPU="0.00535463" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2702" LogicalOp="Segment" NodeId="3" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="0.110837">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
<ColumnReference Column="Segment1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2702" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" />
</RunTimeInformation>
<Segment>
<GroupBy>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
</GroupBy>
<SegmentColumn>
<ColumnReference Column="Segment1007" />
</SegmentColumn>
<RelOp AvgRowSize="27" EstimateCPU="0.0481516" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2702" LogicalOp="Sort" NodeId="4" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.105483">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="2702" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="184" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="27" EstimateCPU="0.0031292" EstimateIO="0.0416435" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2702" EstimatedRowsRead="2702" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0447727" TableCardinality="2702">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2702" ActualRowsRead="2702" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="1" ActualCPUms="1" ActualScans="1" ActualLogicalReads="56" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</DefinedValue>
</DefinedValues>
<Object Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Index="[PK__CustTrav__401DC63546C0E5F6]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="[Airport].[dbo].[CustTravelReqLocPlan].[CustTravelReqRef] IS NOT NULL">
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Segment>
</RelOp>
</Spool>
</RelOp>
<RelOp AvgRowSize="27" EstimateCPU="0.000535463" EstimateIO="0" EstimateRebinds="283" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00107093">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
<ColumnReference Column="Expr1004" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2702" Batches="0" ActualEndOfScans="408" ActualExecutions="408" ActualExecutionMode="Row" ActualElapsedms="8" ActualCPUms="8" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="27" EstimateCPU="5.35463E-05" EstimateIO="0" EstimateRebinds="283" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="7" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00058901">
<OutputList>
<ColumnReference Column="Expr1004" />
<ColumnReference Column="Expr1004" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1008],0)">
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1008" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="27" EstimateCPU="0.000535463" EstimateIO="0" EstimateRebinds="283" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="8" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.000535463">
<OutputList>
<ColumnReference Column="Expr1008" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="407" Batches="0" ActualEndOfScans="408" ActualExecutions="408" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="6" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="283" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="9" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="407" ActualRows="2702" Batches="0" ActualEndOfScans="408" ActualExecutions="408" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool PrimaryNodeId="2" />
</RelOp>
</StreamAggregate>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="283" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="10" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="407" ActualRows="2702" Batches="0" ActualEndOfScans="407" ActualExecutions="407" ActualExecutionMode="Row" ActualElapsedms="1" ActualCPUms="1" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool PrimaryNodeId="2" />
</RelOp>
</NestedLoops>
</RelOp>
</NestedLoops>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Window function version:
select CustTravelReqLocPlan.CustTravelReqRef, place,
count(place) over (partition by CustTravelReqLocPlan.CustTravelReqRef) from custtravelreqlocplan
order by CustTravelReqLocPlan.CustTravelReqRef, place
<?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.481" Build="14.0.3370.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="2702" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.157732" StatementText="select CustTravelReqLocPlan.CustTravelReqRef, place,
count(place) over (partition by CustTravelReqLocPlan.CustTravelReqRef) from custtravelreqlocplan
order by CustTravelReqLocPlan.CustTravelReqRef, place" StatementType="SELECT" QueryHash="0xF58D98C0A20E6856" QueryPlanHash="0xCF6BD03533520E9B" RetrievedFromCache="true" SecurityPolicyApplied="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 DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="32" CompileTime="1" CompileCPU="1" CompileMemory="208">
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="880" RequiredMemory="512" DesiredMemory="880" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="184" MaxQueryMemory="199016" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104857" EstimatedPagesCached="13107" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="741792" />
<OptimizerStatsUsage>
<StatisticsInfo Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Statistics="[_WA_Sys_00000002_6B79F03D]" ModificationCount="776" SamplingPercent="100" LastUpdate="2021-03-02T12:07:11.35" />
</OptimizerStatsUsage>
<WaitStats>
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="2" WaitCount="2" />
<Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="58" WaitCount="12" />
</WaitStats>
<QueryTimeStats CpuTime="20" ElapsedTime="80" />
<RelOp AvgRowSize="31" EstimateCPU="0.0412307" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2702" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.157732">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
<ColumnReference Column="Expr1002" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2702" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="21" ActualCPUms="18" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="283" LogicalOp="Lazy Spool" NodeId="1" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.114895">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="408" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="16" ActualCPUms="13" ActualScans="1" ActualLogicalReads="5405" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool>
<RelOp AvgRowSize="27" EstimateCPU="0.00535463" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2702" LogicalOp="Segment" NodeId="2" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="0.10954">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
<ColumnReference Column="Segment1003" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2702" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="8" ActualCPUms="6" />
</RunTimeInformation>
<Segment>
<GroupBy>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
</GroupBy>
<SegmentColumn>
<ColumnReference Column="Segment1003" />
</SegmentColumn>
<RelOp AvgRowSize="27" EstimateCPU="0.0481516" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2702" LogicalOp="Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.104186">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="2702" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="6" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="184" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="27" EstimateCPU="0.0031292" EstimateIO="0.0416435" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2702" EstimatedRowsRead="2702" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0447727" TableCardinality="2702">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2702" ActualRowsRead="2702" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="1" ActualCPUms="1" ActualScans="1" ActualLogicalReads="56" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</DefinedValue>
</DefinedValues>
<Object Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Index="[PK__CustTrav__401DC63546C0E5F6]" IndexKind="Clustered" Storage="RowStore" />
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Segment>
</RelOp>
</Spool>
</RelOp>
<RelOp AvgRowSize="27" EstimateCPU="0.000535463" EstimateIO="0" EstimateRebinds="283" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00107093">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
<ColumnReference Column="Expr1002" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2702" Batches="0" ActualEndOfScans="408" ActualExecutions="408" ActualExecutionMode="Row" ActualElapsedms="4" ActualCPUms="4" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="27" EstimateCPU="5.35463E-05" EstimateIO="0" EstimateRebinds="283" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00058901">
<OutputList>
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1002" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1004],0)">
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1004" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="27" EstimateCPU="0.000535463" EstimateIO="0" EstimateRebinds="283" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="7" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.000535463">
<OutputList>
<ColumnReference Column="Expr1004" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="407" Batches="0" ActualEndOfScans="408" ActualExecutions="408" ActualExecutionMode="Row" ActualElapsedms="2" ActualCPUms="2" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="COUNT([Airport].[dbo].[CustTravelReqLocPlan].[Place])">
<Aggregate AggType="COUNT_BIG" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="283" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="8" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="407" ActualRows="2702" Batches="0" ActualEndOfScans="408" ActualExecutions="408" ActualExecutionMode="Row" ActualElapsedms="1" ActualCPUms="1" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool PrimaryNodeId="1" />
</RelOp>
</StreamAggregate>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="27" EstimateCPU="0" EstimateIO="0" EstimateRebinds="283" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="9" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0">
<OutputList>
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="CustTravelReqRef" />
<ColumnReference Database="[Airport]" Schema="[dbo]" Table="[CustTravelReqLocPlan]" Column="Place" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="407" ActualRows="2702" Batches="0" ActualEndOfScans="407" ActualExecutions="407" ActualExecutionMode="Row" ActualElapsedms="1" ActualCPUms="1" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Spool PrimaryNodeId="1" />
</RelOp>
</NestedLoops>
</RelOp>
</NestedLoops>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
The first thing I noticed is that the operations used are the same but the CTE version suggests a missing index but the window function doesn't. Is this due to the way that Window functions work or something I've missed in the execution plan?
On the CTE version I noticed that node ID 8 (Stream aggregate) has the expression:
[Expr1008] = Scalar Operator(Count(*))
I think that using * is normally discouraged in a count? However as the input for this node is the output of another node there is nothing I can do about it and I can just assume SQL Server has made the best choice?
With my relatively limited experience of reading plans I'm trying to gain more experience of knowing what to look for. In these plans the only possible improvements I saw were improving the index scan and trying to remove the sort. Is there anything else that should be immediately obvious as something to investigate?
Thanks
April 6, 2021 at 3:38 pm
For these specific queries alone -- since we know nothing else about this table -- you should change the clustering key on the table shown.
CREATE TABLE dbo.CustTravelReqLocPlan (
...
CONSTRAINT [PK__CustTrav__401DC63546C0E5F6] PRIMARY KEY NONCLUSTERED ( [uniqueref] ) WITH ...,
CONSTRAINT [CustTravelReqLocPlan__CL] UNIQUE CLUSTERED ( [CustTravelReqRef], [uniqueref] ) WITH ...
...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 6, 2021 at 4:14 pm
No. Using (*) as a part of a COUNT is NOT discouraged. It will allow SQL Server to pick the best index to use to accomplish the count. The use of COUNT(some column name) is generally used for when you want to count a specific column without counting any NULL values.
As for the execution plans you posted, it would be helpful to right-click on the Actual Execution plan(s) and save them as a file. This site won't allow you to attach them with the default extension so just rename them with a .TXT extension and let people know you've done so.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2021 at 4:44 pm
To add to what Scott said, I would expect that the plans would be different. One is using a JOIN and one is not. If you replace the CTE with a nested select, you can see the two queries are quite a bit different in what they are doing.
You may get a similar plan, but with the CTE, you have the JOIN on an un-indexed column which is why it is recommending that index get created. With the windowing function, you have no join, so the optimizer sees no benefit in adding that index.
As for the count(*) you mentioned, my understanding is that SQL saw no benefit to doing a count on that specific column, so it is doing a count(*) which essentially is just counting the rows grouped by the column you selected to group by as it didn't need to read that column's data to know if it should count it or not. In your sample queries, COUNT(1) or COUNT(*) would have given the same result.
Removing scans is not always the best idea either. Really, you need to look at what you are trying to improve. What I mean is that by adding a non-clustered index to remove the sort and scan operations, you are increasing the write time to the database. Now, I'm not saying "indexes are bad"; they serve a purpose in that they usually help SELECT performance (not always, but that is generally why they get created). If your query runs in 1 second and is used 1 time per year, I personally wouldn't bother trying to tune it. But if it is used multiple times per hour and runs in 1 second, tuning it could be beneficial.
The fun thing with SQL is that there are a LOT of ways to write the same query that may result in similar query plans (or drastically different ones!). I mean, you could have used a temp table or a table variable to hold the data, you could do a self-join, you could even be crazy and use a cursor (note - don't use a cursor... it won't be efficient); there are multiple ways to write the query you did that will give you different execution plans. The trick comes in when you go to tune it to review the code AND the execution plan to determine where it MAY be improved and to test it. And when adding indexes, you need to be careful that you are adding the CORRECT indexes. Adding an index to improve an already efficient and infrequently used SELECT query may cause other performance issues you were not expecting. Also, adding indexes uses more disk, so you need to be mindful of that as well. More disk = larger backups. I mean, adding 1 index on a million row table shouldn't overfill your disk, but adding 100 indexes on a million row table might (and that'll tank performance as well).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 6, 2021 at 5:53 pm
The execution plans are identical. For some reason SQL Server hasn't identified the missing index in the second query.
Have you tried creating the index to check that both queries will use the new index?
CREATE INDEX IX_custtravelreqlocplan_CustTravelReqRef_INC_Place
ON dbo.custtravelreqlocplan(CustTravelReqRef) INCLUDE (Place);
Check the messages when you execute with SET STATISTICS IO, TIME ON before and after creation of the missing index.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply