Understanding database-scoped configurations in Microsoft Azure SQL is a critical part of preparing for the DP-300 exam. This article will introduce you to this concept and illustrate how you can efficiently implement database-scoped configuration settings in your database. It will also help you build your competence as you prepare for the DP-300: Administering Microsoft Azure SQL Solutions certification exam.
Azure SQL Database uses a mechanism known as the database-scoped configuration to allow specific database configurations without affecting the entire SQL Server instance. This method is useful for the optimization of single, individual databases, without impacting the performance of other databases that may be running on the same SQL Server machine.
Let’s take a closer look at how to work with database-scoped configurations.
Implementing Database-Scoped Configuration
To implement database-scoped configurations in Azure SQL Database, the T-SQL ALTER DATABASE SCOPED CONFIGURATION statement is used. This database level command allows one to set specific configurations applicable only to individual databases.
Here is an example of how you could use the ALTER DATABASE SCOPED CONFIGURATION statement:
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_NAME = VALUE;
In this statement, PARAMETER_NAME is the name of the property you want to change, and VALUE is the new value that you want to set for that property.
Let’s use a real-world example:
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
In this example, the MAXDOP parameter represents the maximum degree of parallelism. Setting this value to 4 means that SQL Server will use up to 4 processors for parallel plan execution.
This provides granular control over how Azure SQL Database behaves, offering the flexibility to differentiate configurations between databases located on the same SQL Server instance.
Database-Scoped Configuration Options
There are several database configuration options that can be set at the database level in Azure SQL. Just to name a few, these include:
- MAXDOP: Determines the maximum degree of parallelism SQL Server could use.
- LEGACY_CARDINALITY_ESTIMATION: Uses the old Cardinality Estimation model.
- PARAMETER_SNIFFING: Enables or disables parameter sniffing.
- QUERY_OPTIMIZER_HOTFIXES: Applies or not the latest query optimizer hotfixes.
Verification of these settings is done using the following SQL command:
SELECT * FROM sys.database_scoped_configurations;
Benefits of Database-Scoped Configurations
Adopting database-scoped configurations presents quite a few advantages, including:
- Greater customization: Each database can have its own unique settings.
- Better performance: By customizing database parameters, you can optimize individual database performances without negatively affecting other databases’ performances.
- Enhanced security: Some configurations, like CLEAR procedure cache can be isolated to a particular database, preventing cross-database security vulnerabilities.
In conclusion, understanding, and effectively implementing database-scoped configurations are critical components of the DP-300 exam. As a potential Azure SQL administrator, this allows you to optimize the performance and security of your individual databases hosted in Azure without risking impact to other databases on the same SQL Server instance.
Practice Test
True or False: Database-scoped configurations allow for settings that apply only to a single database.
– True
– False
Answer: True
Explanation: Database-scoped configurations are settings specified at the database level, affecting only that specific database.
Which of the following settings are considered Database-scoped configurations in Microsoft Azure SQL Database? (Select all that apply)
– A. Max DOP
– B. Legacy Cardinality Estimation
– C. Parameter Sniffing
– D. Network Protocol
Answer: A, B, C
Explanation: Max DOP, Legacy Cardinality Estimation, and Parameter Sniffing are among the settings that can be configured at the database level, while Network Protocol is network-level setting.
True or False: You can alter database-scoped configurations only through Azure portal.
– True
– False
Answer: False
Explanation: You can change these configurations through Azure portal, Azure CLI, PowerShell and SQL Server Management Studio (SSMS).
MAXDOP option in database-scoped configuration refers to:
– A. Maximum Degree of Parallelism
– B. Maximum Degree of Prevention
– C. Maximum Degree of Partition
– D. Maximum Degree of Performance
Answer: A. Maximum Degree of Parallelism
Explanation: MAXDOP refers to Maximum Degree of Parallelism. It controls the number of processors used for the execution of a single SQL statement.
True or False: Database-scoped configurations in Azure SQL Database do not require membership in the db_owner role.
– True
– False
Answer: False
Explanation: Modifying database-scoped configurations requires membership in the db_owner role.
Which of the following query can be used to view the current database-scoped configurations?
– A. SELECT * FROM sys.databases;
– B. SELECT * FROM sys.database_scoped_configurations;
– C. SELECT * FROM sys.configuration;
– D. None of the above
Answer: B. SELECT * FROM sys.database_scoped_configurations;
Explanation: sys.database_scoped_configurations view in SQL Server can be used to view database-scoped configurations.
True or False: Legacy cardinality estimation can be configured at both the server-level and the database-level.
– True
– False
Answer: True
Explanation: While this setting can typically be done at the server-level, with database-scoped configurations in Azure, it can also be done at the database level.
Clear procedure cache option in database-scoped configuration:
– A. Removes all elements from the plan cache for a database
– B. Clears the procedure logs
– C. Removes all stored procedures from a database
– D. None of the above
Answer: A. Removes all elements from the plan cache for a database
Explanation: This option will clear the cache of all plans for that specific database.
Statement-level recompilation policy for natively compiled stored procedures is set with what option in database-scoped configuration?
– A. NATIVE_COMPILATION
– B. RECOMPILE
– C. NATIVE_PROCS
– D. None of the above
Answer: A. NATIVE_COMPILATION
Explanation: To set the recompilation policy for natively compiled stored procedures, one must use the NATIVE_COMPILATION option.
True or False: The database-scoped configuration for MAXDOP is overridden by the server level setting.
– True
– False
Answer: False
Explanation: The database-level setting will take precedence over the server level setting if both are configured.
Interview Questions
What is a database-scoped configuration in the context of Microsoft Azure SQL Solutions?
A database-scoped configuration is a set of options that enable you to customize the behavior of a database. It is a feature that allows you to perform database-level configuration for single databases or pooled databases in Azure SQL Database.
Give an example of a setting that can be modified using the database-scoped configuration.
MAXDOP or Maximum Degree of Parallelism is an example of a setting that can be adjusted using database-scoped configurations. It specifies the maximum number of processors to use in a parallel plan execution.
How can you implement database-scoped configuration using T-SQL?
You can implement a database-scoped configuration using T-SQL with the ALTER DATABASE SCOPED CONFIGURATION command, followed by the setting you want to adjust, and the value to be specified.
What command would you use to set the MAXDOP setting to 4 for a database using T-SQL?
The command would be:
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;How can you view current database-scoped configurations?
You can use the SELECT FROM sys.database_scoped_configurations system view to check the current configurations.
Can the database-scoped configurations of Azure SQL Database and SQL server be configured independently?
Yes, database-scoped configurations of Azure SQL Database and SQL server can be configured independently, allowing different settings for each.
Which command can you use to turn on the QUERY_OPTIMIZER_HOTFIXES?
The command would be:
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;What does the CLEAR PROCEDURE_CACHE command do in the database-scoped configuration settings?
The CLEAR PROCEDURE_CACHE command removes all elements from the plan cache for the database, ensuring that all subsequent executions of queries result in a new plan being compiled.
Can the user modify the database-scoped configuration settings of an Azure SQL database?
Yes, a user with sufficient permissions, such as the db_owner or db_ddladmin fixed database roles, can modify the database-scoped configuration settings of an Azure SQL database.
Which query option enables the use of a legacy cardinality estimation model?
The LEGACY_CARDINALITY_ESTIMATION = ON option enables the use of the legacy cardinality estimation model.
What is the purpose of the MAXDOP parameter in a database-scoped configuration?
The MAXDOP parameter controls the maximum number of processors or threads can be used for a single query. This helps to manage system resources and queries performance by parallelizing data processing.
How can PARAMETER_SNIFFING be turned off in a database-scoped configuration?
The PARAMETER_SNIFFING can be turned off with the command:
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;What is the effect of turning off the automated tuning FORCE_LAST_GOOD_PLAN option in the scoped configuration?
Turning off the automated tuning FORCE_LAST_GOOD_PLAN option means that Azure SQL Database stops forcing the last good plan when a regression is detected, allowing the query optimizer to select the most optimal plan currently.
What is the significance of the SET QUERY_STORE = ON setting?
SET QUERY_STORE = ON enables the Query Store feature for the database, which collects detailed performance information about the queries being run against the database.
Can you change the MAXDOP setting on the master database?
No, the MAXDOP setting, which sets the maximum degree of parallelism, cannot be changed on the master database or on any of the system databases. It applies only to user databases.
extutorials.com