SQL Server 2016数据库范围的配置

最新推荐文章于 2023-06-09 18:30:19 发布

culuo4781

最新推荐文章于 2023-06-09 18:30:19 发布

阅读量1.4k

点赞数

文章标签：数据库 linux mysql java python

原文链接：https://www.sqlshack.com/sql-server-2016-database-scoped-configuration/

版权

SQL Server 2016 introduces a new set of configurations that can be applied at the database level to control its performance and behavior. These configurations were previously applicable only at the SQL instance level before version SQL Server 2016, With the ability to configure settings at the database level, many databases that are hosted in the same instance can now be isolated from each other, with each database has its own customized configurations. The database level configurations allow us also to set different database configurations for the Always On Availability Groups primary and secondary databases to meet the different types of workloads.

SQL Server 2016引入了一组新的配置，可以在数据库级别应用这些配置以控制其性能和行为。这些配置以前仅适用于版本SQL Server 2016之前SQL实例级别。由于能够在数据库级别配置设置，因此现在可以将位于同一实例中的许多数据库彼此隔离，因为每个数据库都有自己的数据库。自己的定制配置。数据库级别的配置还允许我们为Always On Availability Groups主数据库和辅助数据库设置不同的数据库配置，以满足不同类型的工作负载。

A new T-SQL statement ALTER DATABASE SCOPED CONFIGURATION has been introduced to set these new database level configurations for a particular database. These database scoped configurations can be viewed from the Options tab of the Database Properties window as follows:

引入了新的T-SQL语句ALTER DATABASE SCOPED CONFIGURATION来为特定数据库设置这些新的数据库级别配置。可以从“数据库属性”窗口的“选项”选项卡中查看这些数据库范围的配置，如下所示：

The first database scoped configuration that is used to override the server’s default setting for a specific database is the MAXDOP option. MAXDOP is used in SQL Server to configure the maximum degree of parallelism, which specifies the number of processors used by each parallel query, at the database level. The MAXDOP option can take numeric values such as 1,2,3…etc, in addition to the PRIMARY value, which assigns the MAXDOP of the primary database to the secondary one if the Always On Availability Group is configured. In order to be able to configure database scoped configurations, the user should be granted ALTER ANY DATABASE SCOPED CONFIGURATION permission on that database. The following statement allows our example user, Suheir, to configure the database level configurations:

用于覆盖特定数据库服务器默认设置的第一个数据库范围配置是MAXDOP选项。 MAXDOP在SQL Server中用于配置最大并行度，该最大并行度指定在数据库级别上每个并行查询使用的处理器数量。除了PRIMARY值之外，MAXDOP选项还可以采用数字值，例如1,2,3…等，如果配置了Always On可用性组，则PRIMARY值会将主数据库的MAXDOP分配给辅助数据库。为了能够配置数据库范围的配置，应该授予用户该数据库的ALTER ANY DATABASE SCOPED CONFIGURATION权限。以下语句允许我们的示例用户Suheir配置数据库级别的配置：

 
GRANT ALTER ANY DATABASE SCOPED CONFIGURATION to Suheir

Let’s check the SQL Server instance level MAXDOP value as below by querying the sys.configurations system object:

让我们通过查询sys.configurations系统对象来检查SQL Server实例级别的MAXDOP值，如下所示：

 
SELECT name AS configName,value_in_use As ConfigValue
FROM sys.configurations
WHERE name IN ('max degree of parallelism','cost threshold for parallelism');

You will see that the server is configure to assign 2 processors for any query with cost more than 2:

您将看到服务器已配置为成本超过2的任何查询分配2个处理器：

If we run the below query that change the MAXDOP value of the SQLShackDemo database to use a single processor only:

如果我们运行下面的查询，将SQLShackDemo数据库的MAXDOP值更改为仅使用单个处理器：

 
USE SQLShackDemo 
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP =1 ;

And try to run a complex query with cost more than 2, you will find that the query generated using the APEXSQL Plan application will use a serial plan as the MAXDOP value 1 at the database level overrides the MAXDOP value 2 at the server level:

并尝试运行一个成本超过2的复杂查询，您会发现使用APEXSQL Plan应用程序生成的查询将使用一个串行计划，因为数据库级别的MAXDOP值1覆盖服务器级别的MAXDOP值2：

SQL Server allows you also to configure the MAXDOP value for the secondary database with value different from the value set to the primary server depending on the workload type performed on these replicas as follows:

SQL Server还允许您为辅助数据库配置MAXDOP值，该值与为主服务器设置的值不同，具体取决于在这些副本上执行的工作负载类型，如下所示：

 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=4 ;

Or set the MAXDOP value at the secondary database with the same primary database MAXDOP value if you are not sure what the primary value is, as below:

或者，如果不确定主要值是什么，则在辅助数据库中将MAXDOP值设置为与主要数据库的MAXDOP值相同，如下所示：

 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=PRIMARY ;

