Created
April 3, 2017 16:27
-
-
Save LitKnd/93a0a4ed4e15039ba0030e1bb34a74dd to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DBCC TRACEOFF (4199, -1); | |
GO | |
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 = ON; | |
GO | |
SELECT IntCol | |
FROM dbo.LetsTalkAboutQueryOptimizerHotfixes | |
WHERE PartitioningCol < '2017-10-02' | |
ORDER BY PartitioningCol DESC, CharCol DESC | |
OPTION (RECOMPILE); | |
GO | |
USE tempdb; | |
GO | |
SELECT IntCol | |
FROM QueryOptimizerHotfixes.dbo.LetsTalkAboutQueryOptimizerHotfixes | |
WHERE PartitioningCol < '2017-10-02' | |
ORDER BY PartitioningCol DESC, CharCol DESC | |
OPTION (RECOMPILE); | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment