SQLTeam.com | Weblogs | Forums

Partition Elimination

Good Friday afternoon,

For the execution plan, I would expect partition elimination would mean only one partition is accessed. I expect the partition count to be one but it is two. The predicate tells me I should expect only one partition to be accessed but that is not the case. I suspect the partition function is to blame because of datetime but I'm not sure.

Thank you in advance for your time and consideration!

These are the partition/index stats for the table after loading from DDL below.

DDL is below along with INSERT statements and a SELECT statement to illustrate the point.

USE [master]
GO

IF (EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = 'MyDatabase'))
BEGIN
	ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE MyDatabase;
END

CREATE DATABASE [MyDatabase] CONTAINMENT = NONE ON PRIMARY (
	NAME = N'MyDatabase', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2012 (
	NAME = N'MyDatabase_2012_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2012_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2013 (
	NAME = N'MyDatabase_2013_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2013_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2014 (
	NAME = N'MyDatabase_2014_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2014_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2015 (
	NAME = N'MyDatabase_2015_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2015_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2016 (
	NAME = N'MyDatabase_2016_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2016_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2017 (
	NAME = N'MyDatabase_2017_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2017_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2018 (
	NAME = N'MyDatabase_2018_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2018_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2019 (
	NAME = N'MyDatabase_2019_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2019_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2020 (
	NAME = N'MyDatabase_2020_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2020_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2021 (
	NAME = N'MyDatabase_2021_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2021_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2022 (
	NAME = N'MyDatabase_2022_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2022_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_2023 (
	NAME = N'MyDatabase_2023_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_2023_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
FILEGROUP FG_OVERFLOW (
	NAME = N'MyDatabase_OVERFLOW_DATA', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase_OVERFLOW_DATA.ndf', 
	SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB)
LOG ON (
	NAME = N'MyDatabase_log', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabaseLog.ldf',
	SIZE = 64MB, MAXSIZE = 2048GB, FILEGROWTH = 32MB)
WITH CATALOG_COLLATION = DATABASE_DEFAULT;
GO

BEGIN
	ALTER DATABASE [MyDatabase] SET ANSI_NULL_DEFAULT OFF;
	ALTER DATABASE [MyDatabase] SET ANSI_NULLS OFF;
	ALTER DATABASE [MyDatabase] SET ANSI_PADDING OFF;
	ALTER DATABASE [MyDatabase] SET ANSI_WARNINGS OFF;
	ALTER DATABASE [MyDatabase] SET ARITHABORT OFF;
	ALTER DATABASE [MyDatabase] SET AUTO_CLOSE OFF;
	ALTER DATABASE [MyDatabase] SET AUTO_SHRINK OFF;
	ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS ON;
	ALTER DATABASE [MyDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF;
	ALTER DATABASE [MyDatabase] SET CURSOR_DEFAULT  GLOBAL;
	ALTER DATABASE [MyDatabase] SET CONCAT_NULL_YIELDS_NULL OFF;
	ALTER DATABASE [MyDatabase] SET NUMERIC_ROUNDABORT OFF;
	ALTER DATABASE [MyDatabase] SET QUOTED_IDENTIFIER OFF;
	ALTER DATABASE [MyDatabase] SET RECURSIVE_TRIGGERS OFF;
	ALTER DATABASE [MyDatabase] SET  DISABLE_BROKER;
	ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
	ALTER DATABASE [MyDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF;
	ALTER DATABASE [MyDatabase] SET TRUSTWORTHY OFF;
	ALTER DATABASE [MyDatabase] SET ALLOW_SNAPSHOT_ISOLATION OFF;
	ALTER DATABASE [MyDatabase] SET PARAMETERIZATION SIMPLE;
	ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT OFF;
	ALTER DATABASE [MyDatabase] SET HONOR_BROKER_PRIORITY OFF;
	ALTER DATABASE [MyDatabase] SET RECOVERY FULL;
	ALTER DATABASE [MyDatabase] SET  MULTI_USER;
	ALTER DATABASE [MyDatabase] SET PAGE_VERIFY CHECKSUM;
	ALTER DATABASE [MyDatabase] SET DB_CHAINING OFF;
	ALTER DATABASE [MyDatabase] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF );
	ALTER DATABASE [MyDatabase] SET TARGET_RECOVERY_TIME = 60 SECONDS;
	ALTER DATABASE [MyDatabase] SET DELAYED_DURABILITY = DISABLED;
	ALTER DATABASE [MyDatabase] SET QUERY_STORE = ON;
	ALTER DATABASE [MyDatabase] SET READ_WRITE;
END
GO

USE MyDatabase;

CREATE PARTITION FUNCTION PF_MyDatabaseYearlyPartition (DATETIME) AS RANGE LEFT
FOR
VALUES (
	N'2013-01-01' ,N'2014-01-01' ,N'2015-01-01' ,N'2016-01-01'
	,N'2017-01-01' ,N'2018-01-01' ,N'2019-01-01' ,N'2020-01-01' ,N'2021-01-01'
	,N'2022-01-01' ,N'2023-01-01' ,N'2024-01-01'
);

CREATE PARTITION SCHEME PS_MyDatabaseYearlyPartition AS PARTITION PF_MyDatabaseYearlyPartition TO (
	FG_2012, FG_2013, FG_2014, FG_2015, FG_2016, FG_2017,
	FG_2018 ,FG_2019 ,FG_2020 ,FG_2021 ,FG_2022 ,FG_2023 ,FG_OVERFLOW
);

CREATE TABLE dbo.MyTable
(
	ColumnID int NOT NULL,
	ColumnSalesDate datetime NOT NULL,
	ItemID int NOT NULL,
	OtherValue numeric(10, 0) NOT NULL,
	ColumnDateTime datetime NOT NULL,
	ColumnMonth tinyint NULL,
	ColumnYear smallint NULL,
	Quantity int NULL,
	UnitPrice numeric(7, 2) NULL,
	PaymentType tinyint NULL,
	CustomerType tinyint NULL,
	ColumnKey numeric(10, 0) NULL,
	ColumnInvoice numeric(10, 0) NULL,
	ColumnUPC char(14) NULL,
	ColumnLoadDate datetime NULL,
	ColumnLastUpdate datetime NULL,
	ColumnUserName varchar(15) NULL
) ON [PRIMARY];

CREATE CLUSTERED INDEX ciMyDatabaseTranDateTime ON dbo.MyTable (ColumnDateTime)
	WITH (
			SORT_IN_TEMPDB = OFF
			,DROP_EXISTING = OFF
			,ONLINE = OFF
			) ON PS_MyDatabaseYearlyPartition(ColumnDateTime)

INSERT INTO dbo.MyTable (ColumnID, ColumnSalesDate, ItemID, OtherValue, ColumnDateTime, ColumnMonth, ColumnYear, Quantity, UnitPrice, PaymentType, CustomerType, ColumnKey, ColumnInvoice, ColumnUPC, ColumnLoadDate, ColumnLastUpdate, ColumnUserName)
SELECT ColumnID, ColumnSalesDate, ItemID, OtherValue, ColumnDateTime, ColumnMonth, ColumnYear, Quantity, UnitPrice, PaymentType, CustomerType, ColumnKey, ColumnInvoice, ColumnUPC, ColumnLoadDate, ColumnLastUpdate, ColumnUserName
  FROM 
	(
		VALUES 
		(211, '2013-08-29 00:00:00', '413', 9000064803, '2013-08-29 07:44:45', 8, 2013, 1, 15.4, 1, 1, 0, 0, '10084104242426', '2013-08-29 00:00:00', '2013-08-29 19:11:07' , 'Value1'),
		(1821, '2013-08-29 00:00:00', '1348', 3070691501, '2013-08-29 09:03:56', 8, 2013, 1, 24.95, 1, 1, 0, 0, '721059897501', '2013-08-30 00:00:00', '2013-08-30 03:03:54' , 'Value2'),
		(1501, '2013-08-29 00:00:00', '611', 947472, '2013-08-29 09:16:33', 8, 2013, 3, 34.95, 1, 1, 0, 0, '8200010574', '2013-08-30 00:00:00', '2013-08-30 03:03:53' , 'Value3'),
		(521, '2013-08-29 00:00:00', '69', 2055832001, '2013-08-29 09:18:55', 8, 2013, 1, 4.85, 1, 1, 0, 0, '87221002990', '2013-08-29 00:00:00', '2013-08-29 19:09:11' , 'Value4'),
		(182, '2013-08-29 00:00:00', '4597', 3070691901, '2013-08-29 09:19:37', 8, 2013, 1, 37.45, 1, 1, 0, 0, '618397200929', '2013-08-30 00:00:00', '2013-08-30 03:03:54' , 'Value5'),
		(217, '2014-02-01 00:00:00', '544', 1007857803, '2014-02-01 09:06:17', 2, 2014, 1, 6.95, 3, 1, 0, 0, '83103518368', '2014-02-02 00:00:00', '2014-02-02 03:06:24' , 'Value6'),
		(34, '2014-02-01 00:00:00', '639', 1034423801, '2014-02-01 09:50:16', 2, 2014, 1, 7.1, 1, 1, 0, 0, '87221000392', '2014-02-01 00:00:00', '2014-02-01 19:04:13' , 'Value7'),
		(34, '2014-02-01 00:00:00', '622', 1034424101, '2014-02-01 10:00:04', 2, 2014, 1, 13.95, 1, 1, 0, 0, '82000000068', '2014-02-01 00:00:00', '2014-02-01 19:04:13' , 'Value8'),
		(34, '2014-02-01 00:00:00', '132', 1034424102, '2014-02-01 10:00:04', 2, 2014, 1, 9.95, 1, 1, 0, 0, '88100134559', '2014-02-01 00:00:00', '2014-02-01 19:04:13' , 'Value9'),
		(217, '2014-02-01 00:00:00', '506', 3071885603, '2014-02-01 10:12:22', 2, 2014, 2, 7.95, 3, 1, 0, 0, '11034410055', '2014-02-02 00:00:00', '2014-02-02 03:06:24' , 'Value10'),
		(81, '2015-02-01 00:00:00', '6261', 78580, '2015-02-01 10:51:52', 2, 2015, 1, 30.95, 3, 1, 0, 0, '8200010570', '2015-02-01 00:00:00', '2015-02-01 16:01:31' , 'Value11'),
		(40, '2015-02-01 00:00:00', '606', 1102133201, '2015-02-01 11:07:30', 2, 2015, 1, 8.45, 1, 1, 0, 0, '8861235', '2015-02-02 00:00:00', '2015-02-02 19:55:20' , 'Value12'),
		(239, '2015-02-01 00:00:00', '596', 2075929901, '2015-02-01 11:11:58', 2, 2015, 2, 6.85, 1, 1, 0, 0, '82928214349', '2015-02-01 00:00:00', '2015-02-01 16:13:34' , 'Value13'),
		(239, '2015-02-01 00:00:00', '281', 2075930101, '2015-02-01 11:13:10', 2, 2015, 1, 13.95, 1, 1, 0, 0, '82184090503', '2015-02-01 00:00:00', '2015-02-01 16:13:34' , 'Value14'),
		(40, '2015-02-01 00:00:00', '5688', 1102133802, '2015-02-01 11:13:28', 2, 2015, 1, 10.95, 1, 1, 0, 0, '84279971391', '2015-02-02 00:00:00', '2015-02-02 19:55:20' , 'Value15')
	) As MyTableData (ColumnID, ColumnSalesDate, ItemID, OtherValue, ColumnDateTime, ColumnMonth, ColumnYear, Quantity, UnitPrice, PaymentType, CustomerType, ColumnKey, ColumnInvoice, ColumnUPC, ColumnLoadDate, ColumnLastUpdate, ColumnUserName)

SELECT * 
FROM dbo.MyTable As ds
WHERE ds.ColumnDateTime >= '20140101' 
 AND ds.ColumnDateTime < '20150101';

Wouldn't you want RANGE RIGHT for those dates?! I think Jan 1 is going into the prior year, forcing both partitions to be accessed for 1 year to be read.

Thank you for your input!

Sadly - or not depending on how you look at it - that didn't resolve the issue. I change the DDL to RANGE RIGHT and I still get two partitions in the scan (see screenshot below). Do I need to adjust the partition scheme to account for the change to the RANGE RIGHT modification? I commented out all INSERT rows except for the rows that meet the criteria of the sample SQL statement (see screenshot below). So all rows go into one partition yet the execution plan shows two partitions are considered in the plan and the rows are ordered.

It has to be something simple I am missing but that is why I am consulting with the best. Yes that was a shameless plug for the great help this site provides.

Two things solved this. First, thank you to @ScottPletcher because his suggestion on using RANGE RIGHT fixed part of the problem. The other issue was covered by this article:

Using OPTION(RECOMPILE) results in one partition read rather than multiple. So my sad Friday afternoon mind cramp was partly the problem by using LEFT and the other was due to the query engine.

Thank you all for your consideration!

:+1:
NJOY

nice to see

i have experienced this MANY MANY MANY times in my career .. still going on
( as software .. whatever which ever )