'Row Goal' different behavior - why? This is a heap with 400+ Million rows and 16 non clustered indexes, 146 columns. Please do not focus on why I cannot create a clustered index. That is not in my control and not in scope to my question.

SQL Server:


  Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64)   Jan  5 2018 22:11:56   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor) 
  Database Compatibility Level:SQL Server 2008(100)


When I run following query (anonymized), I get this actual execution plan:  

SELECT TOP ? *
FROM Schema1.Object1 Object2
WHERE Object2.Column1 LIKE ?
ORDER BY Object2.Column2 DESC


Same database restored to SQL 2017 CU13 and change compatibility mode to SQL Server 2012 (110) (following is the full version name), and I get this actual execution plan.


  Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)   Nov 30 2018 12:57:58   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor) 


I totally understand that 'ROW GOAL' is in play here. You cannot see it in the first plan as it was not exposed in the query plan before. To be sure I can run the SQL Server 2008 version with Trace Flag 4138 and SQL Server 2012 version with DISABLE_OPTIMIZER_ROWGOAL hint. Both cases I get significant performance gain. Gain happens because optimizer pick the right index and does not choose a nested loop join.

Question:


While both compatibility level is prior to SQL Server 2014 version why EstimatedRowsWithoutRowGoal (I understand you cannot see it in 2008) is different?
Why Index Scan qualify 1.1 Million rows in SQL 2008 vs 8.7 Million rows in SQL 2012? What heuristics changed (even I am not using new optimizer launched with Compatibility 120)? 


I read this article by Paul White and all 8 links given in the article.
Also read More Showplan enhancements – Row Goal by Pedro Lopes
Your 2 queries (and tables?) don't exactly match.

The sql server 2008 compatibility level query has 143 columns returned while 
the sql server 2012 compatibility level query  has 146 columns returned.

Please validate any other differences in these tables.




  Why Index Scan qualify 1.1 Million rows in SQL 2008 vs 8.7 Million
  rows in SQL 2012?


Your index scan is returning rows, ordered by Object2.Column2 
to then push their bookmark 

(bookmark=column144 / Column147 in your anonomized plans) 

to the RID lookup and filter on Object2.Column1.



Initial guess: Ties?

This leads me to believe that Object2.Column2 has ties, resulting in different rows to be read first. 

Both are executing ordered prefetching

  &lt;NestedLoops Optimized="0" WithOrderedPrefetch="1"&gt;



  ... An ordered prefetch preserves key order on the inner side of the
  nested loops join,  so SQL Server ensures that asynchronous I/Os are
  processed in the correct order. ... Our test query features an ORDER BY
  clause that the optimizer can avoid sorting for by using ordered
  prefetch...


Source

I might be wrong, but what I take from this is that, as long as the ordering is preserved, different rows could be fed to the RID lookup operator, supporting the ties claim. 

You could try running the query with a unique order and adding these query plans to the question.




  While both compatibility level is prior to SQL Server 2014 version why
  EstimatedRowsWithoutRowGoal (I understand you cannot see it in 2008)
  is different?


We would need detailed statistic information here. The statistics show different estimates between the tables, resulting in a difference in EstimatedRowsWithoutRowGoal 

You could try running a full scan update before running both queries while selecting the same amount of columns.



Testing

When doing tests between sql server instances (2008) &amp; (2017 with compat 2012)

I keep on getting the same amount of rows returned:

2008 (compatibility):



Query plan

2012 (compatibility):



Query Plan

Even when changing Column2 to hold many duplicates, the estimates &amp; actuals remain the same.

UPDATE dbo.Object1
SET Object1.Column2
= Object1.Column2 % 400;


As expected, estimates remain the same when adding the extra columns to the table in the compat 2012 db

ALTER TABLE dbo.Object1 
ADD ExtraColumn1 int default(1) with values;
ALTER TABLE dbo.Object1 
ADD ExtraColumn2 int default(2) with values;
ALTER TABLE dbo.Object1 
ADD ExtraColumn3 int default(3) with values;


Same story with enabling the traceflags

Conclusion 

This kind of rules out my previous statement about ties in your data, but also shows that I cannot replicate your problem with my dataset (much smaller).

Even if I set the entire column2 to the same value, there is 0 difference in the amount of rows returned. Data differences seem like what should cause this.



Dataset used

CREATE DATABASE TEST
GO
USE [master]
GO
ALTER DATABASE [TEST] MODIFY FILE ( NAME = N'TEST', FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [TEST] MODIFY FILE ( NAME = N'TEST_log', FILEGROWTH = 1048576KB )
GO
alter database test set recovery simple
GO
backup database test to disk = 'NUL'

GO

use TEST
go

CREATE TABLE dbo.Object1(
Column1 int,
Column2 int,
Column3 int,
Column4 int,
Column5 int,
Column6 int,
Column7 int,
Column8 int,
Column9 int,
Column10 int,
Column11 int,
Column12 int,
Column13 int,
Column14 int,
Column15 int,
Column16 int,
Column17 int,
Column18 int,
Column19 int,
Column20 int,
column1_varchar varchar(20));

CREATE INDEX Index1
on dbo.Object1(column15,Column16,Column17)
 CREATE INDEX Index2
on dbo.Object1(Column2,Column10,Column19)


SELECT top(1000000)  --1M
row_number() over(order by (select null)) as rownum
INTO #temp
FROM master..spt_values spt1
cross apply master..spt_values spt2
cross apply master..spt_values spt3


INSERT INTO dbo.Object1
WITH(TABLOCK)
SELECT 
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
'ABC'+ CAST(rownum as varchar(20)) 
FROM #temp  
GO 3