Determine sources for performance metrics

It’s crucial to monitor and optimize the performance of your Azure SQL database to ensure smooth operation and performance. One of the critical parts of optimizing performance involves identifying relevant metric sources. The primary sources of performance metrics in Azure SQL include Azure Monitor, Dynamic Management Views (DMVs), and Query Store.

1. Azure Monitor:

Azure Monitor is a service in Azure that provides comprehensive solutions for collecting, analyzing, and acting on telemetry data from your cloud and on-premises environments. It helps you understand how applications are performing and proactively identifies issues affecting them, with capabilities such as:

• Metric Explorer: Metrics represent the system’s numeric values at a specific point in time. Metric Explorer will assist you in identifying these performance metrics.
  Here’s an example to use Azure Monitor to get metrics:

Azure CLI
az monitor metrics list –resource –resource-type –provider-namespace –metric-names –time-grain

In the example above, replace –resource with your Azure resource and –metric-names with metrics you want to explore.

• Logs: Azure SQL Database emits various logs which contain different types of data and are a resource of great detail. Query logs to identify the root cause of SQL slow performance. Logs have metrics like Errors, Blocks, Deadlocks, Timeout, etc.

2. Dynamic Management Views (DMVs):

Dynamic Management Views provide server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Typically, we use DMVs for troubleshooting purposes. There are two types of DMVs: Server-scoped DMVs and Database-scoped DMVs. DMVs give real-time data about the current state of SQL Server machine.

Here is an example of using a DMV to get the details about recently executed queries:

SQL
SELECT top 10 query_stats.query_hash AS “Query Hash”,
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS “Avg CPU Time”,
MIN(query_stats.statement_text) AS “Statement Text”

FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
– QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

In the SQL query above, DMV (sys.dm_exec_query_stats) is used to get information about query performance.

3. Query Store:

The Query Store feature in Azure SQL is a tool that allows you to track Query plans, identify performance differences, and improve query performance in SQL Database. Query Store saves the history of query execution plans changes with their performance data.

i.e., if you have a day when the database is performing badly, you can use Query Store to compare the query plans with a good day and find multiple queries that have been affected on a bad day.

For enabling the Query Store using T-SQL, one can execute the following command:

SQL
— Enable Query Store on a database
ALTER DATABASE current SET QUERY_STORE = ON;

In summary, Azure Monitor, Dynamic Management Views (DMVs), and Query Store are the primary sources for performance metrics. Each provides a unique perspective and collects different types of data. Azure Monitor provides an overall view of the health, performance, and operation of your Azure SQL. On the other hand, Query Store captures and presents detailed performance data specific to your application’s workloads, and DMVs provide state-specific server information that can help diagnose problems and tune performance.

Practice Test

True or False: The Query Performance Insight tool can be used for determining performance metrics in Microsoft Azure SQL.

• Answer: True

Explanation: The Query Performance Insight tool provides a list of your DB’s longest lasting queries, allowing you to identify potential trouble spots and optimize performance metrics.

In determining performance metrics, which Microsoft Azure SQL tool can help monitor and identify performance problems in real time?

• A. Query Performance Insight
• B. Azure Monitor
• C. Azure SQL Analytics
• D. All of the above

Answer: B. Azure Monitor

Explanation: Azure Monitor collects and analyzes performance metrics and activity logs for most Azure services. It provides real-time monitoring of your apps and other resources running in Azure.

True or False: Using Performance Recommendations, you can only get automatic tuning recommendations for your database, but not for your entire SQL Server.

• Answer: False

Explanation: Performance Recommendations enables you to get automatic tuning recommendations for both databases and SQL Server on Azure Virtual Machines.

What is Responsible for collecting metrics related to system internals and resource utilization for Azure SQL databases?

• A. Diagnostic Logs
• B. Log Analytics
• C. Azure Metrics
• D. None of the above

Answer: C. Azure Metrics

Explanation: Azure Metrics are responsible for monitoring resource utilization and system internals for Azure services including SQL databases.

True or False: Azure SQL Analytics is only used for advanced cloud-based analytics, not for performance metrics.

• Answer: False

Explanation: Azure SQL Analytics uses Azure Monitor logs to visualize important SQL Database performance metrics.

Which of the following tools can help in logging data for performance metrics in Azure SQL

• A. Azure Monitor Logs
• B. Azure Monitor Metrics
• C. SQL Server Management Studio
• D. Both A and B

Answer: D. Both A and B

Explanation: Azure Monitor Logs and Azure Monitor Metrics both provides capabilities to collect, analyze, and act on telemetry from your cloud and on-premises environments.

True or False: Azure SQL database supports the sys.dm_exec_query_stats dynamic management view for performance metrics.

