%3CLINGO-SUB%20id%3D%22lingo-sub-1447504%22%20slang%3D%22en-US%22%3ERowgoal%20impacts%20the%20estimated%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1447504%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20engineer%20asked%20me%20a%20question%2C%20how%20does%20the%20optimizer%20estimate%20the%20row%20for%20index%20scan(range%20scan).%20he%20observed%20100-109%20estimated%20row%20for%20index%20scan%20but%20he%20wants%20to%20know%20the%20reason.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lduan_dsd_0-1591592282073.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197169iD83460BCE4F532A5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22lduan_dsd_0-1591592282073.png%22%20alt%3D%22lduan_dsd_0-1591592282073.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20my%20understanding%2C%20if%20there%20is%20no%20predicated%2C%20the%20estimated%20row%20should%20equal%20total%20of%20index%20row%20count.%20Then%20I%20requested%20the%20plan%20and%20test%20query.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20my%20test%20script%3C%2FP%3E%0A%3CP%3Ecreate%20table%20ta(c1%20int%20%2Cc2%20int%2C%20c3%20varchar(1000))%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3Ecreate%20table%20tb(c1%20int%20%2Cc2%20int%2C%20c3%20varchar(1000))%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewith%20cte%3C%2FP%3E%0A%3CP%3Eas%3C%2FP%3E%0A%3CP%3E(%3C%2FP%3E%0A%3CP%3Eselect%20ROW_NUMBER()%20over(order%20by%20o.object_id)%20id%20from%20sys.columns%20o%3C%2FP%3E%0A%3CP%3E)%3C%2FP%3E%0A%3CP%3Einsert%20ta%3C%2FP%3E%0A%3CP%3Eselect%20top%201200%20id%20%2Cid%20%2CREPLICATE('a'%2C1000)%20from%20cte%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E----insert%201%2C200%20rows%20into%20ta%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3Ecreate%20index%20ic1%20on%20ta(c1)%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3Einsert%20tb%3C%2FP%3E%0A%3CP%3Eselect%26nbsp%3B%20top%201100%20*%20from%20ta%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E----insert%201%2C000%20rows%20into%20tb%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3Ecreate%20index%20ic2%20on%20tb(c1)%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eselect%20tx.c1%2C%20tx.c2%3C%2FP%3E%0A%3CP%3Efrom%3C%2FP%3E%0A%3CP%3E(%3C%2FP%3E%0A%3CP%3Eselect%26nbsp%3B%20row_number()%20over(order%20by%20ta.c1)%20as%20id%2C%3C%2FP%3E%0A%3CP%3Eta.c1%2Ctb.c2%20From%20ta%20inner%20loop%20join%20tb%20on%20ta.c1%3Dtb.c1%3C%2FP%3E%0A%3CP%3E)%20tx%20where%20tx.id%20%3D1%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20can%20see%20both%20tables%20inserted%201200%20and%201000%20rows.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20analyze%20this%20sample%2C%20I%20want%20to%20introduce%20the%20method%20to%20analyze%20SQL%20CE%20detail.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirstly%2C%20I%20check%20the%20index%20statistics%20on%20the%20table%20'ta'%3A%3C%2FP%3E%0A%3CP%3Eselect%20*%20from%20sys.stats%20where%20object_id%3Dobject_id('ta')%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197170i333B9EDA0ACAEC7A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%221.jpg%22%20alt%3D%221.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197171i0373AB33756F2976%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222.jpg%22%20alt%3D%222.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ETo%20verify%20which%20stats%20uses%20by%20SQL%20CE%2C%20we%20could%20turn%20on%20TF2363%20and%20xevent%3A%3C%2FP%3E%0A%3CP%3Edbcc%20freeproccache%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Edbcc%20traceon(2363%2C3604)%3C%2FP%3E%0A%3CP%3ECREATE%20EVENT%20SESSION%20%5BXeNewCE%5D%20ON%20SERVER%3C%2FP%3E%0A%3CP%3EADD%20EVENT%20sqlserver.query_optimizer_estimate_cardinality(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ACTION(sqlserver.sql_text))%2C%3C%2FP%3E%0A%3CP%3EADD%20EVENT%20sqlserver.query_optimizer_force_both_cardinality_estimation_behaviors%3C%2FP%3E%0A%3CP%3EADD%20TARGET%20package0.event_file(SET%20filename%3DN'D%3A%5CXeNewCE.xel'%2Cmax_file_size%3D(50)%2Cmax_rollover_files%3D(2))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Erun%20the%20query%20again%20and%20include%20real%20plan.%3C%2FP%3E%0A%3CP%3Echeck%20node%208%20from%20xml%20plan%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CRELOP%20avgrowsize%3D%22%26quot%3B11%26quot%3B%22%20estimatecpu%3D%22%26quot%3B0.0012867%26quot%3B%22%20estimateio%3D%22%26quot%3B0.00460648%26quot%3B%22%20estimaterebinds%3D%22%26quot%3B0%26quot%3B%22%20estimaterewinds%3D%22%26quot%3B0%26quot%3B%22%20estimatedexecutionmode%3D%22%26quot%3BRow%26quot%3B%22%20estimaterows%3D%22%26quot%3B100%26quot%3B%22%20estimaterowswithoutrowgoal%3D%22%26quot%3B1027%26quot%3B%22%20estimatedrowsread%3D%22%26quot%3B1027%26quot%3B%22%20logicalop%3D%22%26quot%3BIndex%22%20scan%3D%22%22%3E%3C%2FRELOP%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20can%20see%20this%20node%20gets%20the%26nbsp%3BTableCardinality--total%20table%20row%201027%20from%20statistics.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETableCardinality%3D%221027%22%20StatsCollectionId%3D%221%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%26nbsp%3BStatsCollectionId%3D%221%22%20can%20use%20to%20search%20TF%202363%20result%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETF%202363%20trace%3A%3C%2FP%3E%0A%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%0A%3CP%3EBegin%20selectivity%20computation%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInput%20tree%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20LogOp_Join%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CStCollBaseTable(ID%3D1%2C%20%3CSTRONG%3ECARD%3D1027%3C%2FSTRONG%3E%20TBL%3A%20ta)%26nbsp%3B%20%26nbsp%3B--%3CFONT%20color%3D%22%23FF0000%22%3Ethis%20is%20card%20result%201027%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CStCollBaseTable(ID%3D2%2C%20CARD%3D1027%20TBL%3A%20tb)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Comp%20x_cmpEq%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Identifier%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Btb%5D.c1%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Identifier%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Bta%5D.c1%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlan%20for%20computation%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20CSelCalcExpressionComparedToExpression(%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Bta%5D.c1%20x_cmpEq%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Btb%5D.c1%20)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELoaded%20histogram%20for%20column%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Bta%5D.c1%20from%20stats%20with%20id%202%26nbsp%3B%20%26nbsp%3B%3CFONT%20color%3D%22%23FF0000%22%3E--this%20indicates%20the%20stats%20ID%20referenced%20from%20table%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELoaded%20histogram%20for%20column%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Btb%5D.c1%20from%20stats%20with%20id%202%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelectivity%3A%200.00097371%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStats%20collection%20generated%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20CStCollJoin(ID%3D3%2C%20CARD%3D1027%20x_jtInner)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CStCollBaseTable(ID%3D1%2C%20CARD%3D1027%20TBL%3A%20ta)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CStCollBaseTable(ID%3D2%2C%20CARD%3D1027%20TBL%3A%20tb)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnd%20selectivity%20computation%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20why%20the%20final%20estimated%20row%20changed%20to%20100%3F%3C%2FP%3E%0A%3CP%3E%3CRELOP%20avgrowsize%3D%22%26quot%3B11%26quot%3B%22%20estimatecpu%3D%22%26quot%3B0.0012867%26quot%3B%22%20estimateio%3D%22%26quot%3B0.00460648%26quot%3B%22%20estimaterebinds%3D%22%26quot%3B0%26quot%3B%22%20estimaterewinds%3D%22%26quot%3B0%26quot%3B%22%20estimatedexecutionmode%3D%22%26quot%3BRow%26quot%3B%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EEstimateRows%3D%22100%22%3C%2FSTRONG%3E%20%3CSTRONG%3EEstimateRowsWithoutRowGoal%3D%221027%22%3C%2FSTRONG%3E%3C%2FFONT%3E%20EstimatedRowsRead%3D%221027%22%20LogicalOp%3D%22Index%20Scan%22%20NodeId%3D%228%22%20Parallel%3D%22false%22%20PhysicalOp%3D%22Index%20Scan%22%20EstimatedTotalSubtreeCost%3D%220.00353495%22%20TableCardinality%3D%221027%22%20StatsCollectionId%3D%221%22%26gt%3B%3C%2FRELOP%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20can%20see%20the%20xml%20plan%20includes%26nbsp%3B%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EEstimateRowsWithoutRowGoal%3D%221027%22.%26nbsp%3B%3C%2FSTRONG%3E%3CFONT%20color%3D%22%23000000%22%3Ethis%20introduced%20in%20new%20SSMS%20version%20(18.x)%20and%20you%20may%20easy%20to%20identify%20the%20rowgoal%20applied%20or%20not.%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EOn%20this%20case%2C%20the%20row%20goal%20impacted%20the%20estimated%26nbsp%3Brow%20result.%26nbsp%3B%20We%20can%20disable%20the%20rowgoal%20by%20using%20the%20query%20hint%3A%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3Eselect%20tx.c1%2C%20tx.c2%3C%2FP%3E%0A%3CP%3Efrom%3C%2FP%3E%0A%3CP%3E(%3C%2FP%3E%0A%3CP%3Eselect%26nbsp%3B%20row_number()%20over(order%20by%20ta.c1)%20as%20id%2C%3C%2FP%3E%0A%3CP%3Eta.c1%2Ctb.c2%20From%20ta%20inner%20loop%20join%20tb%20on%20ta.c1%3Dtb.c1%3C%2FP%3E%0A%3CP%3E)%20tx%20where%20tx.id%20%3D1%3C%2FP%3E%0A%3CP%3Eoption(use%20hint('DISABLE_OPTIMIZER_ROWGOAL'))%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%223.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197172i7E5EBD7B52C4D69A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%223.jpg%22%20alt%3D%223.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMore%20information%2C%20please%20reference%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F4051361%2Foptimizer-row-goal-information-in-query-execution-plan-added-in-sql-se%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F4051361%2Foptimizer-row-goal-information-in-query-execution-plan-added-in-sql-se%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fblog.sqlauthority.com%2F2020%2F02%2F07%2Fsql-server-row-goal-and-performance%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblog.sqlauthority.com%2F2020%2F02%2F07%2Fsql-server-row-goal-and-performance%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1447504%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22WeChat%20Image_20200106134830.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211980iC2C8E40BD940F791%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22WeChat%20Image_20200106134830.jpg%22%20alt%3D%22WeChat%20Image_20200106134830.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Microsoft

