Azure SQL Analytics

Performance Monitoring: Azure SQL Analytics

Introduction: Why Performance Monitoring Matters

In the world of cloud-based database management, setting up an instance is merely the first step. Once your application goes live, the true challenge begins: ensuring that your database remains performant, cost-effective, and highly available under fluctuating workloads. Azure SQL Analytics—a solution built on top of Azure Monitor and Log Analytics—serves as the primary lens through which you can view the health and performance of your SQL resources.

Without proper monitoring, you are essentially flying blind. You might notice that your application feels slow, but you won't know if the bottleneck is in the application code, the network latency, or a specific T-SQL query that has grown inefficient over time. Azure SQL Analytics provides the telemetry needed to move from a reactive "firefighting" mode to a proactive management strategy. By understanding the metrics, logs, and diagnostic data provided by this service, you can identify resource contention, optimize indexing strategies, and ensure that your database spend aligns with actual usage patterns.

This lesson explores how to configure, utilize, and interpret Azure SQL Analytics. We will move beyond basic dashboarding to understand how to write complex Kusto Query Language (KQL) queries that reveal the hidden performance patterns in your Azure SQL Databases and Managed Instances.

Understanding the Architecture of Azure SQL Analytics

Azure SQL Analytics is not a standalone "service" in the traditional sense; rather, it is a management solution that utilizes Azure Monitor and Log Analytics to collect and visualize data. To understand how it works, you must understand the data pipeline.

1. Data Sources: Your Azure SQL Databases, Elastic Pools, and Managed Instances generate diagnostic logs and metrics.
2. Diagnostic Settings: You configure these resources to stream their logs (such as Query Store Wait Statistics or SQL Insights) to a Log Analytics Workspace.
3. Log Analytics Workspace: This acts as the central repository where your telemetry data is ingested, indexed, and stored.
4. Azure SQL Analytics Solution: This is a pre-configured dashboard and set of KQL queries that runs on top of your workspace, allowing you to visualize the data without having to build every graph from scratch.

The Role of Diagnostic Settings

The foundation of everything we will discuss is the Diagnostic Setting. If you do not configure your SQL resource to send its data to a Log Analytics Workspace, Azure SQL Analytics will have nothing to display. You must enable specific categories of logs, such as SQLInsights, QueryStoreRuntimeStatistics, and WaitStatistics.

Callout: Diagnostic Settings vs. Metrics It is important to distinguish between metrics and logs. Metrics are numerical values that represent a piece of data at a point in time (e.g., CPU percentage). Logs are richer, event-based data that provide context (e.g., a specific query plan ID or a detailed wait type). Azure SQL Analytics excels because it correlates these two, allowing you to see high CPU usage alongside the specific query causing it.

Setting Up Azure SQL Analytics: A Step-by-Step Guide

Before you can monitor, you must configure the environment. Follow these steps to ensure your resources are correctly sending telemetry data.

Step 1: Create a Log Analytics Workspace

If you do not already have one, you need a Log Analytics Workspace to store your telemetry.

1. Navigate to the Azure Portal.
2. Search for "Log Analytics Workspaces" and click "Create."
3. Select your subscription, resource group, and provide a unique name.
4. Choose a region that matches your SQL resources to minimize data transfer latency and costs.

Step 2: Configure Diagnostic Settings for SQL Resources

Once the workspace is ready, you must connect your SQL databases to it.

1. Go to your Azure SQL Database resource in the portal.
2. In the left-hand menu, under the "Monitoring" section, select "Diagnostic settings."
3. Click "Add diagnostic setting."
4. Name the setting (e.g., "SQL-Telemetry-To-LogAnalytics").
5. Check the boxes for the categories you want to collect. At a minimum, you should select:
   • SQLInsights (for general performance data)
   • QueryStoreRuntimeStatistics (essential for query tuning)
   • WaitStatistics (critical for identifying bottlenecks)
   • Errors and Timeouts (for troubleshooting)
6. Under "Destination details," check the box for "Send to Log Analytics workspace" and select the workspace you created in Step 1.
7. Click "Save."

Note: It can take up to 15-30 minutes for data to start appearing in your Log Analytics workspace after you enable Diagnostic Settings. Do not be alarmed if your dashboard is empty immediately after configuration.

Navigating the Azure SQL Analytics Dashboard

Once data begins flowing, you can access the Azure SQL Analytics solution. While the classic "Azure SQL Analytics" solution is being phased out in favor of the newer "SQL Insights" (preview) and native Azure Monitor workbooks, the underlying logic remains the same.

The dashboard typically presents data in several key categories:

• Database Availability: Shows if the database is online or offline.
• Performance: Visualizes CPU, Data I/O, and Log I/O percentages.
• Query Performance: Highlights the most resource-intensive queries based on duration or CPU consumption.
• Wait Statistics: Provides a breakdown of what the database is waiting on (e.g., locks, memory, or disk).

