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';
