Trace Flag 9288

[BlitzErik]

I don't see it in your list. While it's not documented much anywhere, it does have known effects around local and global aggregates. Here's a demo:

CREATE TABLE tf_9288
(
    Id INT IDENTITY(1, 1) PRIMARY KEY,
    SomeNumber INT
);

INSERT dbo.tf_9288 WITH ( TABLOCK ) ( SomeNumber )
SELECT TOP 1000000 x.n
FROM   (   SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
           FROM   sys.messages AS m
           CROSS JOIN sys.messages AS m2 ) AS x;

SELECT SUM(x.c) AS sum_c
FROM   (   SELECT COUNT(*) AS c
           FROM   dbo.tf_9288 AS t
           WHERE  t.SomeNumber < 500000
           UNION ALL
           SELECT COUNT(*) AS c
           FROM   dbo.tf_9288 AS t
           WHERE  t.SomeNumber > 500000 ) AS x;

SELECT SUM(x.c) AS sum_c
FROM   (   SELECT COUNT(*) AS c
           FROM   dbo.tf_9288 AS t
           WHERE  t.SomeNumber < 500000
           UNION ALL
           SELECT COUNT(*) AS c
           FROM   dbo.tf_9288 AS t
           WHERE  t.SomeNumber > 500000 ) AS x
OPTION ( QUERYTRACEON 9288 );

[ktaranov]

Hi, @BlitzErik . Great thanks for your issue.

Could you explain more detailed this mysterious phrase:

it does have known effects around local and global aggregates

I run your demo code on

Microsoft SQL Server 2016 (SP1-CU4) (KB4024305) - 13.0.4446.0 (X64)   Jul 16 2017 18:08:49   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home Single Language 6.3 <X64> (Build 15063: ) 

and give the same results (999999 rows) and the same plans.

I added this flag via 1aa9320 (and some others from yours awesome article https://www.brentozar.com/archive/2017/10/bad-idea-jeans-finding-undocumented-trace-flags/) to list but need more information about it behaviour. Please help me.

[BlitzErik]

Are you sure the plans are the same?

To expand on the definition, it seems to coerce partial and global aggregation over scalar aggregation.

[ktaranov]

@BlitzErik ,thankls for quick reply, I retest your demo tommorow.

[ktaranov]

@BlitzErik,thanks for detailed explanation. Updated via 2db22c0

Also, Erik, do you have twitter account or personal blog? I added you to SQL Server People list but cant google any additional info except your awesome article on https://www.brentozar.com/archive/author/erik-darling/

[BlitzErik]

That link is fine. I don't have any other social media accounts, and my personal blog doesn't have any SQL Server content.

Thanks!

[ktaranov]

Once again - thank you for colossal work in popularization and development sql server!