SQL2016 Spatial index not using partition elimination even after update

Chris Singleton 1 Reputation point
2021-10-20T17:02:43.643+00:00

I have a large table (10's of millions of rows per day) which is partitioned by time and includes a geography column with a spatial index.
The following query, 

    DECLARE @polygon geography = geography::STGeomFromText('POLYGON((-2.797749 30.595396, 40.908162 30.595396, 40.908162 40.523745, -2.797749 40.523745, -2.797749 30.595396))', 4326);
    SELECT * FROM Locations WHERE Time > '2021-01-01T23:59:59.997' AND Time <= '2021-01-02T23:59:59.997' AND @polygon.STContains(Location) = 1;

according to the SSMS query statistics, first uses the spatial index but doesn't apply partition elimination to the index which will result in looking over billions of rows.

According to the following, https://support.microsoft.com/en-us/topic/kb4089950-update-to-support-partition-elimination-in-query-plans-that-have-spatial-indexes-in-sql-server-2016-and-2017-d1f87c36-4738-cd04-1e2c-baf455288f50, an update was released to allow this to function properly but even after applying I'm still having the issue of no partition elimination occurring on the spatial index.

Partition elimination works as expected for partitioned views, but I'd rather not use them if possible.

Is it possible to use partition elimination using spatial indexes in SQL Server 2016?

Thanks in advance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,436 Reputation points
    2021-10-21T08:37:26.683+00:00

    Hi @Chris Singleton ，

    Could you check this article for some ideas:
    https://www.littlekendra.com/2015/11/17/did-my-query-eliminate-table-partitions-sql-server/
    BTW, did you have any error message?

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.