No partition elimination for partition column values in a lookup table?

Question

I created a partitioned table (as shown below), and seeded 480 million rows - about 181 rows per account.

I'm running baseline queries before adding indexes. I was surprised to see that doing date lookups on the partition column didn't result in partition elimination even after adding option(recompile). Is that how it is with partitioned tables? Seems to me that is more like real life than hard coding the predicate's partition column values.

• Actual execution plan of query where partition elimination appears to occur.

• Actual execution plan of query where partitions aren't eliminated.

Eventually, I will add index(es) and post back here if I have questions about that. I don't want to proceed until I'm comfortable with answers given in this post.

    --step 2 (after creating db)
    ALTER DATABASE partitionresearch
    ADD FILEGROUP January
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP February
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP March
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP April
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP May
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP June
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP July
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP August
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP September
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP October
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP November
    GO
    ALTER DATABASE partitionresearch
    ADD FILEGROUP December
    GO
    --step 3
    -- Table Partitioning in SQL Server
        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartJan],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartJan.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [January]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartFeb],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartFeb.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [February]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartMar],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartMar.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [March]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartApr],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartApr.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [April]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartMay],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartMay.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [May]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartJun],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartJun.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [June]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartJul],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartJul.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [July]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartAug],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartAug.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [August]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartSep],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartSep.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [September]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartOct],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartOct.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [October]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartNov],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartNov.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [November]

        ALTER DATABASE [Partitionresearch]
        ADD FILE 
        (
        NAME = [PartDec],
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartDec.ndf',
            SIZE = 5080 KB, 
            MAXSIZE = UNLIMITED, 
            FILEGROWTH = 2040 KB
        ) TO FILEGROUP [December]

    --step 4
    -- Table Partitioning in SQL Server
    USE Partitionresearch
    GO

    CREATE PARTITION FUNCTION [MonthlyPartition] (date)
    AS RANGE RIGHT FOR VALUES ('20190201', '20190301', '20190401',
                   '20190501', '20190601', '20190701', '20190801', 
                   '20190901', '20191001', '20191101', '20191201');

    --step 5
    -- Table Partitioning in SQL Server
    USE Partitionresearch
    GO

    CREATE PARTITION SCHEME MonthWisePartition
    AS PARTITION MonthlyPartition
            TO (January, February, March, April, May, June, July, 
                August, September, October, November, December
                );
    --step 6
    create table dbo.partitionresearch 
    (
    tranid int identity(1,1),
    [Date] date,

    Account int,
    SeqNumber tinyint,
    AlertType int,
    IsFirst tinyint,
    Indicator1 int,
    [time] time
    )
    on monthwisepartition([date])

    --with partitioning help - 40 seconds (as opposed to 3 min 46 sec) , hovered over table scan and didnt see partition count, but clearly partitions (elimination) were used
    --did see scalar operators with values 5 and 10 which happens to be where these accounts are partition wise (may and october)
    use partitionresearch
    select * from dbo.partitionresearch --hoverd over and closest thing to partn help i saw were scalar operators 5 and 10
    where (date between '5/1/2019' and '5/31/2019' or date between '10/1/2019' and '10/31/2019') and
          account in (1000000,2000000) 
    ------------------------------------------------------------------------------------------------------------------------
    --with "partition help" from a lookup table--3 minutes 33 seconds
    use partitionresearch
    select a.* from dbo.partitionresearch a--hovered over and believe partns wont be used
    join [dbo].[monthlookup] b
    on a.date=b.date
    where account in (1000000,2000000) 
    ------------------------------------------------------------------------------------------------------------------------
--this is the date lookup table which isnt partitioned, thus not aligned
USE [partitionresearch]
GO