The engineer asked me a question, how does the optimizer estimate the row for index scan(range scan). he observed 100-109 estimated row for index scan but he wants to know the reason.

 

lduan_dsd_0-1591592282073.png

 

As my understanding, if there is no predicated, the estimated row should equal total of index row count. Then I requested the plan and test query.

 

Here is my test script

create table ta(c1 int ,c2 int, c3 varchar(1000))

go

create table tb(c1 int ,c2 int, c3 varchar(1000))

go

 

with cte

as

(

select ROW_NUMBER() over(order by o.object_id) id from sys.columns o

)

insert ta

select top 1200 id ,id ,REPLICATE('a',1000) from cte

----insert 1,200 rows into ta

go

create index ic1 on ta(c1)

go

insert tb

select  top 1100 * from ta

----insert 1,000 rows into tb

 

go

create index ic2 on tb(c1)

go

 

select tx.c1, tx.c2

from

(

select  row_number() over(order by ta.c1) as id,

ta.c1,tb.c2 From ta inner loop join tb on ta.c1=tb.c1

) tx where tx.id =1

 

I can see both tables inserted 1200 and 1000 rows.

 

To analyze this sample, I want to introduce the method to analyze SQL CE detail.

 

Firstly, I check the index statistics on the table 'ta':

select * from sys.stats where object_id=object_id('ta')