Customizing with KQL (Kusto Query Language)

The real power of this platform lies in the ability to run custom queries against the data. If the standard dashboard doesn't show you exactly what you need, you can write your own KQL.

Example: Finding the Top 10 Most Expensive Queries by CPU

AzureDiagnostics
| where Category == "QueryStoreRuntimeStatistics"
| summarize TotalCPU = sum(cpu_time_d) by query_hash_s
| top 10 by TotalCPU desc

Explanation: This query filters for the QueryStoreRuntimeStatistics category, groups the data by the unique query_hash_s (which identifies the query regardless of parameter values), sums the CPU time, and returns the top 10 culprits.

Example: Analyzing Wait Statistics

AzureDiagnostics
| where Category == "WaitStatistics"
| summarize TotalWaitTime = sum(wait_time_ms_d) by wait_type_s
| sort by TotalWaitTime desc

Explanation: This query helps you understand where your database is spending its time. If LCK_M_X (Exclusive Lock) is consistently at the top, you know you have a concurrency or blocking issue rather than a hardware limitation.

Best Practices for Performance Monitoring

Monitoring is not a "set it and forget it" task. To be effective, you must follow established industry patterns.

1. Establish Baselines

A metric like "60% CPU usage" is meaningless without context. Is this normal for your Tuesday morning batch process, or is it an anomaly? You should capture performance metrics over a period of at least two weeks to understand your "normal" behavior. Once you have a baseline, you can set up alerts that trigger only when metrics deviate significantly from that norm.

2. Focus on Wait Statistics

Many administrators fixate on hardware metrics like CPU or memory. While important, they are often symptoms. Wait statistics tell you the cause. If a query is waiting on PAGEIOLATCH_SH, it means the data is not in memory and must be read from disk. The solution isn't always "more CPU"; it might be "better indexing" or "increasing the database memory limit."

3. Use Query Store

Azure SQL Database includes the Query Store feature by default. Ensure it is enabled and configured correctly. Query Store tracks execution plans, runtime stats, and wait stats over time. When you use Azure SQL Analytics, you are essentially querying the data that Query Store has gathered.

4. Alerting Strategy

Don't alert on everything. If you set an alert for every time CPU hits 80%, you will quickly suffer from "alert fatigue" and start ignoring notifications. Alert only on actionable issues. For example, alert on a high frequency of deadlocks or a sudden drop in transaction throughput.

Warning: Avoid Over-Alerting Creating alerts for every minor fluctuation in performance will lead to a noisy environment where engineers stop paying attention to alerts altogether. Only configure alerts for scenarios that require human intervention.

Common Pitfalls and How to Avoid Them

Even with the best tools, it is easy to fall into traps that lead to inaccurate data or wasted money.

Pitfall 1: Insufficient Data Retention

By default, some Log Analytics workspaces might have short retention periods (e.g., 30 days). If you are trying to perform a quarterly performance review, you will find that your data has disappeared.

• Solution: Check your Log Analytics Workspace settings under "Usage and estimated costs" and adjust the data retention period to match your business requirements (e.g., 90 days or 1 year).

Pitfall 2: High Costs of Ingestion

Sending every single diagnostic log to Log Analytics can become expensive as your database grows. If you have 500 databases, the cost of ingesting high-volume telemetry can exceed the cost of the databases themselves.

• Solution: Be selective about the categories you enable. If you don't need detailed QueryStoreRuntimeStatistics for development environments, turn them off and only enable them for production.

Pitfall 3: Ignoring Parameterization

When analyzing query performance, ensure you are looking at the query_hash. If you look at the raw SQL text, you might see 1,000 different entries for the same query just because the ID numbers in the WHERE clause are different.

• Solution: Always group or summarize by query_hash_s to see the aggregate impact of a query pattern.

Advanced Monitoring: SQL Insights (Preview)

While the classic Azure SQL Analytics solution is useful, Microsoft has introduced "SQL Insights," which provides a more granular, agent-based monitoring experience. It uses a virtual machine (the monitoring profile) to connect to your SQL instances and pull data more frequently and with more detail than standard diagnostic settings.

Features of SQL Insights:

• Customizable Views: You can see performance metrics for multiple SQL resources in a single pane.
• Dynamic Analysis: It provides deeper insights into tempdb contention, memory grant waits, and buffer pool usage.
• Better Scaling: It is designed to handle large environments with hundreds of SQL instances without the overhead of individual diagnostic settings for every single one.

Comparison: Standard Monitoring vs. SQL Insights

Interpreting Performance Data: Practical Scenarios

Let’s look at how to handle common performance scenarios using the data you collect.

Scenario 1: The "Slow Application" Complaint

