Sql-server – No partition elimination for partition column values in a lookup table

execution-planpartitioningsql serversql-server-2017

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.

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

Best Answer

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?

Related Question