/****** Object:  Table [dbo].[monthlookup]    Script Date: 7/12/2019 6:24:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[monthlookup](
    [monthid] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL
) ON [PRIMARY]
GO

Answer 1

This isn't available in the product for rowstore partitioned heaps. If you change the table to have a partitioned clustered columnstore index then you will sometimes be able to eliminate partitions via rowgroup elimination by a bitmap filter, which seems to be what you're after.

I blogged about this here. Quoting a small section:

We know that based on the data in the dimension table that SQL Server only needs to read two partitions from the fact table. Could the query optimizer in theory do better than it did? Consider the fact that a partitioned table has at most 15000 partitions. All of the partition values cannot overlap and they don’t change without a DDL operation. When building the hash table the query optimizer could keep track of which partitions have at least one row in them. By the end of the hash build we’ll know exactly which partitions could contain data, so the rest of the partitions could be skipped during the probe phase.

Perhaps this isn’t implemented because it’s important for the hash build to be independent of the probe. Maybe there’s no guarantee available at the right time that the bitmap operator will be pushed all the way down to the scan as opposed to a repartition streams operator. Perhaps this isn’t a common case and the optimization isn’t worth the effort. After all, how often do you join on the partitioning column instead of filtering by it?

Answer 2

Just for completeness, you can get dynamic partition elimination, but only if the join type is nested loops with correlated parameters.

For example, using the provided partitioning function and scheme:

CREATE PARTITION FUNCTION MonthlyPartition ([date])
AS RANGE RIGHT FOR VALUES
(
    '20190201', '20190301', '20190401',
    '20190501', '20190601', '20190701', '20190801', 
    '20190901', '20191001', '20191101', '20191201'
);

CREATE PARTITION SCHEME MonthWisePartition
AS PARTITION MonthlyPartition ALL TO ([PRIMARY]);

and tables:

CREATE TABLE dbo.PartitionResearch 
(
    tranid integer identity(1,1) NOT NULL,
    [Date] date NULL,
    Account integer NULL,
    SeqNumber tinyint NULL,
    AlertType integer NULL,
    IsFirst tinyint NULL,
    Indicator1 integer NULL,
    [time] time NULL
)
ON MonthWisePartition([Date]);

CREATE TABLE dbo.MonthLookup
(
    [MonthId] integer IDENTITY(1,1) NOT NULL,
    [Date] date NOT NULL
)
ON [PRIMARY];

The query that did not use partition elimination:

SELECT
    a.* 
FROM dbo.PartitionResearch AS a
JOIN dbo.MonthLookup AS b
    ON a.[Date]=b.[Date]
WHERE
    a.Account IN (1000000,2000000);

...produces the following plan:

The properties of the PartitionResearch table scan show dynamic partition elimination using the current date from the MonthLookup table on each iteration of the loop:

That option is preferred here because the tables are empty. In your case the optimizer preferred a hash join plan for estimated cost reasons. Given a MonthLookup table with 56 rows (as shown in your plans), the nested loops alternative would scan a single partition 56 times. The optimizer (probably rightly) assesses that it would be better to scan all 12 partitions once instead.

If you want to test your data with dynamic partition elimination, you may get such a plan with an OPTION (LOOP JOIN) query hint. With only two partitions accessed by the example query, it is at least plausible that two partitions could be scanned 28 times each in reasonable time.

For a more robust general strategy, you would need to write specific T-SQL to achieve partition elimination, for example using the $PARTITION function, a temporary table, or dynamic SQL.

Answer 3

After better understanding the direction given by this community, I was tempted to try a prototype where the SQL is generated dynamically based on what the lookup table contains (i.e. no join). Possibly after changing the partition column to a new column composed of just year and month.

I wanted to base the dynamic SQL on 1-12 variables as needed with a recompile option. Instead (see code below), and in the interest of saving time, I simply proved to myself that in theory dynamic SQL could be leveraged to work around any (within reason) limitation related to lookup tables and partition elimination.

I built 2 strings. One with 2 sets of date range variable declarations (@low1,@high1,@low2,@high2). One with a select and predicate that references the date range variables and two target accounts. I executed the concatenation of the two strings and am confident partition elimination was used. The select had an option(recompile) on the end. Cache was cleared prior to compare run times from one trial to the next...apples to apples. Partly to be sure that partition elimination really occurred.

declare @sql1 varchar(500)=
'declare @low1 date ='+'''' +'5/1/19'+'''' +
'declare @low2 date ='+'''' +'10/1/19'+'''' +
'declare @high1 date ='+''''+'5/31/19' +'''' +
'declare @high2 date ='+''''+'10/31/19'+'''' 
declare @sql2 varchar(500)=
'select * from dbo.partitionresearch 
where (date between @low1 and @high1 or date between @low2 and @high2) and
      account in (1000000,2000000) 
OPTION (RECOMPILE)'
CHECKPOINT 
DBCC DROPCLEANBUFFERS
exec (@sql1+@sql2)