SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select *
into docs
from (values ('Enter one batch per field, don''t use ''GO''')
     , ('Fields grow as you type')
     , ('Use the [+] buttons to add more')
     , ('See examples below for advanced usage') ) z(how_to_use_dbfiddle);
4 rows affected

select * from docs;


how_to_use_dbfiddle


Enter one batch per field, don't use 'GO'

Fields grow as you type

Use the [+] buttons to add more

See examples below for advanced usage


--------------------------------------------------------------------------------
-- ① Execution Plans 
--------------------------------------------------------------------------------
set statistics profile on;
select max('Do this to get execution plans and stats') "Execution Plans"
from docs d1 cross join docs d2;
set statistics profile off;


Execution Plans


Do this to get execution plans and stats




Rows
Executes
StmtText
StmtId
NodeId
Parent
PhysicalOp
LogicalOp
Argument
DefinedValues
EstimateRows
EstimateIO
EstimateCPU
AvgRowSize
TotalSubtreeCost
OutputList
Warnings
Type
Parallel
EstimateExecutions


1
1
select max('Do this to get execution plans and stats') "Execution Plans"
from docs d1 cross join docs d2
1
1
0
null
null
null
null
1
null
null
null
0.00689848
null
null
SELECT
False
null

1
1
  |--Stream Aggregate(DEFINE:([Expr1004]=MAX('Do this to get execution plans and stats')))
1
2
1
Stream Aggregate
Aggregate
null
[Expr1004]=MAX('Do this to get execution plans and stats')
1
0
1.01E-05
31
0.00689848
[Expr1004]
null
PLAN_ROW
False
1

16
1
       |--Nested Loops(Inner Join)
1
3
2
Nested Loops
Inner Join
null
null
16
0
6.688E-05
9
0.00688838
null
NO JOIN PREDICATE
PLAN_ROW
False
1

4
1
            |--Table Scan(OBJECT:([fiddle_1e2dafa08fbe48baa07f91a36099dc66].[dbo].[docs] AS [d2]))
1
4
3
Table Scan
Table Scan
OBJECT:([fiddle_1e2dafa08fbe48baa07f91a36099dc66].[dbo].[docs] AS [d2])
null
4
0.003125
0.0001614
9
0.0032864
null
null
PLAN_ROW
False
1

16
4
            |--Table Scan(OBJECT:([fiddle_1e2dafa08fbe48baa07f91a36099dc66].[dbo].[docs] AS [d1]))
1
5
3
Table Scan
Table Scan
OBJECT:([fiddle_1e2dafa08fbe48baa07f91a36099dc66].[dbo].[docs] AS [d1])
null
4
0.0032035
8.29E-05
9
0.0035351
null
null
PLAN_ROW
False
4


--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select max('Do this to get execution plans and stats') "Execution Plans"
from docs d1 cross join docs d2;
set statistics xml off;


Execution Plans


Do this to get execution plans and stats




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4365.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="select max(&apos;Do this to get execution plans and stats&apos;) &quot;Execution Plans&quot;&#xa;from docs d1 cross join docs d2" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00689848" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x9BEA186AE6E95E86" QueryPlanHash="0xC79ED7BBB7BC4A7F" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><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" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="160"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="321228" EstimatedPagesCached="20076" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1682840"></OptimizerHardwareDependentProperties><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><WaitStats><Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="1" WaitCount="5"></Wait></WaitStats><QueryTimeStats ElapsedTime="1" CpuTime="1"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.01e-05" AvgRowSize="31" EstimatedTotalSubtreeCost="0.00689848" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="MAX(&apos;Do this to get execution plans and stats&apos;)"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Const ConstValue="&apos;Do this to get execution plans and stats&apos;"></Const></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="16" EstimateIO="0" EstimateCPU="6.688e-05" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00688838" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><Warnings NoJoinPredicate="1"></Warnings><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="16" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_1e2dafa08fbe48baa07f91a36099dc66]" Schema="[dbo]" Table="[docs]" Alias="[d2]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp><RelOp NodeId="3" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.0032035" EstimateCPU="8.29e-05" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0035351" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="3" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="16" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="16" ActualEndOfScans="4" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_1e2dafa08fbe48baa07f91a36099dc66]" Schema="[dbo]" Table="[docs]" Alias="[d1]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></NestedLoops></RelOp></StreamAggregate></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


--------------------------------------------------------------------------------
-- ② Integers table
--------------------------------------------------------------------------------
with
  p0(i) as (select 1 union all select 1 union all select 1 union all select 1)
, p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows
, p2(i) as (select 1 from p1 as a, p1 as b)--1M rows
select row_number() over(order by i) as val
into integers
from p2
1048576 rows affected

Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
1	1	select max('Do this to get execution plans and stats') "Execution Plans" from docs d1 cross join docs d2	1	1	0	null	null	null	null	1	null	null	null	0.00689848	null	null	SELECT	False	null
1	1	\|--Stream Aggregate(DEFINE:([Expr1004]=MAX('Do this to get execution plans and stats')))	1	2	1	Stream Aggregate	Aggregate	null	[Expr1004]=MAX('Do this to get execution plans and stats')	1	0	1.01E-05	31	0.00689848	[Expr1004]	null	PLAN_ROW	False	1
16	1	\|--Nested Loops(Inner Join)	1	3	2	Nested Loops	Inner Join	null	null	16	0	6.688E-05	9	0.00688838	null	NO JOIN PREDICATE	PLAN_ROW	False	1
4	1	\|--Table Scan(OBJECT:([fiddle_1e2dafa08fbe48baa07f91a36099dc66].[dbo].[docs] AS [d2]))	1	4	3	Table Scan	Table Scan	OBJECT:([fiddle_1e2dafa08fbe48baa07f91a36099dc66].[dbo].[docs] AS [d2])	null	4	0.003125	0.0001614	9	0.0032864	null	null	PLAN_ROW	False	1
16	4	\|--Table Scan(OBJECT:([fiddle_1e2dafa08fbe48baa07f91a36099dc66].[dbo].[docs] AS [d1]))	1	5	3	Table Scan	Table Scan	OBJECT:([fiddle_1e2dafa08fbe48baa07f91a36099dc66].[dbo].[docs] AS [d1])	null	4	0.0032035	8.29E-05	9	0.0035351	null	null	PLAN_ROW	False	4