--- title: Databases with their own server level settings description: Databases with their own server level settings --- [![](https://www.coeo.com/wp-content/themes/coeo/images/logo.svg)](https://blog.coeo.com/) +44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) | [Client portal login](https://my.coeo.com) # Databases with their own server level settings # The Coeo Blog ![Gavin Payne](https://blog.coeo.com/hubfs/Profile%20photos/GavinCircle.jpg) Being a futurist, I like new features and SQL Server 2016 and Azure SQL Database have one that’s really caught my eye!   ### ALTER DATABASE SCOPED CONFIGURATION This command lets you set what used to be server level configuration settings on a per-database level.  It  also lets you set different values for the primary and secondary replicas in an Availability Group. > ALTER DATABASE SCOPED CONFIGURATION > > MAXDOP = { | PRIMARY} > > LEGACY\_CARDINALITY\_ESTIMATION = { ON | OFF | PRIMARY} > > PARAMETER\_SNIFFING = { ON | OFF | PRIMARY} > > QUERY\_OPTIMIZER\_HOTFIXES = { ON | OFF | PRIMARY} Four settings that used to sp\_configure, database options or trace flag settings can be now be enabled on a per-database basis and set using T-SQL. More information is available here about what each of the settings do, but they should be relatively self-explanatory: [https://msdn.microsoft.com/en-gb/library/mt629158.aspx](https://msdn.microsoft.com/en-gb/library/mt629158.aspx)   ### Different settings for primary and secondary replicas Also with this new command, you can set different configuration values for the primary and secondary replicas in an Availability Groups deployment.  This lets you automatically tune the primary replica for transactional workloads and the secondary replicas for analytics queries. In the example below, you’d set an primary Availability Group replica to have a MaxDOP setting of 4 and its secondary replicas to have MaxDOP values of 32: > ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4 > > ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=32 ### Parallel queries in Azure SQL Database Now that the Premium tier of Azure SQL Database supports parallel queries, the same commands can be used to change the MaxDOP of queries that run there too as it doesn’t support the traditional sp\_configure method.  This is useful for forcing highly concurrent transactional queries to execute with serial (MaxDOP=1) plans.   ### Clearing the plan cache You can also clear the procedure cache of plans that only originated from a specific database: > ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE\_CACHE ### Querying the a database’s configuration state To see the current configuration of a database’s database scope settings, you can use the DMV > sys.database\_scoped\_configurations Hopefully of interest to some! ### Subscribe to Email Updates ## Related posts --- ### [Troubleshooting Ola Hallengren’s Maintenance Solution](https://blog.coeo.com/troubleshooting-ola-hallengrens-maintenance-solution) ### [Microsoft Purview's 14 Key Controls](https://blog.coeo.com/data-governance-with-azure-purview-empowering-your-data-governance-with-microsoft-purview-controls-a-comprehensive-guide) ### [Domain-Independent Windows Failover Cluster for SQL Server AlwaysOn Availability Group](https://blog.coeo.com/domain-independent-windows-failover-cluster-for-sql-server-alwayson-availability-group) ### [Three ways to profile data with Azure Databricks](https://blog.coeo.com/three-ways-to-profile-data-with-azure-databricks) ![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png) +44 (0)20 3051 3595 | info@coeo.com ## Contact Us By clicking submit below, you consent to allow Coeo to store and process the personal information submitted above to provide you the content requested. You may unsubscribe from these communications at any time. For more information on how to unsubscribe and our commitment to your privacy, please review our **[Privacy Policy](https://www.coeo.com/privacy/)**. ## Upcoming Events [See all events](https://www.coeo.com/events/) #### NOW Building, Thames Valley Park Drive, Reading, RG6 1RB [![](https://www.coeo.com/wp-content/themes/coeo/images/social-glass.png)](https://www.glassdoor.co.uk/Overview/Working-at-Coeo-EI_IE959052.11,15.htm)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-in.png)](https://www.linkedin.com/company/coeo-ltd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-twitter.png)](https://twitter.com/CoeoLtd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-fb.png)](https://www.facebook.com/coeoltd/) ![](https://www.coeo.com/wp-content/themes/coeo/images//menu-icon.png) ![](https://www.coeo.com/wp-content/themes/coeo/images//menu-close.png) ![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png) +44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) - [Solutions](https://www.coeo.com/solutions/) - [Next Steps](https://www.coeo.com/next-steps/) - [Dedicated Support](https://www.coeo.com/dedicated-support/) - [Case studies](https://www.coeo.com/case-studies/) - [Technologies](https://www.coeo.com/solutions/technologies/) - [Industries](https://www.coeo.com/industries/) - [Finance](https://www.coeo.com/industries/finance/) - [Retail](https://www.coeo.com/industries/retail/) - [Technology](https://www.coeo.com/industries/technology/) - [The Team](https://www.coeo.com/people/) - [Join Us](https://www.coeo.com/careers/) - [Graduate Programme](https://www.coeo.com/graduate-programme/) - [About Coeo](https://www.coeo.com/about-coeo/) - [The Coeo Blog](https://www.coeo.com/blog/) - [Contact us](https://www.coeo.com/contact-us/) - [Privacy Notice](https://www.coeo.com/privacy/) - [Cookie Policy](https://www.coeo.com/privacy#Cookie_Policy) - [Events](https://www.coeo.com/events) Sign up to our newsletter ![go arrow](https://www.coeo.com/wp-content/themes/coeo/images/newsletter-go.png) Back to top