- You run a spatial query by using a filter with a function such as STIntersects in SQL Server 2016.
- The database compatibility level is 120.
- The query uses a parallel execution plan.
- You upgrade the database compatibility level to 130, and the execution plan has changed from parallel to serial.
In this scenario, you experience performance degradation if the query returns a large result set.
- Revert your database compatibility level to 120. When you do this, you will not be able to benefit from some of the functionality that's available in SQL Server 2016 under database compatibility level 130. However, you will still be able to realize many improvements that are not bound to the database compatibility level—for example, an overall performance improvement of query operations with spatial data types.
For a list of SQL Server 2016 enhancements that require database compatibility level 130, see ALTER DATABASE Compatibility Level (Transact-SQL).
- Force the plan that's generated with database compatibility level 120 if it provides better performance. You can force this plan while running with database compatibility level 130 by using the USE PLAN query hint. For more information about using hints, see Query Hints (Transact-SQL).
Alternatively, use Query Store to identify and fix specific plan choices. For more information about using Query Store for this purpose, see Pinpoint and fix queries with plan choice regressions.
Although the cost model that's used by database compatibility level 130 generates performance gains for many workloads as compared to level 120, for some queries (depending on the data and functions used) performance of query plans may actually decrease at level 130.
Article ID: 3212023 - Last Review: 14 Dec 2016 - Revision: 1