A user reports that the dashboard loads slowly. You check your Azure SQL Analytics dashboard and see that CPU usage is normal, but WaitStatistics shows a high value for LCK_M_S (Shared Lock).

• Action: This indicates a blocking issue. You should investigate which queries are holding the locks for a long time. You can use the sys.dm_tran_locks view or check the QueryStore reports to find long-running transactions that might be holding locks unnecessarily.

Scenario 2: High Data I/O

Your database is hitting its IOPS limit, causing throttling. You check the "Top Queries by Data I/O" widget.

• Action: You identify a query that is performing a large table scan. By examining the execution plan (which you can link to from the query hash), you realize that the query is missing an index on a frequently filtered column. Adding the missing index reduces the I/O demand significantly.

Scenario 3: Memory Pressure

The database is frequently hitting the "Memory Limit" event. You see high RESOURCE_SEMAPHORE wait types.

• Action: This means queries are waiting for memory grants to execute. You might need to optimize the queries to use less memory (e.g., avoiding large sorts or hashes) or consider scaling up your database to a higher Service Tier (e.g., from General Purpose to Business Critical).

Best Practices for KQL Query Performance

Since you are paying for the data you process in Log Analytics, writing efficient KQL queries is a best practice for both performance and cost control.

1. Use Time Filters: Always include a where TimeGenerated > ago(24h) clause. Without a time filter, the engine scans the entire history of your workspace, which is slow and expensive.
2. Project Only Necessary Columns: Don't use * in your queries. Use the project operator to select only the columns you need for your report.
3. Filter Early: Apply your where clauses as early as possible in the query pipeline to reduce the amount of data processed in subsequent steps.

Tip: Optimizing KQL Use the summarize operator early to aggregate data. For example, if you have 1 million rows of telemetry, summarizing them by query_hash first reduces the data set drastically before you perform any joins or complex sorting.

Security and Compliance Considerations

When monitoring databases, you are often handling sensitive data. While the telemetry logs themselves usually do not contain actual customer data (they contain query hashes and execution plans), you must still be mindful of:

• Access Control: Use Role-Based Access Control (RBAC) to limit who can view the Log Analytics Workspace. Developers may need access to performance stats, but they shouldn't necessarily have access to security-related logs if they aren't part of the security team.
• Data Masking: If your application logs errors that include sensitive information, ensure that your application-level logging is sanitized before it hits the database logs.
• Regional Compliance: Ensure that your Log Analytics Workspace is located in a region that complies with your organization's data residency requirements.

Putting It All Together: A Proactive Workflow

To truly master Azure SQL Analytics, adopt this weekly workflow:

1. Monday Morning Review: Spend 15 minutes checking the "Top 10 Queries by CPU" and "Top 10 Queries by Duration." Look for any new entries that weren't there the previous week.
2. Wait Stat Analysis: Check the "Wait Statistics" to see if there is a shift in the primary bottleneck. If PAGEIOLATCH_SH has increased, it might be time to review your indexing strategy.
3. Alert Review: Check your alert history. Did you have any "false positives"? If so, adjust the thresholds. Did you have any alerts that you missed? If so, investigate why.
4. Capacity Planning: Look at the 30-day trend for CPU and Memory. If you are consistently hitting 80% usage, start the conversation with your team about scaling or code optimization before an outage occurs.

Key Takeaways

• Visibility is Foundation: You cannot optimize what you cannot measure. Enabling Diagnostic Settings is the non-negotiable first step in SQL performance management.
• Wait Stats are More Important than CPU: Hardware metrics tell you that there is a problem; wait statistics tell you what the problem is. Always investigate the "why" behind a performance dip.
• Leverage Query Store: The Query Store is the engine behind your performance data. Ensure it is enabled and that you understand how to use query_hash to identify patterns rather than individual executions.
• Manage Costs through Selectivity: Be thoughtful about what you log. Not every database needs every diagnostic category enabled at all times.
• Optimize Your Queries: KQL is powerful, but it can be expensive. Write efficient queries by filtering on time and columns early, and always aggregate data as soon as possible.
• Establish Baselines: Performance is relative. Know what "normal" looks like for your specific environment so you can distinguish between a minor fluctuation and a genuine incident.
• Proactive vs. Reactive: Use the data to plan for growth and optimize code before it becomes a bottleneck. Monitoring should be a regular part of your operational routine, not just a tool for troubleshooting outages.

By following these principles and utilizing the tools provided within the Azure ecosystem, you can transition from a reactive administrator to a proactive database engineer, ensuring that your SQL resources are always performing at their peak while remaining within your budget. Remember that monitoring is an iterative process; as your application evolves, your monitoring strategy should evolve with it. Stay curious, keep exploring the KQL data, and always look for the story behind the numbers.