SQL2016 Spatial index not using partition elimination even after update

Question

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

Answer 1

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.