{"id":13927,"date":"2019-11-20T11:45:28","date_gmt":"2019-11-20T11:45:28","guid":{"rendered":"https:\/\/dbtut.com\/?p=13927"},"modified":"2019-11-20T11:46:54","modified_gmt":"2019-11-20T11:46:54","slug":"database-scoped-configurations-in-sql-server-2016-and-sql-server-2017","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/","title":{"rendered":"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017"},"content":{"rendered":"

We are able to configure some critical options set at instance level, such as MAXDOP, at the database level with Database Scoped Configurations<\/strong> feature of SQL Server 2016. Especially on consolidated systems, you may need to configure the databases differently. In this sense, I think this is a very nice development.<\/p>\n

At the database level we are able to configure the following options. You can access Database Scoped Configurations by right-clicking on the database, clicking properties, and then clicking on the Options tab.<\/p>\n

\"\"<\/p>\n

Features Supported by Database Scoped Configurations<\/h1>\n

Legacy Cardinality Estimation<\/h2>\n

In SQL Server 2014, Cardinality Estimator has been redesigned. New version of Cardinality Estimator allows better query plan generation by predicting how many rows the query will return. In order for queries to use the new cardinality estimator, the Compatibility Level must be 120 or higher.<\/p>\n

A better query plan is produced with new cardinality estimator for 98% of the queries. But for a 2% slice, Legacy CE has the opposite effect. In such cases, you can downgrade the compatibility level to 110. But you’ll be deprived of the many improvements that the new compatibility level provide.<\/p>\n

Or, instead of downgrade the compatibility level, you can use the old cardinality estimator for the database with the help of the following script. The default value is OFF, and if the compatibility level is 120 or higher, it uses the new cardinality estimator.<\/p>\n

Enable Legacy Cardinality Estimation<\/h3>\n
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON;<\/pre>\n
You may want to use the old cardinality estimator based on the query. To do this, you can use the query hint that can usable with SQL Server 2016 SP1 as follows. You must append this query hint to the end of the query.<\/p>\n
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));<\/pre>\n
If you are using Always ON Availability Group, you can configure your secondary database to use the same cardinality estimator as the primary database with the following query.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=PRIMARY ;<\/pre>\n
You can also set a different setting for the secondary database than the primary database.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=OFF;<\/pre>\n
MAX DOP<\/h2>\n
We could previously set the maxdop value of the queries at the instance or query level. We can now set specific MAXDOP values \u200b\u200bfor specific databases. To understand MAXDOP, you can read “Numa Nodes, MAX \/ MIN Server Memory, Lock Pages In Memory, MAXDOP<\/a>“.<\/p>\n
The configuration at the database level overrides the configuration at the instance level. If a session level maxdop is set, it will override the database level.<\/p>\n
We can set maxdop at database level with the help of the following query.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP =1 ;<\/pre>\n
If you are using Always On Availability Group, you can set a different maxdop setting for your seconday database as follows.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=4 ;<\/pre>\n
Also, you can configure your secondary database to use the same max dop setting as the primary database with the following query.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=PRIMARY;<\/pre>\n
Parameter Sniffing<\/h1>\n
To understand what this database scoped configuration is, we must first understand what parameter sniffing is. You can find a detailed content in the article “What is Parameter Sniffing”.<\/p>\n
You can use the following script to solve the parameter sniffing problem for the database. When you execute this script, all queries act as if “OPTIMIZE FOR UNKNOWN” has been added at the end of each query in the database.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF;<\/pre>\n
If you use always on availability group, you can prevent the parameter sniffing problem in the secondary database with the help of the below script.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING=OFF;<\/pre>\n
Also, you can configure your secondary database to use the parameter sniffing setting as the primary database with the following query.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING=PRIMARY;<\/pre>\n
Query Optimizer Fixes<\/h1>\n
By enabling this configuration, we set it up to take advantage of the latest hotfixes related to Query Optimizer, regardless of the compatibility level of the database.<\/p>\n
We can enable it as follows.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON;\n<\/pre>\n
You can configure your secondary database to use the same setting as the primary database with the following query.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES =PRIMARY;<\/pre>\n
Clear Procedure Cache<\/h1>\n
You can use this database scoped configuration to clear the procedure cache in the database. You can perform this operation with the following script.<\/p>\n
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;\n<\/pre>\n
Identitiy Cache<\/h1>\n
This is database scoped configuration announced with SQL Server 2017.<\/p>\n
It would be better to explain this configuration through a scenario.<\/p>\n
Suppose that you have an auto increment identity column in a table;<\/p>\n
If the service closes unexpectedly before commit while inserting data into this table, or if the failover occurs, there will be gaps on the auto increment identity values.<\/p>\n
For example, you have inserted 5 records. ID values \u200b\u200b1,2,3,4,5. Subsequent inserts should continue as 6,7,8. But it continues from 1003,1004 after the service closes unexpectedly. We can solve this problem by disabling Identity cache with the following script.<\/p>\n
Disable Identitiy Cache<\/h3>\n
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;<\/pre>\n
Check Current Database Scoped Configurations<\/h1>\n
select * from sys.database_scoped_configurations<\/pre>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
We are able to configure some critical options set at instance level, such as MAXDOP, at the database level with Database Scoped Configurations feature of SQL Server 2016. Especially on consolidated systems, you may need to configure the databases differently. In this sense, I think this is a very nice development. At the database level …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":13935,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[6003,5997,5995,5993,5996,5998,3203,6028,6029,6018,6019,6020,6021,6022,6006,6005,6004,6008,6009,6007,6010,6002,6016,6017,5991,6000,5999,6023,6025,6024,6027,6011,6012,6013,6014,6015,5994,6001,6026,5992],"class_list":["post-13927","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-alter-database-scoped-configuration","tag-alter-database-scoped-configuration-for-secondary-set-maxdop-primary","tag-alter-database-scoped-configuration-set-maxdop","tag-alter-database-scoped-configuration-set-maxdop-0","tag-alter-database-scoped-configuration-set-maxdop-1","tag-alter-database-set-maxdop","tag-always-encrypted-in-sql-server-2016-step-by-step","tag-check-current-database-scoped-configurations","tag-check-database-scoped-configurations","tag-clear-procedure-cache","tag-clear-procedure-cache-database","tag-clear-procedure-cache-database-scoped-configuration","tag-clear-procedure-cache-in-sql-server","tag-clear-procedure-cache-sql-server","tag-database-options-parameter-sniffing","tag-database-parameter-sniffing","tag-database-scoped-configuration","tag-database-scoped-configuration-legacy-cardinality-estimation","tag-database-scoped-configuration-maxdop","tag-database-scoped-configuration-parameter-sniffing","tag-database-scoped-configuration-query-optimizer-fixes","tag-database-scoped-configurations","tag-database-scoped-configution-sql-server-2016","tag-database-scoped-configution-sql-server-2017","tag-enable-legacy-cardinality-estimation","tag-how-do-you-set-maxdop-database-based","tag-how-do-you-set-maxdop","tag-identitiy-cache","tag-identitiy-cache-database-scoped-configuration","tag-identitiy-cache-in-sql-server","tag-identity-cache-sql-server-2016","tag-query-optimizer-fixes","tag-query-optimizer-fixes-setting","tag-query-optimizer-fixes-sql-server","tag-query-optimizer-fixes-sql-server-2016","tag-query-optimizer-fixes-sql-server-2017","tag-set-maxdop-for-database","tag-sql-server-2016-database-scoped-configurations","tag-sql-server-2016-identity-cache","tag-sql-server-enable-legacy-cardinality-estimation"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":1031,"today_views":0},"yoast_head":"\nDatabase Scoped Configurations in SQL Server 2016 and SQL Server 2017 - Database Tutorials<\/title>\n<meta name=\"description\" content=\"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017 - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-11-20T11:45:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-11-20T11:46:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-61.png\" \/>\n\t<meta property=\"og:image:width\" content=\"528\" \/>\n\t<meta property=\"og:image:height\" content=\"301\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017\",\"datePublished\":\"2019-11-20T11:45:28+00:00\",\"dateModified\":\"2019-11-20T11:46:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/\"},\"wordCount\":760,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-61.png\",\"keywords\":[\"ALTER DATABASE SCOPED CONFIGURATION\",\"alter database scoped configuration for secondary set maxdop = primary\",\"alter database scoped configuration set maxdop\",\"alter database scoped configuration set maxdop = 0\",\"alter database scoped configuration set maxdop = 1\",\"alter database set maxdop\",\"always encrypted in sql server 2016 step by step\",\"Check Current Database Scoped Configurations\",\"Check Database Scoped Configurations\",\"Clear Procedure Cache\",\"Clear Procedure Cache database\",\"Clear Procedure Cache database scoped configuration\",\"Clear Procedure Cache in sql server\",\"Clear Procedure Cache sql server\",\"database options parameter sniffing\",\"database parameter sniffing\",\"DATABASE SCOPED CONFIGURATION\",\"database scoped configuration Legacy Cardinality Estimation\",\"database scoped configuration maxdop\",\"database scoped configuration parameter sniffing\",\"database scoped configuration Query Optimizer Fixes\",\"Database Scoped Configurations\",\"database scoped configution sql server 2016\",\"database scoped configution sql server 2017\",\"enable legacy cardinality estimation\",\"How do you set Maxdop database based\",\"How do you set Maxdop?\",\"Identitiy Cache\",\"Identitiy Cache database scoped configuration\",\"Identitiy Cache in sql server\",\"identity cache sql server 2016\",\"Query Optimizer Fixes\",\"Query Optimizer Fixes setting\",\"Query Optimizer Fixes sql server\",\"Query Optimizer Fixes sql server 2016\",\"Query Optimizer Fixes sql server 2017\",\"set maxdop for database\",\"SQL Server 2016 Database Scoped Configurations\",\"sql server 2016 identity cache\",\"sql server enable legacy cardinality estimation\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/\",\"name\":\"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017 - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-61.png\",\"datePublished\":\"2019-11-20T11:45:28+00:00\",\"dateModified\":\"2019-11-20T11:46:54+00:00\",\"description\":\"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-61.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-61.png\",\"width\":528,\"height\":301},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017 - Database Tutorials","description":"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017","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:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/","og_locale":"en_US","og_type":"article","og_title":"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017 - Database Tutorials","og_description":"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017","og_url":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/","og_site_name":"Database Tutorials","article_published_time":"2019-11-20T11:45:28+00:00","article_modified_time":"2019-11-20T11:46:54+00:00","og_image":[{"width":528,"height":301,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-61.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017","datePublished":"2019-11-20T11:45:28+00:00","dateModified":"2019-11-20T11:46:54+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/"},"wordCount":760,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-61.png","keywords":["ALTER DATABASE SCOPED CONFIGURATION","alter database scoped configuration for secondary set maxdop = primary","alter database scoped configuration set maxdop","alter database scoped configuration set maxdop = 0","alter database scoped configuration set maxdop = 1","alter database set maxdop","always encrypted in sql server 2016 step by step","Check Current Database Scoped Configurations","Check Database Scoped Configurations","Clear Procedure Cache","Clear Procedure Cache database","Clear Procedure Cache database scoped configuration","Clear Procedure Cache in sql server","Clear Procedure Cache sql server","database options parameter sniffing","database parameter sniffing","DATABASE SCOPED CONFIGURATION","database scoped configuration Legacy Cardinality Estimation","database scoped configuration maxdop","database scoped configuration parameter sniffing","database scoped configuration Query Optimizer Fixes","Database Scoped Configurations","database scoped configution sql server 2016","database scoped configution sql server 2017","enable legacy cardinality estimation","How do you set Maxdop database based","How do you set Maxdop?","Identitiy Cache","Identitiy Cache database scoped configuration","Identitiy Cache in sql server","identity cache sql server 2016","Query Optimizer Fixes","Query Optimizer Fixes setting","Query Optimizer Fixes sql server","Query Optimizer Fixes sql server 2016","Query Optimizer Fixes sql server 2017","set maxdop for database","SQL Server 2016 Database Scoped Configurations","sql server 2016 identity cache","sql server enable legacy cardinality estimation"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/","url":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/","name":"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017 - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-61.png","datePublished":"2019-11-20T11:45:28+00:00","dateModified":"2019-11-20T11:46:54+00:00","description":"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-61.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-61.png","width":528,"height":301},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/20\/database-scoped-configurations-in-sql-server-2016-and-sql-server-2017\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Database Scoped Configurations in SQL Server 2016 and SQL Server 2017"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/13927","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=13927"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/13927\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/13935"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=13927"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=13927"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=13927"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}