TF-4199-global-vs-session.sql

IF @@TRANCOUNT > 0
    ROLLBACK;
GO
SET NOCOUNT ON;
GO

USE master;
GO

IF DB_ID('QueryOptimizerHotfixes') IS NOT NULL
BEGIN
    ALTER DATABASE QueryOptimizerHotfixes SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE QueryOptimizerHotfixes;
END

CREATE DATABASE QueryOptimizerHotfixes
GO

USE QueryOptimizerHotfixes
GO

--Let's repro this bug
--https://support.microsoft.com/en-us/help/3198775/fix-an-inefficient-query-plan-is-used-for-a-query-requiring-order-by-partitioning-column-of-a-table-with-single-partition

--The loneliest partition function
CREATE PARTITION FUNCTION pf (DATE)
    AS RANGE RIGHT
    FOR VALUES (   );
GO

CREATE PARTITION SCHEME ps
    AS PARTITION pf
    ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.LetsTalkAboutQueryOptimizerHotfixes (
	CXCol BIGINT IDENTITY NOT NULL,
	PartitioningCol DATE NOT NULL,
    CharCol CHAR(100) NOT NULL DEFAULT ('FOO'),
    IntCol INT NOT NULL DEFAULT (2)
) ON ps (PartitioningCol) 
GO

/* Insert 1 million rows */
BEGIN TRAN
    DECLARE @i int = 0;
    WHILE @i < 1000000
    BEGIN
        INSERT dbo.LetsTalkAboutQueryOptimizerHotfixes (PartitioningCol)
        SELECT DATEADD(dd,@i,'2017-01-01')

        SET @i=@i+1;
    END
COMMIT
GO 

/* Gotta put the partitioning col in the key */
CREATE UNIQUE CLUSTERED INDEX cx_LetsTalkAboutQueryOptimizerHotfixes
    on dbo.LetsTalkAboutQueryOptimizerHotfixes (CXCol, PartitioningCol);
GO

/* Now give it a NC on the partitioning col */
CREATE NONCLUSTERED INDEX ix_LetsTalkAboutQueryOptimizerHotfixes_PartitioningCol_CharCol on
    dbo.LetsTalkAboutQueryOptimizerHotfixes (PartitioningCol, CharCol);
GO


ALTER DATABASE [QueryOptimizerHotfixes] SET COMPATIBILITY_LEVEL = 130
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO

USE QueryOptimizerHotfixes;
GO

SELECT IntCol
FROM dbo.LetsTalkAboutQueryOptimizerHotfixes
WHERE PartitioningCol < '2017-10-02'
ORDER BY PartitioningCol DESC, CharCol DESC
    OPTION (RECOMPILE);
GO



/* Enable TF 4199 globally */
/* Run from another session if you like, same difference */
DBCC TRACEON (4199, -1);
GO

/* Note that Global = 1, Session =0.
That's normal-- we haven't enabled this for our session, we have
enabled it globally for all sessions. */
DBCC TRACESTATUS;
GO

/* Let's try to turn this off just for our session */
DBCC TRACEOFF (4199);
GO

/* It's still the same status-- it was never on just for our session. */
DBCC TRACESTATUS;
GO

/* We can turn it off globally */
DBCC TRACEOFF (4199, -1);
GO