1.jpg2.jpg

To verify which stats uses by SQL CE, we could turn on TF2363 and xevent:

dbcc freeproccache

 

dbcc traceon(2363,3604)

CREATE EVENT SESSION [XeNewCE] ON SERVER

ADD EVENT sqlserver.query_optimizer_estimate_cardinality(

    ACTION(sqlserver.sql_text)),

ADD EVENT sqlserver.query_optimizer_force_both_cardinality_estimation_behaviors

ADD TARGET package0.event_file(SET filename=N'D:\XeNewCE.xel',max_file_size=(50),max_rollover_files=(2))

 

run the query again and include real plan.

check node 8 from xml plan:

 

 

<RelOp AvgRowSize="11" EstimateCPU="0.0012867" EstimateIO="0.00460648" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" EstimateRowsWithoutRowGoal="1027" EstimatedRowsRead="1027" LogicalOp="Index Scan" NodeId="8" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00353495" TableCardinality="1027" StatsCollectionId="1">

 

We can see this node gets the TableCardinality--total table row 1027 from statistics.  

TableCardinality="1027" StatsCollectionId="1"

 

The StatsCollectionId="1" can use to search TF 2363 result:

 

TF 2363 trace:

============================

Begin selectivity computation

 

Input tree:

 

  LogOp_Join

 

      CStCollBaseTable(ID=1, CARD=1027 TBL: ta)   --this is card result 1027

 

      CStCollBaseTable(ID=2, CARD=1027 TBL: tb)

 

      ScaOp_Comp x_cmpEq

 

          ScaOp_Identifier QCOL: [tempdb].[dbo].[tb].c1

 

          ScaOp_Identifier QCOL: [tempdb].[dbo].[ta].c1

 

