{"id":1068,"date":"2016-03-07T14:05:58","date_gmt":"2016-03-07T22:05:58","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1068"},"modified":"2017-04-13T12:28:57","modified_gmt":"2017-04-13T19:28:57","slug":"using-alter-database-scoped-configuration-in-sql-server-2016","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/","title":{"rendered":"Using ALTER DATABASE SCOPED CONFIGURATION in SQL Server 2016"},"content":{"rendered":"<p>On March 7, 2016, <a href=\"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2016\/03\/07\/first-release-candidate-of-sql-server-2016-now-available\/\">Microsoft announced the availability of the RC0 Build of SQL Server 2016<\/a>. One of the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-scoped-configuration-transact-sql\">new features<\/a> in the RC0 Build is Database Scoped Configurations, which gives you the ability to easily make several database-level configuration changes for things that were previously configured at the instance-level. <\/p>\n<p>These include:<\/p>\n<ul>\n<ul>\n<li>Setting MAXDOP for an individual database<\/li>\n<li>Setting the query optimizer cardinality estimation model for an individual database (independent of the database compatibility level)<\/li>\n<li>Enable or disable parameter sniffing for an individual database<\/li>\n<li>Enable or disable query optimization hotfixes for an individual database (equivalent to TF 4199)<\/li>\n<li>The ability to clear the plan cache for an individual database (without using a DBCC command)<\/li>\n<\/ul>\n<\/ul>\n<p>If you are using AlwaysOn Availability Groups, the first four commands can also be used to affect the database-level configuration setting for ALL of the secondary database copies that are in a AG. You can set these values for just the Primary database, set the values in the secondary database(s) to the same value as the Primary database, or set the values in the secondary database(s) to different values from the Primary database.<\/p>\n<p>There is also a new DMV, <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-catalog-views\/sys-database-scoped-configurations-transact-sql\">sys.database_scoped_configurations<\/a>, which lets you see the configuration values for the current database, including the values for the Primary database and the secondary databases (if that database is part of an AlwaysOn AG). I have examples of these commands in the code in Figure 1.<\/p>\n<div id=\"codeSnippetWrapper\" style=\"overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4\">\n<div id=\"codeSnippet\" style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\">\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum1\" style=\"color: #606060\">   1:<\/span> <span style=\"color: #008000\">-- Database scoped T-SQL Commands in SQL Server 2016 RC0<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum2\" style=\"color: #606060\">   2:<\/span> <span style=\"color: #008000\">-- Glenn Berry<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum3\" style=\"color: #606060\">   3:<\/span> <span style=\"color: #008000\">-- SQLskills.com<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum4\" style=\"color: #606060\">   4:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum5\" style=\"color: #606060\">   5:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum6\" style=\"color: #606060\">   6:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum7\" style=\"color: #606060\">   7:<\/span> <span style=\"color: #008000\">-- Get database scoped configuration values for current database <\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum8\" style=\"color: #606060\">   8:<\/span> <span style=\"color: #0000ff\">SELECT<\/span> configuration_id, name, [<span style=\"color: #0000ff\">value<\/span>] <span style=\"color: #0000ff\">AS<\/span> [value_for_primary], value_for_secondary<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum9\" style=\"color: #606060\">   9:<\/span> <span style=\"color: #0000ff\">FROM<\/span> sys.database_scoped_configurations <span style=\"color: #0000ff\">WITH<\/span> (NOLOCK) <span style=\"color: #0000ff\">OPTION<\/span> (RECOMPILE);<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum10\" style=\"color: #606060\">  10:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum11\" style=\"color: #606060\">  11:<\/span> <span style=\"color: #008000\">-- ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum12\" style=\"color: #606060\">  12:<\/span> <span style=\"color: #008000\">-- https:\/\/msdn.microsoft.com\/en-us\/library\/mt629158.aspx<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum13\" style=\"color: #606060\">  13:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum14\" style=\"color: #606060\">  14:<\/span> <span style=\"color: #008000\">-- The commands for the secondary database affect ALL secondary databases in an AlwaysOn AG<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum15\" style=\"color: #606060\">  15:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum16\" style=\"color: #606060\">  16:<\/span> <span style=\"color: #0000ff\">USE<\/span> [CHECKDBTest]<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum17\" style=\"color: #606060\">  17:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum18\" style=\"color: #606060\">  18:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum19\" style=\"color: #606060\">  19:<\/span> <span style=\"color: #008000\">-- Enable legacy Cardinality Estimation for Primary database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum20\" style=\"color: #606060\">  20:<\/span> <span style=\"color: #008000\">-- Equivalent to using TF 4136, except this is only for this database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum21\" style=\"color: #606060\">  21:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum22\" style=\"color: #606060\">  22:<\/span> <span style=\"color: #0000ff\">SET<\/span> LEGACY_CARDINALITY_ESTIMATION = <span style=\"color: #0000ff\">ON<\/span>;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum23\" style=\"color: #606060\">  23:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum24\" style=\"color: #606060\">  24:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum25\" style=\"color: #606060\">  25:<\/span> <span style=\"color: #008000\">-- Enable legacy Cardinality Estimation for Secondary database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum26\" style=\"color: #606060\">  26:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <span style=\"color: #0000ff\">FOR<\/span> SECONDARY <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum27\" style=\"color: #606060\">  27:<\/span> <span style=\"color: #0000ff\">SET<\/span> LEGACY_CARDINALITY_ESTIMATION = <span style=\"color: #0000ff\">ON<\/span>;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum28\" style=\"color: #606060\">  28:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum29\" style=\"color: #606060\">  29:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum30\" style=\"color: #606060\">  30:<\/span> <span style=\"color: #008000\">-- Set legacy Cardinality Estimation for the Secondary database(s) to the same value as the Primary database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum31\" style=\"color: #606060\">  31:<\/span> <span style=\"color: #008000\">-- This is the default setting<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum32\" style=\"color: #606060\">  32:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <span style=\"color: #0000ff\">FOR<\/span> SECONDARY <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum33\" style=\"color: #606060\">  33:<\/span> <span style=\"color: #0000ff\">SET<\/span> LEGACY_CARDINALITY_ESTIMATION = <span style=\"color: #0000ff\">PRIMARY<\/span>;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum34\" style=\"color: #606060\">  34:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum35\" style=\"color: #606060\">  35:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum36\" style=\"color: #606060\">  36:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum37\" style=\"color: #606060\">  37:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum38\" style=\"color: #606060\">  38:<\/span> <span style=\"color: #008000\">-- Disable parameter sniffing for Primary database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum39\" style=\"color: #606060\">  39:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <span style=\"color: #0000ff\">SET<\/span> PARAMETER_SNIFFING = <span style=\"color: #0000ff\">OFF<\/span>;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum40\" style=\"color: #606060\">  40:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum41\" style=\"color: #606060\">  41:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum42\" style=\"color: #606060\">  42:<\/span> <span style=\"color: #008000\">-- Disable parameter sniffing for Secondary database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum43\" style=\"color: #606060\">  43:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <span style=\"color: #0000ff\">FOR<\/span> SECONDARY <span style=\"color: #0000ff\">SET<\/span> PARAMETER_SNIFFING = <span style=\"color: #0000ff\">OFF<\/span>;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum44\" style=\"color: #606060\">  44:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum45\" style=\"color: #606060\">  45:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum46\" style=\"color: #606060\">  46:<\/span> <span style=\"color: #008000\">-- Set parameter sniffing for the Secondary database(s) to the same value as the Primary database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum47\" style=\"color: #606060\">  47:<\/span> <span style=\"color: #008000\">-- This is the default setting<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum48\" style=\"color: #606060\">  48:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <span style=\"color: #0000ff\">FOR<\/span> SECONDARY    <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum49\" style=\"color: #606060\">  49:<\/span> <span style=\"color: #0000ff\">SET<\/span> PARAMETER_SNIFFING = <span style=\"color: #0000ff\">PRIMARY<\/span>;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum50\" style=\"color: #606060\">  50:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum51\" style=\"color: #606060\">  51:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum52\" style=\"color: #606060\">  52:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum53\" style=\"color: #606060\">  53:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum54\" style=\"color: #606060\">  54:<\/span> <span style=\"color: #008000\">-- Enable query optimizer fixes for Primary database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum55\" style=\"color: #606060\">  55:<\/span> <span style=\"color: #008000\">-- Equivalent to using TF 4199, except this is only for this database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum56\" style=\"color: #606060\">  56:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum57\" style=\"color: #606060\">  57:<\/span> <span style=\"color: #0000ff\">SET<\/span> QUERY_OPTIMIZER_HOTFIXES = <span style=\"color: #0000ff\">ON<\/span>;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum58\" style=\"color: #606060\">  58:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum59\" style=\"color: #606060\">  59:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum60\" style=\"color: #606060\">  60:<\/span> <span style=\"color: #008000\">-- Enable query optimizer fixes for Secondary database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum61\" style=\"color: #606060\">  61:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <span style=\"color: #0000ff\">FOR<\/span> SECONDARY <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum62\" style=\"color: #606060\">  62:<\/span> <span style=\"color: #0000ff\">SET<\/span> QUERY_OPTIMIZER_HOTFIXES = <span style=\"color: #0000ff\">ON<\/span>;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum63\" style=\"color: #606060\">  63:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum64\" style=\"color: #606060\">  64:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum65\" style=\"color: #606060\">  65:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum66\" style=\"color: #606060\">  66:<\/span> <span style=\"color: #008000\">-- Set MAXDOP for Primary database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum67\" style=\"color: #606060\">  67:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum68\" style=\"color: #606060\">  68:<\/span> <span style=\"color: #0000ff\">SET<\/span> MAXDOP = 4;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum69\" style=\"color: #606060\">  69:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum70\" style=\"color: #606060\">  70:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum71\" style=\"color: #606060\">  71:<\/span> <span style=\"color: #008000\">-- Set MAXDOP for Secondary database(s)<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum72\" style=\"color: #606060\">  72:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <span style=\"color: #0000ff\">FOR<\/span> SECONDARY <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum73\" style=\"color: #606060\">  73:<\/span> <span style=\"color: #0000ff\">SET<\/span> MAXDOP = 4;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum74\" style=\"color: #606060\">  74:<\/span> <span style=\"color: #0000ff\">GO<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum75\" style=\"color: #606060\">  75:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum76\" style=\"color: #606060\">  76:<\/span> <span style=\"color: #008000\">-- Set MAXDOP for the Secondary database(s) to the same value as the Primary database<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum77\" style=\"color: #606060\">  77:<\/span> <span style=\"color: #008000\">-- This is the default setting<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum78\" style=\"color: #606060\">  78:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION <span style=\"color: #0000ff\">FOR<\/span> SECONDARY <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum79\" style=\"color: #606060\">  79:<\/span> <span style=\"color: #0000ff\">SET<\/span> MAXDOP = <span style=\"color: #0000ff\">PRIMARY<\/span>; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum80\" style=\"color: #606060\">  80:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum81\" style=\"color: #606060\">  81:<\/span>&nbsp; <\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum82\" style=\"color: #606060\">  82:<\/span> <span style=\"color: #008000\">-- Clear plan cache for current database (only possible for Primary database)<\/span><\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white\"><span id=\"lnum83\" style=\"color: #606060\">  83:<\/span> <span style=\"color: #0000ff\">ALTER<\/span> <span style=\"color: #0000ff\">DATABASE<\/span> SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;<\/pre>\n<p><!--CRLF--><\/p>\n<pre style=\"border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span id=\"lnum84\" style=\"color: #606060\">  84:<\/span> GO<\/pre>\n<p><!--CRLF--><\/div>\n<\/div>\n<p><strong>Figure 1: Example Database Scoped Configuration Commands in SQL Server 2016<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><style type=\"text\/css\">.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }\n<\/style><\/p>\n","protected":false},"excerpt":{"rendered":"<p>On March 7, 2016, Microsoft announced the availability of the RC0 Build of SQL Server 2016. One of the new features in the RC0 Build is Database Scoped Configurations, which gives you the ability to easily make several database-level configuration changes for things that were previously configured at the instance-level. These include: Setting MAXDOP for [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[246],"tags":[277],"class_list":["post-1068","post","type-post","status-publish","format-standard","hentry","category-sql-server-2016","tag-alter-database-scoped-configuration"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Using ALTER DATABASE SCOPED CONFIGURATION in SQL Server 2016 - Glenn Berry<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using ALTER DATABASE SCOPED CONFIGURATION in SQL Server 2016 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"On March 7, 2016, Microsoft announced the availability of the RC0 Build of SQL Server 2016. One of the new features in the RC0 Build is Database Scoped Configurations, which gives you the ability to easily make several database-level configuration changes for things that were previously configured at the instance-level. These include: Setting MAXDOP for [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-07T22:05:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:57+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/\",\"name\":\"Using ALTER DATABASE SCOPED CONFIGURATION in SQL Server 2016 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-03-07T22:05:58+00:00\",\"dateModified\":\"2017-04-13T19:28:57+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using ALTER DATABASE SCOPED CONFIGURATION in SQL Server 2016\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using ALTER DATABASE SCOPED CONFIGURATION in SQL Server 2016 - Glenn Berry","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/","og_locale":"en_US","og_type":"article","og_title":"Using ALTER DATABASE SCOPED CONFIGURATION in SQL Server 2016 - Glenn Berry","og_description":"On March 7, 2016, Microsoft announced the availability of the RC0 Build of SQL Server 2016. One of the new features in the RC0 Build is Database Scoped Configurations, which gives you the ability to easily make several database-level configuration changes for things that were previously configured at the instance-level. These include: Setting MAXDOP for [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/","og_site_name":"Glenn Berry","article_published_time":"2016-03-07T22:05:58+00:00","article_modified_time":"2017-04-13T19:28:57+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/","name":"Using ALTER DATABASE SCOPED CONFIGURATION in SQL Server 2016 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-03-07T22:05:58+00:00","dateModified":"2017-04-13T19:28:57+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/using-alter-database-scoped-configuration-in-sql-server-2016\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"Using ALTER DATABASE SCOPED CONFIGURATION in SQL Server 2016"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1068","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1068"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1068\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1068"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1068"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1068"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}