變更資料庫相容性層級並使用查詢存放區Change the Database Compatibility Level and use the Query Store

適用於: 是SQL Server (僅限 Windows) 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (Windows only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017 中,某些變更只在資料庫相容性層級變更後才啟用。In SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017, some changes are only enabled once the database compatibility level has been changed. 有數種原因可以完成這項作業:This was done for several reasons:

  • 因為升級是單向作業 (不可能降級檔案格式),所以可以將啟用新功能區隔到資料庫內的個別作業。Since upgrade is a one-way operation (it is not possible to downgrade the file format), there is value in separating the enablement of new features to a separate operation within the database. 設定可還原為先前的資料庫相容性層級。It is possible to revert a setting to a prior database compatibility level. 新的模型可減少必須在中斷期間發生的事項數目。The new model reduces the number of things that must happen during an outage window.

  • 查詢處理器的變更會有複雜的影響。Changes to the query processor can have complex effects. 即使對系統進行「良好」變更對多數工作負載而言可能有益,但可能造成其他工作負載之重要查詢發生無法接受的迴歸。Even though a "good" change to the system may be great for most workloads - it may cause an unacceptable regression on an important query for others. 將此邏輯與升級程序區隔可讓功能 (例如查詢存放區) 快速降低計畫選擇迴歸,或甚至在生產伺服器中予以完全避免。Separating this logic from the upgrade process, allows for features such as the Query Store, to mitigate plan choice regressions quickly or even avoid them completely in production servers.

重要

附加或還原資料庫以及就地升級之後,SQL Server 2017 (14.x)SQL Server 2017 (14.x) 預期會有下列行為:The below behaviors are expected for SQL Server 2017 (14.x)SQL Server 2017 (14.x) when a database is attached or restored, and after an in-place upgrade:

  • 如果使用者資料庫的相容性層級在升級前為 100 或更高層級,則在升級後仍會保持相同。If the compatibility level of a user database was 100 or higher before the upgrade, it remains the same after upgrade.
  • 如果使用者資料庫在升級前的相容性層級為 90,則在升級後的資料庫中,相容性層級會設定為 100,這是 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 支援的最低相容性層級)。If the compatibility level of a user database was 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2017 (14.x)SQL Server 2017 (14.x).
  • tempdb、model、msdb 和 Resource 資料庫的相容性層級在升級之後會設定為目前相容性層級。The compatibility levels of the tempdb, model, msdb and Resource databases are set to the current compatibility level after upgrade.
  • master 系統資料庫會繼續保有升級前的相容性層級。The master system database retains the compatibility level it had before upgrade.

啟用新查詢處理器功能的升級程序與產品的發行後服務模型有關。The upgrade process to enable new query processor functionality is related to the post-release servicing model of the product. 其中某些修正是在追蹤旗標 4199 下所發行。Some of those fixes are released under trace flag 4199. 需要修正程式的客戶可以選擇這些修正程式,而不會造成其他客戶的非預期衰退。Customers needing fixes can opt-in to those fixes without causing unexpected regressions for other customers. 查詢處理器 Hotfix 的發行後服務模型記載在 這裡The post-release servicing model for query processor hotfixes is documented here. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,移到新的相容性層級表示不再需要追蹤旗標 4199,原因是現在預設會在最新的相容性層級中啟用那些修正。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), moving to a new compatibility level implies that trace flag 4199 is no longer needed, because those fixes are now enabled by default in the latest compatibility level. 因此,在升級程序期間,一定要確認在升級程序完成之後未啟用 4199。Therefore, as part of the upgrade process, it is important to validate that 4199 is not enabled once the upgrade process completes.

注意

不過,如果適用的話,仍需追蹤旗標 4199,以啟用在 RTM 後發行的任何新查詢處理器修正。However, trace flag 4199 is still needed to enable any new query processor fixes released after RTM, if applicable.

如需將查詢處理器程式碼升級至最新版,建議遵循以下工作流程,其說明請參閱查詢存放區使用案例的在升級至較新 SQL Server 期間保持效能穩定性一節The recommended workflow for upgrading the query processor to the latest version of the code is documented in the Keep performance stability during the upgrade to newer SQL Server section of Query Store Usage Scenarios, as seen below.

query-store-usage-5query-store-usage-5

SQL Server Management StudioSQL Server Management Studio v18 開始,可以使用 [查詢調整小幫手] 引導使用者完成建議的工作流程。Starting with SQL Server Management StudioSQL Server Management Studio v18, users can be guided through the recommended workflow using the Query Tuning Assistant. 如需詳細資訊,請參閱使用查詢調整小幫手來升級資料庫For more information, see Upgrading Databases by using the Query Tuning Assistant.

另請參閱See Also

檢視或變更資料庫的相容性層級 View or Change the Compatibility Level of a Database
查詢存放區使用案例 Query Store Usage Scenarios
ALTER DATABASE (Transact-SQL) 相容性層級 ALTER DATABASE (Transact-SQL) Compatibility Level
使用查詢調整小幫手來升級資料庫Upgrading Databases by using the Query Tuning Assistant