Plan for computation:

 

  CSelCalcExpressionComparedToExpression( QCOL: [tempdb].[dbo].[ta].c1 x_cmpEq QCOL: [tempdb].[dbo].[tb].c1 )

 

Loaded histogram for column QCOL: [tempdb].[dbo].[ta].c1 from stats with id 2   --this indicates the stats ID referenced from table

 

Loaded histogram for column QCOL: [tempdb].[dbo].[tb].c1 from stats with id 2

 

Selectivity: 0.00097371

 

Stats collection generated:

 

  CStCollJoin(ID=3, CARD=1027 x_jtInner)

 

      CStCollBaseTable(ID=1, CARD=1027 TBL: ta)

 

      CStCollBaseTable(ID=2, CARD=1027 TBL: tb)

 

End selectivity computation

 

Then why the final estimated row changed to 100?

<RelOp AvgRowSize="11" EstimateCPU="0.0012867" EstimateIO="0.00460648" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" EstimateRowsWithoutRowGoal="1027" EstimatedRowsRead="1027" LogicalOp="Index Scan" NodeId="8" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00353495" TableCardinality="1027" StatsCollectionId="1">

 

We can see the xml plan includes EstimateRowsWithoutRowGoal="1027". this introduced in new SSMS version (18.x) and you may easy to identify the rowgoal applied or not.

 

On this case, the row goal impacted the estimated row result.  We can disable the rowgoal by using the query hint:

select tx.c1, tx.c2

from

(

select  row_number() over(order by ta.c1) as id,

ta.c1,tb.c2 From ta inner loop join tb on ta.c1=tb.c1

) tx where tx.id =1

option(use hint('DISABLE_OPTIMIZER_ROWGOAL'))

3.jpg

 

More information, please reference:

https://support.microsoft.com/en-us/help/4051361/optimizer-row-goal-information-in-query-execution-p...

https://blog.sqlauthority.com/2020/02/07/sql-server-row-goal-and-performance/