add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
category_id logger_group_count
974b0fee-66eb-49f8-ae7f-2cddd0eb7571 3
aa7b1395-7def-4e6c-afe0-84468cb51172 1
30f4dec8-8cc5-42c2-9f6d-9a790ab26ae1 1
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3335.7"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT &#xa; category_id = C.id, &#xa; logger_group_count = COUNT_BIG(DISTINCT L.group_id)&#xa;FROM dbo.logger AS L&#xa;CROSS APPLY &#xa;(&#xa; -- Latest reading per logger&#xa; SELECT TOP (1) &#xa; LD.recorded_on,&#xa; LD.category_name&#xa; FROM dbo.logger_data AS LD&#xa; WHERE LD.logger_uuid = L.uuid&#xa; ORDER BY &#xa; LD.recorded_on DESC&#xa;) AS LDT1&#xa;JOIN dbo.categories AS C&#xa; ON C.[name] = LDT1.category_name&#xa;GROUP BY&#xa; C.id&#xa;ORDER BY&#xa; C.id" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0232072" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x261092D62BF37C17" QueryPlanHash="0xC9BA41FAF4BCA806" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="15" CompileCPU="15" CompileMemory="312"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" MaxQueryMemory="477272"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="867864"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-07-18T10:18:40.75" ModificationCount="0" SamplingPercent="100" Statistics="[PK dbo.categories name]" Table="[categories]" Schema="[dbo]" Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-18T10:18:40.75" ModificationCount="0" SamplingPercent="100" Statistics="[UQ dbo.logger id]" Table="[logger]" Schema="[dbo]" Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-18T10:18:40.77" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_398D8EEE]" Table="[logger]" Schema="[dbo]" Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-18T10:18:40.75" ModificationCount="0" SamplingPercent="100" Statistics="[dbo.logger_data logger_uuid recorded_on]" Table="[logger_data]" Schema="[dbo]" Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-18T10:18:40.75" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_3D5E1FD2]" Table="[logger_data]" Schema="[dbo]" Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="2.9e-006" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0232072" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[categories]" Alias="[C]" Column="id"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[categories]" Alias="[C]" Column="id"></ColumnReference></GroupBy><RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="4" EstimateIO="0.0112613" EstimateCPU="0.000130695" AvgRowSize="39" EstimatedTotalSubtreeCost="0.0232043" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Alias="[L]" Column="group_id"></ColumnReference><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[categories]" Alias="[C]" Column="id"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="1"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[categories]" Alias="[C]" Column="id"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Alias="[L]" Column="group_id"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="7" EstimateIO="0" EstimateCPU="2.926e-005" AvgRowSize="39" EstimatedTotalSubtreeCost="0.0118123" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Alias="[L]" Column="group_id"></ColumnReference><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[categories]" Alias="[C]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger_data]" Alias="[LD]" Column="category_name"></ColumnReference></OuterReferences><RelOp NodeId="3" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="7" EstimateIO="0" EstimateCPU="2.926e-005" AvgRowSize="47" EstimatedTotalSubtreeCost="0.00755136" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Alias="[L]" Column="group_id"></ColumnReference><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger_data]" Alias="[LD]" Column="category_name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Alias="[L]" Column="uuid"></ColumnReference></OuterReferences><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="7" EstimatedRowsRead="7" EstimateIO="0.003125" EstimateCPU="0.0001647" AvgRowSize="40" EstimatedTotalSubtreeCost="0.0032897" TableCardinality="7" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Alias="[L]" Column="group_id"></ColumnReference><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Alias="[L]" Column="uuid"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="7" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Alias="[L]" Column="group_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Alias="[L]" Column="uuid"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Index="[PK dbo.logger uuid]" Alias="[L]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="5" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0042324" Parallel="0" EstimateRebinds="6" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger_data]" Alias="[LD]" Column="category_name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="6" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateRowsWithoutRowGoal="1.71429" EstimatedRowsRead="1.71429" EstimateIO="0.003125" EstimateCPU="0.000158886" AvgRowSize="39" EstimatedTotalSubtreeCost="0.0042317" TableCardinality="12" Parallel="0" EstimateRebinds="6" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger_data]" Alias="[LD]" Column="category_name"></ColumnReference><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger_data]" Alias="[LD]" Column="recorded_on"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="7" ActualLogicalReads="14" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="7" ActualEndOfScans="0" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="BACKWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger_data]" Alias="[LD]" Column="category_name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger_data]" Alias="[LD]" Column="recorded_on"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger_data]" Index="[dbo.logger_data logger_uuid recorded_on]" Alias="[LD]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger_data]" Alias="[LD]" Column="logger_uuid"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6].[dbo].[logger].[uuid] as [L].[uuid]"><Identifier><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger]" Alias="[L]" Column="uuid"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Top></RelOp></NestedLoops></RelOp><RelOp NodeId="7" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0042317" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="6" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[categories]" Alias="[C]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="7" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="14" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="7" ActualEndOfScans="0" ActualExecutions="7"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[categories]" Alias="[C]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[categories]" Index="[PK dbo.categories name]" Alias="[C]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[categories]" Alias="[C]" Column="name"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6].[dbo].[logger_data].[category_name] as [LD].[category_name]"><Identifier><ColumnReference Database="[fiddle_783bf77fe3a34a20b5d7ae81e4bd9fb6]" Schema="[dbo]" Table="[logger_data]" Alias="[LD]" Column="category_name"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Sort></RelOp></StreamAggregate></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>