{"id":7416,"date":"2016-03-18T17:59:58","date_gmt":"2016-03-18T16:59:58","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/"},"modified":"2016-03-18T17:59:58","modified_gmt":"2016-03-18T16:59:58","slug":"sql-server-2016-new-database-scoped-configuration-parameters-part-2","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/","title":{"rendered":"SQL Server 2016: new database-scoped configuration parameters part 2"},"content":{"rendered":"<p><span style=\"color: #000000;font-family: Calibri\">In this blog post, let\u2019s continue with other database-scoped options shipped with the SQL Server 2016 RC0.<\/span><\/p>\n<p><span style=\"color: #000000;font-family: Calibri\">We will first begin with the first one and my preferred option: setting the MAXDOP value for an individual database. I really hope that applications like SharePoint that requires to limit the MAXDOP to 1 at the server level will use this kind of options in the future. As a reminder, changing this configuration parameter at the server level may have a huge impact on other applications and in most cases we end up dedicating the entire instance for the concerned application. <\/span><\/p>\n<p><span style=\"color: #000000;font-family: Calibri\">First, let\u2019s take a look at the MAXDOP setting at the server level on my lab environment:<\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT \n\tname as configuration_setting,\n\tvalue_in_use\nFROM sys.configurations\nWHERE name IN ('cost threshold for parallelism', 'max degree of parallelism');\nGO<\/pre>\n<p><span style=\"color: #000000;font-family: Calibri\"><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-1-maxdop-server-level-setting.jpg\">\u00a0<\/a><\/span><\/p>\n<p><span style=\"color: #000000;font-family: Calibri\"><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-1-maxdop-server-level-setting.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-7647\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-1-maxdop-server-level-setting.jpg\" alt=\"blog 85 - 1 - maxdop server level setting\" width=\"323\" height=\"73\" \/><\/a><\/span><\/p>\n<p>So, basically any query with a cost greater than 5 may be candidate to parallel execution plans with a maximum of 4 processors in my case.<\/p>\n<p>Let\u2019s first disable parallelism only for the DB_2016 database by using this command syntax:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">USE DB_2016;\nGO\n\nALTER DATABASE SCOPED CONFIGURATION \nSET MAXDOP = 1;\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p>Now let\u2019s verify the state of this parameter by using the new <em>sys.database_scoped_configurations <\/em>DMV:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT \n\tconfiguration_id, \n\tname, \n\t[value] AS [value_for_primary], \n\tvalue_for_secondary\nFROM sys.database_scoped_configurations \nOPTION (RECOMPILE);<\/pre>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-2-maxdop-configuration-for-one-db.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-7648\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-2-maxdop-configuration-for-one-db.jpg\" alt=\"blog 85 - 2 - maxdop configuration for one db\" width=\"681\" height=\"121\" \/><\/a><\/p>\n<p><span style=\"color: #000000;font-family: Calibri\">You have probably noticed the last two columns. According to the Microsoft documentation <\/span><span style=\"color: #0563c1;font-family: Calibri\">here<\/span><span style=\"color: #000000;font-family: Calibri\">, it means that we may enable or disable database-scoped configuration settings either for AlwaysOn primary or secondary replicas. Well, very interesting \u2026 and I will probably cover this specific topic in a future blog post but at this moment let\u2019s focus only on the database-scoped capabilities. <\/span><\/p>\n<p><span style=\"color: #000000;font-family: Calibri\">So it\u2019s time to execute the following testing query: <\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT COUNT(*)\nFROM dbo.test_2016<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #000000;font-family: Calibri\">And here the related execution plan generated by SQL Server:<\/span><\/p>\n<p><span style=\"color: #000000;font-family: Calibri\"><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-3-disabling-maxdop-db-test.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-7649\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-3-disabling-maxdop-db-test.jpg\" alt=\"blog 85 - 3 - disabling maxdop db test\" width=\"942\" height=\"270\" \/><\/a><\/span><\/p>\n<p>This is a serial plan as you may see above. However, according to the estimated subtree Cost, we would expect to a parallel execution plan but if we take a look at the <em>NonParallelPlanReason<\/em> property we may notice clearly why no parallel plan has been used in this case. Now, let\u2019s imagine a scenario where with one hand you want to disable parallelism on a AlwaysOn primary replica and on other hand you want to benefit to parallelism for reporting queries on the AlwaysOn readable secondary \u2026 The response in a next blog post J<\/p>\n<p>So let\u2019s continue and let\u2019s first go back to the previous output result from the new <em>sys.database_scoped_configurations<\/em> DMV. You may see other interesting parameters as PARAMETER_SNIFFING or LEGACY_CARDINALITY_ESTIMATION. I remembered a performance audit at one of my customers where the trace flag 4136 was enabled on the audited SQL Server instance. Enabling this trace flag may be a good approach to solve parameter sniffing issues in some circumstances but the main drawback is the scope that may be too large and may impact other databases. The new database-scoped PARAMETER_SNIFFING parameter will probably address this kind of scenario.<\/p>\n<p>Furthermore, enabling the LEGACY_CARDINALITY_ESTIMATION database-scoped parameter is equivalent to enabling the trace flag 9481 either at the server instance level, at the session level or finally at the query level by using the QUERYTRACEON hint. Another way to disable new cardinality estimator was to switch to compatibility level 120 or earlier to force SQL Server using cardinality estimator version 70. Many ways that may be replaced by only one at the database level.<\/p>\n<p>Then, we have now the possibility to enable query optimization hotfixes for an individual database that is equivalent to use the trace flag 4199 at the server level. You may refer to this Microsoft KB (<a href=\"https:\/\/support.microsoft.com\/en-us\/kb\/2801413\">2801413<\/a>) to get a complete list of trace flags implicitly enabled when enabling the global trace flag 4199.<\/p>\n<p>Let\u2019s finish with the CLEAR PROCEDURE_CACHE database-scoped parameter. The good news is that we are now able to clear the procedure cache for a specific database and eventually on AlwaysOn environments either on the primary or the secondaries.<\/p>\n<p>Let\u2019s perform a quick test \u2026 but before let\u2019s have a look at the number of entries for each concerned database from the <em>sys.dm_exec_cached_plans <\/em>DMV:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">USE master;\nGO\n\nSELECT \n\td.name AS [db_name],\n\tcount(*) as nb_cached_entries\nFROM sys.dm_exec_cached_plans AS cp\nCROSS APPLY (\n\tSELECT CAST(pa.value AS INT) AS database_id\n\tFROM sys.dm_exec_plan_attributes(cp.plan_handle) AS pa\n\tWHERE pa.attribute = N'dbid'\n) AS DB\nINNER JOIN sys.databases AS d\n\tON d.database_id = DB.database_id\nGROUP BY d.name\nORDER BY d.name<\/pre>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-4-clear-procedure-cache-for-a-db.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-7651\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-4-clear-procedure-cache-for-a-db.jpg\" alt=\"blog 85 - 4 - clear procedure cache for a db\" width=\"270\" height=\"170\" \/><\/a><\/p>\n<p><span style=\"color: #000000;font-family: Calibri\">Now, let\u2019s try to clear the procedure cache for the DB_2016 database by using the new following syntax:<\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">USE DB_2016;\nGO\n\nALTER DATABASE SCOPED CONFIGURATION \nCLEAR PROCEDURE_CACHE;\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p>And finally let\u2019s check if all cached entries related to the DB_2016 database have been cleared correctly by executing the previous query based on the <em>sys.dm_exec_cached_plans<\/em> DMV:<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-5-clear-procedure-cache-for-a-db-check.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-7650\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-5-clear-procedure-cache-for-a-db-check.jpg\" alt=\"blog 85 - 5 - clear procedure cache for a db check\" width=\"276\" height=\"147\" \/><\/a><\/p>\n<p>Nice! Finally, we have a real command to clear the procedure cache at the database level!<\/p>\n<p>From my point of view, this set of new database-scoped parameters are really welcome because the first advantage is that we get a better and more granular control or their scopes. The second advantage is that we have now a \u201cstandard way\u201d to enable or disable these parameters compared to use trace flags at different levels.<\/p>\n<p>So the next time I will write a blog post about using these parameters on SQL Server availability groups, so stay tuned!<\/p>\n<p>By David Barbarin<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog post, let\u2019s continue with other database-scoped options shipped with the SQL Server 2016 RC0. We will first begin with the first one and my preferred option: setting the MAXDOP value for an individual database. I really hope that applications like SharePoint that requires to limit the MAXDOP to 1 at the server [&hellip;]<\/p>\n","protected":false},"author":26,"featured_media":7422,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[783,784,785,741,67,786,650,566,787],"type_dbi":[],"class_list":["post-7416","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-cardinality-estimator","tag-database-scoped-parameter","tag-maxdop","tag-parameter-sniffing","tag-performance","tag-procedure-cache","tag-query-optimizer","tag-sql-server-2016","tag-traceflag"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server 2016: new database-scoped configuration parameters part 2<\/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.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2016: new database-scoped configuration parameters part 2\" \/>\n<meta property=\"og:description\" content=\"In this blog post, let\u2019s continue with other database-scoped options shipped with the SQL Server 2016 RC0. We will first begin with the first one and my preferred option: setting the MAXDOP value for an individual database. I really hope that applications like SharePoint that requires to limit the MAXDOP to 1 at the server [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-18T16:59:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-1-maxdop-server-level-setting-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"323\" \/>\n\t<meta property=\"og:image:height\" content=\"73\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"SQL Server 2016: new database-scoped configuration parameters part 2\",\"datePublished\":\"2016-03-18T16:59:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/\"},\"wordCount\":813,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog-85-1-maxdop-server-level-setting-1.jpg\",\"keywords\":[\"cardinality estimator\",\"database scoped parameter\",\"maxdop\",\"parameter sniffing\",\"Performance\",\"procedure cache\",\"query optimizer\",\"SQL Server 2016\",\"traceflag\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/\",\"name\":\"SQL Server 2016: new database-scoped configuration parameters part 2\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog-85-1-maxdop-server-level-setting-1.jpg\",\"datePublished\":\"2016-03-18T16:59:58+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog-85-1-maxdop-server-level-setting-1.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/blog-85-1-maxdop-server-level-setting-1.jpg\",\"width\":323,\"height\":73},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2016: new database-scoped configuration parameters part 2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/microsoft-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2016: new database-scoped configuration parameters part 2","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.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2016: new database-scoped configuration parameters part 2","og_description":"In this blog post, let\u2019s continue with other database-scoped options shipped with the SQL Server 2016 RC0. We will first begin with the first one and my preferred option: setting the MAXDOP value for an individual database. I really hope that applications like SharePoint that requires to limit the MAXDOP to 1 at the server [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/","og_site_name":"dbi Blog","article_published_time":"2016-03-18T16:59:58+00:00","og_image":[{"width":323,"height":73,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-1-maxdop-server-level-setting-1.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"SQL Server 2016: new database-scoped configuration parameters part 2","datePublished":"2016-03-18T16:59:58+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/"},"wordCount":813,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-1-maxdop-server-level-setting-1.jpg","keywords":["cardinality estimator","database scoped parameter","maxdop","parameter sniffing","Performance","procedure cache","query optimizer","SQL Server 2016","traceflag"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/","name":"SQL Server 2016: new database-scoped configuration parameters part 2","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-1-maxdop-server-level-setting-1.jpg","datePublished":"2016-03-18T16:59:58+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-1-maxdop-server-level-setting-1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-85-1-maxdop-server-level-setting-1.jpg","width":323,"height":73},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-database-scoped-configuration-parameters-part-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2016: new database-scoped configuration parameters part 2"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7416","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7416"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7416\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/7422"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7416"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7416"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7416"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7416"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}