Azure SQL Database Configuration Options
When dealing with configuration options in Azure SQL Databases, you should be comfortable with multiple settings that influence performance and behaviour of the database. Some key configuration options include:
- Compatibility level: Determines certain database behaviors to be compatible with the specified version of SQL Server.
- Connection settings: Settings relating to the governance of connections and their behaviour.
- Cursor threshold: Specifies the number of rows in the cursor set at which cursor keysets are generated asynchronously.
- Recovery model: Defines how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.
Here’s an example of how to change the compatibility level using T-SQL:
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }
Performance Levels and DTUs
In Azure SQL Databases, the performance level is determined by a measure known as the DTU (Database Transaction Units). DTUs represent a blended measure of CPU, memory, and data I/O and transaction log I/O in a ratio determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads. Knowing how to increase or decrease these measures ensures that you use the right amount of resources for your tasks and avoid overspending.
Here is how you can modify a database’s DTUs using Azure Powershell:
New-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -Edition "Standard" -RequestedServiceObjectiveName "S1" -MaxSizeBytes "134217728"
Section 2: Monitoring Azure SQL Databases
Azure offers a rich set of built-in monitoring solutions that expose performance and health metrics of Azure SQL Databases.
Monitoring with Azure Monitor
Azure Monitor helps you understand how your applications are performing and proactively identifies issues affecting them and the resources they depend on. Azure Monitor collects data from multiple sources into a common data platform where it can be analyzed for trends and anomalies.
Query Performance Insight
Query Performance Insight provides intelligent query analysis for single and pooled databases. It helps identify your most expensive queries, and can provide insights into your database resource (DTU) consumption.
Performance Recommendations with Advisor
SQL Database Advisor is a feature of Azure SQL Databases that provides recommendations for optimizing the performance of databases by suggesting actions such as creating or dropping indexes, parameterizing queries, and fixing schema issues.
In conclusion, understanding how to configure and monitor Azure SQL Databases is a fundamental aspect of managing Azure SQL Solutions. From configuration settings to performance monitoring tools, Azure provides a robust set of options for you to ensure the health and efficiency of your databases. Remember to leverage these features as you prepare for your DP-300 exam and in your everyday database administration tasks.
Practice Test
True or False: Query performance insight in Azure SQL database provides quick insights to the performance of your database.
- True
- False
Answer: True
Explanation: As a tool of Azure SQL, Query performance insight assists in observing and fixing performance issues by gaining insights into your database’s performance.
In Azure SQL, what manages the hard work of both monitoring diagnosing performance issues?
- a) Automatic Tuning
- b) Query performance insight
- c) Performance Overview
- d) Azure Advisor
Answer: a) Automatic Tuning
Explanation: Automatic Tuning in Azure SQL manages the performance monitoring and automatic tuning capabilities of Azure SQL Database and SQL Server based on your workload’s behavior.
True or False: Once a SQL Server instance is deployed in Azure, it cannot be scaled up or down.
- True
- False
Answer: False
Explanation: Azure provides the flexibility to adjust the performance and cost of your SQL Server instance after it is deployed. You can scale up or down your SQL Server resources at any time as required.
Which of the following can be done using the Azure SQL Advisor? (Select all that apply)
- a) Index recommendations
- b) Parameterization recommendations
- c) Using third-party applications
- d) Query optimization suggestions
Answer: a) Index recommendations b) Parameterization recommendations d) Query optimization suggestions
Explanation: The Azure SQL Advisor uses machine learning to analyze your SQL Database workload to detect disruptive events that impact performance and guides through appropriate mitigation steps.
True or False: In SQL Database, you can use dynamic data masking to hide sensitive data in your applications.
- True
- False
Answer: True
Explanation: Dynamic Data Masking is a feature in Azure’s SQL database which prevents unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on application code.
What command is used to view the wait stats in an Azure SQL database?
- a) VIEW WAIT_STATS
- b) SELECT WAIT_STATS
- c) EXECUTE WAITSTATS
- d) DBCC SQLPERF (‘sys.dm_os_wait_stats’)
Answer: d) DBCC SQLPERF (‘sys.dm_os_wait_stats’)
Explanation: The DBCC SQLPERF command is used to view wait statistics in the Azure SQL database.
True or False: Azure SQL Database provides built-in intelligence that learns app patterns and adapts to maximize performance, reliability, and data protection.
- True
- False
Answer: True
Explanation: The built-in intelligence feature of Azure SQL Database automatically tunes performance and improves reliability and data protection.
Which of the below are not features of Azure’s SQL Performance Tuning Advisor?
- a) Automated backups
- b) Instance recommendations
- c) Performance dashboard
- d) Tuning log
Answer: a) Automated backups
Explanation: The SQL Performance Tuning Advisor does not have the function of automated backups. It primarily focuses on performance enhancements.
True or False: Data Exposed is a tool provided by Azure to examine and scrutinize data.
- True
- False
Answer: False
Explanation: Data Exposed is not a tool, but a well-known show on Azure that focuses on data solutions.
In Azure, which feature can help you assess the impact of workload behavior on your server’s performance?
- a) Automatic Tuning
- b) Performance Overview
- c) Azure Advisor
- d) Query Store
Answer: d) Query Store
Explanation: Query Store in Azure captures a history of queries, plans, and runtime statistics, enabling you to analyze your workload by inspecting the plan choice and performance, as well as track changes in query performance and easily identify queries that need to be tuned.
Interview Questions
What is Azure SQL Database Intelligent Performance?
Azure SQL Database Intelligent Performance is a suite of features that help you identify, troubleshoot, and resolve performance issues within your Azure SQL Database or Azure Synapse Analytics.
What is the primary tool used for monitoring performance of Azure SQL database?
The primary tool used to monitor the performance of an Azure SQL database is Azure SQL Analytics.
How can we configure automated tuning in Azure SQL Database?
Automated tuning can be configured through the Azure portal. It can be enforced on a single Azure SQL Database, a database server, or an elastic pool of databases.
What is Query Store in Azure SQL Database configuration?
Query Store is a feature that keeps track of query execution plans, performance, and usage statistics. It’s used to detect performance issues caused by query plan changes.
Can we monitor the CPU usage of an Azure SQL Database?
Yes, CPU usage can be monitored using the Azure portal. The CPU percentage metric indicates the total processor time across all databases within a server or elastic pool.
Can we get real-time performance metrics in Azure SQL Database?
Yes, Azure SQL Database provides real-time performance metrics and historical analytical reports using Azure Monitor.
How can we monitor database growth and storage in Azure SQL Database?
The Storage metric in Azure SQL Database helps monitor the disk space usage, and you can also use Query Performance Insight for a detailed view of database growth and storage.
What is the purpose of Database Transaction Units (DTUs) in Azure SQL Database?
Database Transaction Units (DTUs) represent the compute, I/O, and storage resources that are guaranteed to a single Azure SQL database in elastic pool.
How is the Query Performance Insight tool in Azure SQL Database used?
Query Performance Insight is used to visualize query performance data and identify disruptive queries. It provides details on top database consuming queries, query history, and activities that consume excessive resources.
Can we scale up or scale down Azure SQL Database?
Yes, Azure SQL Database is highly scalable and you can perform scale up or scale down operations based on your workload capacity.
What is Azure SQL Database Advisor?
Azure SQL Database Advisor provides personalized recommendations based on the usage patterns of your database. It covers indexes, query performance, and other aspects.
What are deadlocks in database performance?
Deadlocks occur when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.
Can Azure SQL Database handle failovers automatically?
Yes, Azure SQL Database has built-in support for automatic failovers. In most cases, the failover process is transparent to the applications.
How is Azure SQL Database backup handled?
Azure SQL Database includes automated backups with point-in-time-restore (PITR) capabilities. Recovery is possible to any point within the retention period.
What is Azure Monitor used for in context of Azure SQL Database?
Azure Monitor is used to get insights into how your database is performing and detect any potential issues. It provides performance metrics, activity logs, and diagnostic logs.
extutorials.com