The second database scoped configuration is the LEGACY_CARDINALITY_ESTIMATION option, which is used to enable or disable the legacy query optimizer Cardinality Estimation model. The Cardinality Estimation, or simply CE, is used in SQL Server to predicate the number of rows that will be returned from your query, which will be used to generate the best plan for that query. In SQL Server 2014 and later, the Cardinality Estimation is updated to work well with the modern OLTP and OLAP workloads. If you are using SQL Server 2014 or later and find that the modern CE is not suiting your workload and impacting the queries performance, you can turn on the legacy CE simply without changing the database compatibility to previous SQL Server versions and lose all new enhancements in the new SQL Server versions using the below query, which is equivalent to turning on the Trace Flag 9481:

第二个数据库范围的配置是LEGACY_CARDINALITY_ESTIMATION选项，该选项用于启用或禁用旧版查询优化器基数估计模型。在SQL Server中，基数估计（或简称为CE）用于确定从查询中返回的行数，这些行数将用于生成该查询的最佳计划。在SQL Server 2014及更高版本中，基数估计已更新，可与现代OLTP和OLAP工作负载很好地配合使用。如果您使用的是SQL Server 2014或更高版本，并且发现现代CE不适合您的工作量并影响查询性能，则只需打开旧版CE，而无需将数据库兼容性更改为以前SQL Server版本，并且会丢失所有新增强功能。使用以下查询SQL Server新版本，相当于打开跟踪标志9481 ：

 
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON ;

You can also configure the secondary database in the Always On Availability Groups with a value different from the primary database if there is a different workload type assigned to that database as below:

如果为该数据库分配了不同的工作负载类型，则还可以在Always On可用性组中为辅助数据库配置一个与主数据库不同的值，如下所示：

 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=OFF ;

Or assign it with the same value as the primary database:

或为其分配与主数据库相同的值：

 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET 
LEGACY_CARDINALITY_ESTIMATION=PRIMARY ;

The third database scoped configuration is the PARAMETER_SNIFFING option. When we have many common queries that have the same shape, the parameters will be useful to improve its performance to create one plan for all these queries rather than compiling each one of these queries, Improving the overall SQL Server performance. SQL Server tries to change the queries with no parameters into parameterized queries to take advantage of this performance enhancement. But in some cases with complex queries, the parameterizing may cause performance issues, where the plan that is suitable for one parameter will not fit the other parameters, so that SQL Server Engine performs a process in which it uses the parameter value to estimate the selectivity and cardinality. This process is called the Parameter Sniffing.

第三个数据库范围的配置是PARAMETER_SNIFFING选项。当我们有许多形状相同的常见查询时，这些参数将有助于提高其性能，从而为所有这些查询创建一个计划，而不是编译这些查询中的每一个，从而提高整体SQL Server性能。 SQL Server尝试将不带参数的查询更改为参数化查询，以利用此性能增强功能。但是在某些查询复杂的情况下，参数化可能会导致性能问题，其中适合一个参数的计划将不适合其他参数，因此SQL Server Engine执行一个过程，在该过程中，它使用参数值来估计选择性。和基数。此过程称为参数嗅探。

SQL Server allows you to enable and disable this feature at the database level. If you disable the PARAMETER_SNIFFING, you order the SQL Server Query Optimizer to use the SQL statistics instead of the initially provided input values. This process is equivalent to using the Trace Flag 4136. You can also disable it for your query by adding the OPTIMIZE FOR UNKNOWN query hint.

SQL Server允许您在数据库级别启用和禁用此功能。如果禁用PARAMETER_SNIFFING，则可以命令SQL Server查询优化器使用SQL统计信息而不是最初提供的输入值。此过程等效于使用跟踪标志4136。您还可以通过添加OPTIMIZE FOR UNKNOWN查询提示来禁用查询。

The below query will disable the PARAMETER_SNIFFING at the SQLShackDemo database level:

以下查询将在SQLShackDemo数据库级别禁用PARAMETER_SNIFFING：

 
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF ;

You can also disable this option on the Always On Availability Group secondary database to meet the secondary workload requirements:

您还可以在Always On可用性组辅助数据库上禁用此选项，以满足辅助工作负荷要求：

 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING=OFF  ;

Or assign it with the same setting as the primary replica:

或为其分配与主副本相同的设置：

 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING =PRIMARY  ;

Enabling the fourth database scoped configuration QUERY_OPTIMIZER_HOTFIXES allows you to take advantages of the latest hotfixes for the SQL Server Query Optimizer, which is disabled by default. This will work same as enabling the Trace Flag 4199 in your SQL Server.

启用第四个数据库范围的配置QUERY_OPTIMIZER_HOTFIXES可让您利用SQL Server Query Optimizer的最新修补程序，该修补程序默认情况下处于禁用状态。这将与在SQL Server中启用跟踪标志4199相同。

