From the course: Microsoft Azure Database Administrator Associate (DP-300) Cert Prep by Microsoft Press

Implement database-scoped configuration

From the course: Microsoft Azure Database Administrator Associate (DP-300) Cert Prep by Microsoft Press

Start my 1-month free trial Buy for my team

Implement database-scoped configuration

“

- Welcome back, this is lesson 9.7, implement database-scoped configuration. Database-scoped configuration items are configuration settings that are configured and enabled at the individual database level, and where appropriate override any associated server level configuration. So these are different than database configuration items. Database configuration items are things like setting automatic tuning, enabling, create and auto update statistics, query store, things like that. Database-scoped configuration items or items that affect the behavior at the database level and include some of the following, max degree of parallelism, so setting this at the database-scoped level overrides the global or server level setting. Query hints can still override the database-scoped max degree of parallelism configuration. Optimize for ad hoc workloads. This enables or disables a compiled plan stub to be stored in cache when a batch is compiled for the first time. Legacy cardinality, this enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and the earlier version independent of the compatibility level at the database. This is off by default and should be. Parameter sniffing. This is an important one. This setting enables or disables parameter sniffing, and it is on by default. Normally parameter sniffing is helpful for query optimization, but it is workload dependent, so enabled test properly. Last query plan stat. This allows you to enable or disable collection of the last query plan statistics, which is equivalent to an actual query plan. The default for this is off. Now, there are a slew of other database-scoped configuration options, and the ones that are on by default should be on, and the ones that are off by default should remain off unless there's a good reason to turn them on. The key to understanding database-scoped configuration is what I mentioned earlier, these are items that affect the behavior at the database level and override any associated server level configuration. So when you're looking at database-scoped configuration items understand what they do and how they override these server level configuration setting.

Contents