2

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

  • Is Object2.Column2 unique? Is the ordering guaranteed to be the same across instances? – Randi Vertongen Oct 31 '19 at 10:20
  • Object2 is alias for object1. Object2.Column2 is not unique. Ordering should be guaranteed as we restored from backup. – SqlWorldWide Oct 31 '19 at 11:33
4

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

  <NestedLoops Optimized="0" WithOrderedPrefetch="1">

... 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) & (2017 with compat 2012)

I keep on getting the same amount of rows returned:

2008 (compatibility):

enter image description here

Query plan

2012 (compatibility):

enter image description here

Query Plan

Even when changing Column2 to hold many duplicates, the estimates & 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
|improve this answer|||||
1

Randi Vertongen's answer is best I can expect based on the information I gave in my original question. I am ONLY writing this as an answer as I cannot put this in comment for other readers. Disclaimer: Randi and I chatted about this here.

Once piece of information will help you to follow along. In my top 100 result set with where clause all result belongs to value 829000514900 from column2.

Data set is totally different in the heap between the two environment. I decided to do a rolling sum based on column2.

Here is what I found. I believe the small differnece still exist is due [Forwarded Record][2] in heap.

I ran below query in both environment.

SELECT 
    column2, 
    COUNT(0) AS CountColumn2, 
    SUM (COUNT(column2)) OVER (ORDER BY column2 DESC) AS RunningTotal
FROM dbo.object1
GROUP BY column2 
ORDER BY column2 DESC

Result for SQL Server 2008 environment:

actual number of row read=1176085

colum2          CountColumn2    RunningTotal
829000515000    79              1175804
829000514900    159             1175963
829000514800    631             1176594

Result for SQL Server 2012 environment:

actual number of rows read=8891415

colum2          CountColumn2            RunningTotal
829000515000    79                      8892962
829000514900    159                     8893121
829000514800    631                     8893752
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.