• Answer: True

Explanation: Azure SQL uses the sys.dm_exec_query_stats dynamic management view to monitor performance of SQL Server, so its related metrics can be determined.

Which of the following tools does not support Azure SQL?

• A. Performance Dashboard Reports
• B. Azure Advisor
• C. SQL Server Profiler
• D. None of the above

Answer: C. SQL Server Profiler

Explanation: SQL Server Profiler is not supported for Azure SQL. Performance Dashboard Reports and Azure Advisor are both widely used with Azure SQL.

True or False: Azure Metrics can track database throughput units (DTUs).

• Answer: True

Explanation: Azure Metrics is a service in Azure that provides pre-configured Charts and Grids showing various performance metrics over time, including Database Throughput Units (DTUs).

What service provides automated checks of an Azure environment to provide personalised recommendations?

• A. Azure Advisor
• B. Azure Monitor
• C. Azure Analytics
• D. None of the above

Answer: A. Azure Advisor

Explanation: Azure Advisor is an Azure service that provides personalised recommendations based on the configuration and usage of your Azure resources and services, including Azure SQL.

Interview Questions

What are performance metrics in the context of Microsoft Azure?

Performance metrics in Microsoft Azure are measurements of system properties that indicate the health, availability, responsiveness, and capability of a system or service.

What tool does Microsoft Azure provide to monitor performance metrics?

Microsoft Azure provides Azure Monitor, a service that collects, analyzes, and acts on telemetry data from your Azure and on-premises environments.

How can you use Azure Monitor to determine sources for performance metrics?

Azure Monitor integrates with a variety of services in Azure to surface metrics directly in the Azure portal, allowing users to determine the sources of performance metrics. It includes Application Insights for application diagnostics and Log Analytics for infrastructure diagnostics.

How can SQL Server performance metrics be sourced in Microsoft Azure?

SQL Server performance metrics can be sourced by using SQL Server Management Studio or Azure SQL Analytics. This involves enabling Query Store on your SQL Database, which collects, stores, and presents detailed information about all queries.

What is Azure SQL Analytics and how does it help in sourcing performance metrics?

Azure SQL Analytics is a solution that provides a comprehensive view of important performance metrics from your Azure SQL Database and Azure Synapse Analytics. It can collect performance metrics for multiple databases and consolidate them for easy analysis.

How can Azure Advisor be used in determining sources for performance metrics?

Azure Advisor is a free Azure service that analyzes your configuration and usage telemetry and then recommends solutions that can help you optimize your Azure resources for high availability, security, performance, and cost.

Can Azure Log Analytics be used for sourcing SQL Server performance metrics?

Yes, Azure Log Analytics can collect data directly from your SQL Server via the Log Analytics agent and can then visualize this data using Log Analytics queries for comprehensive performance insights.

What is a Query Store in Microsoft Azure SQL?

The Query Store feature in Azure SQL Database provides insights on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find top resource consuming queries and allowing you to see their execution plan.

How does Azure Monitor integrate with Application Insights?

Azure Monitor integrates with Application Insights to provide rich, correlated diagnostics logs and views into performance and usage data from your applications. Application Insights collects various metrics for your applications, including page view loads, server response times, exception counts, and more.

Can Azure SQL Analytics collect performance metrics for multiple databases?

Yes, Azure SQL Analytics can collect, integrate, and visualize performance metrics for multiple databases, helping you manage and troubleshoot your databases’ performance more effectively.

Which Azure service can be used to automate data collection?

Azure Automation can be used to automate data collection in Azure. This service provides capabilities to automate tasks across Azure and on-premises environments, reducing errors and increasing efficiency.

What is Azure Diagnostics Extension and its role in sourcing performance metrics?

The Azure Diagnostics Extension is an agent in Azure Monitor that collects monitoring data from the guest operating system of Azure service instances. The data can include performance metrics, event logs, and tracing, which are useful for identifying performance issues.

Can Azure Monitor analyze logs and metrics together?

Yes, Azure Monitor can collect and analyze both logs and metrics together to provide a more comprehensive view of your data. Logs provide insights into the specifics of how your resources are working, and metrics give you numerical information about resource performance.

What is the role of Azure Alerts in performance metrics?

Azure Alerts proactively notify you of critical conditions and potentially attempt to take corrective actions. You can create alerts based on performance metrics, which allows you to respond quickly to potential issues.

What is the purpose of the Azure Monitor Metrics Explorer?

Azure Monitor Metrics Explorer is a component of Azure Monitor that allows you to view and analyze your collected metrics. It’s a versatile tool that enables you to filter, split, and group metrics data, facilitating deeper insight into your resources’ performance.