How can I enable partition elimination to reduce the number of partitions read in my procedure?

-1 votes

0 answers

22 views

                          Question : How can I enable partition elimination so it runs faster and scans fewer partitions?

I'm working with a large partitioned table and trying to improve performance by ensuring only the relevant partition is scanned. However, the execution plan shows all 590 partitions are being accessed.

Environment:

azure sql paas 

Table is partitioned by Created using RANGE RIGHT

Clustered primary key on (Id, Created)

The procedure:


    CREATE PROCEDURE dbo.GetNextPage
    (
        @Next BIGINT,
        @Limit INT
    )
    AS
    BEGIN
        SELECT TOP (@Limit) [RowId], [RecordKey], [GroupId], [SourceSystem], [SchemaVersion], [RecordType], [Payload], [CreatedDate]
        FROM dbo.Event
        WHERE RowId > @Next
        ORDER BY RowId ASC
    END

The query returns results, but the execution plan shows it seeks all partitions.


                        

Asked by dexon (65 rep)

Aug 3, 2025, 03:16 PM

How can I enable partition elimination to reduce the number of partitions read in my procedure?

Related Questions