Одним из наиболее интересных нововведений в SQL Server 2016 (а также в Azure SQL Database V12) является возможность контролировать некоторые параметры, напрямую связанные с производительностью, на уровне базы данных, а не сервера или запроса, как было раньше. Несомненно, это огромный плюс, т.к. обычно с разными базами данных работают разные приложения, которые могут требовать своих требований к настройкам. Также это позволяет более гибко управлять этими настройками, т.к. определенные пользователи с правами на указанные базы уже могут менять эти параметры, нет необходимости расширять им права до уровня сервера. И еще один интересный момент, если ваши базы данных участвуют в AlwaysOn Availability Groups или в GeoReplication для Azure, то вы можете даже выставить разные настройки для главной (PRIMARY) базы и для вторичных реплик (SECONDARY), т.к. опять же, у вторичных реплик может быть совсем другой сценарий использования и требования, чем у первичной базы данных.

Эта новая опция называется Database Scoped Configuration. На текущий релиз в нее входят следующие параметры:

• Возможность очистить процедурный кэш только для конкретной базы данных. Раньше это можно было сделать либо для всего сервера, либо для конкретного запроса по его plan handle.
• MAXDOP – уровень параллелизма по умолчанию. 0 – будут использоваться настройки сервера. Эта настройка перебивает соответствующую на уровне сервера, однако, подсказки на уровне запросов обладают высшим приоритетом. При этом не забываем, что Resource Governor все равно всех ограничивает сверху. Подробнее о его работе вы можете прочитать тут.
• Parameter Sniffing – включить или выключить. Выключение аналогично trace flag 4136. На уровне запроса этим можно было управлять с помощью подсказки OPTIMIZE FOR UNKNOWN.
• Управление, какую версию Cardinality Estimator использовать (новую, появившуюся в SQL Server 2014, либо предыдущую).
• Оптимизатор запросов со всеми последними обновлениями. Аналог trace flag 4199.

Для того, чтобы внести изменения, необходимо обладать правами ALTER ANY DATABASE SCOPED CONFIGURATION на уровне базы данных. Такие права может выдать любой, у кого есть право CONTROL на базу данных.

grant alter any database scoped configuration to [MyUser];

Если вы изменяете настройки в Database Scoped Configuration, то они автоматически транслируются на вторичные реплики, если база данных участвует в AlwaysOn AG или Geo Replication. Если же вы хотите для вторичных реплик выставить свои отдельные значения, то необходимо будет использовать ключевое слово FOR SECONDARY. Например, ниже вы увидите пример изменения настройки MAXDOP для главной базы данных, а во втором случае для вторичных реплик. Все вторичные реплики получают одинаковые настройки. Нет возможности выставить свои отдельные параметры для разных вторичных реплик.

alter database scoped configuration set maxdop = 4;
alter database scoped configuration for secondary set maxdop = 2;

Также параметры с опцией FOR SECONDARY могут принимать значение PRIMARY. Это будет означать, что настройки будут идентичными, что и на основной базе данных.

alter database scoped configuration for secondary set maxdop = primary;

Примеры выключения parameter sniffing для основной базы данных и вторичных реплик:

alter database scoped configuration set parameter_sniffing = off;
alter database scoped configuration for secondary set parameter_sniffing = off;

Возможность вернуть старую версию Cardinality Estimator:

alter database scoped configuration set legacy_cardinality_estimation = on;

Активация нового оптимизатора запросов со всеми исправлениями:

alter database scoped configuration set query_optimizer_hotfixes = on;

Очистка процедурного кэша для отдельной базы данных (доступно только для основной БД):

alter database scoped configuration clear procedure_cache;

Также вам доступен просмотр всех текущи значений в Database Scoped Configuration через системное представление sys.database_scoped_configurations.

Как мы видим, этот новый функционал позволяет очень гибко настраивать различные параметры, которые раньше были доступны только на уровне trace flag, либо недоступны на уровне базы вообще. Также это несомненно облегчает консолидацию нескольких баз данных с различными требованиями к настройкам на одном сервере.