The below query is used to enable your database to work with the latest hotfixes of the SQL Server Query Optimizer:

以下查询用于使您的数据库能够使用SQL Server查询优化器的最新修补程序：

 
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON ;

With the ability to set the QUERY_OPTIMIZER_HOTFIXES value on the secondary database to be same as the primary database as follows:

可以将辅助数据库上的QUERY_OPTIMIZER_HOTFIXES值设置为与主数据库相同，如下所示：

 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES =PRIMARY

The last database scoped configuration that is not mentioned within the Database Properties Options tab and can be configured only using the ALTER DATABASE T-SQL statement is the CLEAR PROCEDURE_CACHE option. This option allows you to clear the procedures cache at the current database only. The CLEAR PROCEDURE_CACHE option will affect the current SQL cache memory status without having any options to be assigned to it, this is why you will not find it within the SSMS database configurations.

“数据库属性选项”选项卡中未提及且只能使用ALTER DATABASE T-SQL语句进行配置的最后一个数据库范围的配置是CLEAR PROCEDURE_CACHE选项。该选项允许您仅清除当前数据库上的过程高速缓存。 CLEAR PROCEDURE_CACHE选项将影响当前SQL高速缓存内存状态，而没有为其分配任何选项，这就是为什么您无法在SSMS数据库配置中找到它。

If we run the below query to return the number of objects in the plan cache per each database before cleaning the cache for the AdventureWorksDW2012 database:

如果我们运行以下查询以返回每个数据库在计划缓存中的对象数，然后清除AdventureWorksDW2012数据库的缓存：

 
USE master;
GO
 SELECT
    DBS.name AS DatabaseName,count(*) as NumOfcacheEntries
FROM sys.dm_exec_cached_plans AS C_Plan
CROSS APPLY (
    SELECT CAST(Plan_Attr.value AS INT) AS dbs_id
    FROM sys.dm_exec_plan_attributes(C_Plan.plan_handle) AS Plan_Attr
    WHERE Plan_Attr.attribute = N'dbid'
) AS All_DataBases
INNER JOIN sys.databases AS DBS
    ON DBS.database_id = All_DataBases.dbs_id
GROUP BY DBS.name
ORDER BY DatabaseName asc

The result in our case will be like:

在我们的案例中，结果将是：

The below query will clear the procedure cache at the AdventureWorksDW2012 database level only, with no option to perform that on the secondary database:

下面的查询将仅清除AdventureWorksDW2012数据库级别的过程高速缓存，没有选择在辅助数据库上执行的过程：

 
USE AdventureWorksDW2012 
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ;

If you check the number of cache entries again, you will find that the previous cache cleanup query take effects only at the AdventureWorksDW2012 database level only as in the below result:

如果再次检查高速缓存条目的数量，则会发现以前的高速缓存清理查询仅在AdventureWorksDW2012数据库级别上生效，如下所示：

A new DMV sys.database_scoped_configurations introduced in SQL Server 2016 to retrieve the database scoped configurations values for both the primary and secondary replicas of the current database:

SQL Server 2016中引入了新的DMV sys.database_scoped_configuration ，用于检索当前数据库的主副本和辅助副本的数据库范围配置值：

 
SELECT * FROM  sys.database_scoped_configurations;

The SQLShackDemo database scoped configuration will be like:

SQLShackDemo数据库范围的配置将类似于：

Where the NULL values for the secondary means that the primary replica values will be used.

次要对象的NULL值表示将使用主副本值。

结论 (Conclusion)

SQL Server 2016 comes with many new features and enhancements to the current features. One of the new enhancements in SQL Server 2016 is the new Database Scoped Configuration; which allows you to overcome the default related server’s configuration and configure each database with that setting to meet each database or application requirements. These new configurations can be isolated at the replica level too, where you can configure the primary replica with a specific setting and the secondary replica which is used to handle another workload type with another setting. Just test these setting on your test environment, then apply what you find it suitable on the production environment and enjoy the new Database Scoped Configuration feature.

SQL Server 2016附带了许多新功能，并对当前功能进行了增强。 SQL Server 2016中的新增强功能之一是新的数据库范围配置。这使您可以克服默认的相关服务器的配置，并使用该设置来配置每个数据库，以满足每个数据库或应用程序的要求。这些新配置也可以在副本级别隔离，您可以在其中配置具有特定设置的主副本和用于处理具有另一设置的另一种工作负载类型的辅助副本。只需在测试环境中测试这些设置，然后在生产环境中应用您认为合适的设置，即可享受新的数据库范围配置功能。

有用的链接 (Useful Links)

翻译自: https://www.sqlshack.com/sql-server-2016-database-scoped-configuration/

culuo4781

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL Server 2016数据库范围的配置

SQL Server 2016 introduces a new set of configurations that can be applied at the database level to control its performance and behavior. These configurations were previously applicable only at th...
复制链接

扫一扫