大网数据广告活动
数据库与应用安全

Upgrade compatibility level degrades performance

· 52CE 我爱测

This article provides resolutions for the problem that occurs when you upgrade database compatibility level from 120 to 130.

This article helps you resolve the problem that occurs when you upgrade database compatibility level from 120 to 130.

Original product version:   SQL Server 2016, SQL Server 2017 on Windows (all editions)
Original KB number:   3212023

Summary

Consider the following scenario:

  • You run a spatial query by using a filter with a function such as STIntersects in SQL Server 2016 or in SQL Server 2017 on Windows.
  • 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.

Resolution

To address this issue, try one of the following methods:

  • 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 or in SQL Server 2017 on Windows 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 (Transact-SQL) Compatibility Level.

  • 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 Hints (Transact-SQL) - Query.

    Alternatively, use Query Store to identify and fix specific plan choices. For more information about using Query Store for this purpose, see Query Store Usage Scenarios.

More information

SQL Server database compatibility levels 120 and 130 use different approaches to estimating cardinality of predicates, where a scalar user-defined function or a particular T-SQL function (such as STIntersects) is compared to a constant.

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.

Applies to

  • SQL Server 2017 on Windows (all editions)
  • SQL Server 2016 Service Pack 1
  • SQL Server 2016 Developer
  • SQL Server 2016 Enterprise
  • SQL Server 2016 Express
  • SQL Server 2016 Standard
  • SQL Server 2016 Web

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 09/11/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。