Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.