Lesson Learned #548: From Symptoms to Evidence – How I Approach an Azure SQL Investigation

After working on many Azure SQL support cases, I have learned that the initial service request details are essential, although they may not always provide sufficient information to understand the complete technical situation.

When a service request is initially created, the available information may be limited to a brief description such as:

• The database is running slowly.
• CPU utilization is high.
• Connections are failing.
• The issue started after a deployment.
• Performance improved after a failover.
• The workload became slower after a service-tier migration.

These details provide an important starting point for the investigation. However, the initial description may not yet include all the relevant timestamps, metrics, logs, configuration details, or historical context required for a complete technical assessment.

This is completely understandable. At the time the service request is created, the immediate priority is normally to describe the observed behavior and its impact. Additional technical context can then be collected progressively during the investigation.

For that reason, I normally use the initial service request details to understand the reported symptom and determine which additional information may be required. The first objective is to clarify the observed behavior, define the affected scope, and identify the evidence that may help us evaluate the different possible explanations.

In this article, I would like to share the approach I normally follow when moving from an initial service request description to an evidence-based technical conclusion.

Clarifying the Observed Behavior

The first thing I normally try to understand is the exact impact.

For example:

• Was the application completely unavailable, or was it slower than usual?
• Did the behavior affect all users or only a specific group?
• Did it affect all queries or only one process?
• Was the issue continuous or intermittent?
• Did it affect one database, several databases, or the complete logical server?
• Did the problem occur only from a particular network location?
• Did existing connections continue working while new connections failed?

These questions may appear simple, but the answers can significantly change the direction of the investigation. For example, the initial service request details may indicate:

Connections to Azure SQL Database are failing. After reviewing the scenario, we may find that:

• other applications can connect successfully;
• only one application instance is affected;
• existing connections continue working;
• only new connections are failing;
• the issue occurs from one specific network path.

With this additional information, the situation may no longer appear to be a general Azure SQL connectivity issue. The investigation may instead need to focus on areas such as:

• application connection pooling;
• authentication;
• DNS resolution;
• token renewal;
• network routing;
• firewall rules;
• a specific application instance;
• client-side resource pressure.

Similarly, a service request may indicate that the database is slow. Before looking for the cause, I normally try to understand what “slow” means in that specific situation.

For example:

• Are all queries slower?
• Is only one stored procedure affected?
• Has execution time increased from seconds to minutes?
• Is the delay occurring while opening the connection?
• Is the delay occurring while executing the command?
• Is the application waiting on Azure SQL Database or on another dependency?

Before reviewing metrics, logs, or execution plans, it is important to convert the initial description into a precise and measurable technical symptom.

Distinguishing Observations from Possible Explanations

One of the most useful habits I have developed is to separate what has been observed from what still needs to be validated.

Consider the following statement: The application became slow when database CPU reached 95%.

From this description, we may have two observations:

• the application experienced a slowdown;
• database CPU reached 95%.

However, the relationship between those two observations still needs to be validated. High CPU may have caused the slowdown, but it could also be part of a larger chain of events. For example, CPU utilization may have increased because:

• the application started sending more requests;
• blocking caused requests to accumulate;
• a query execution plan changed;
• application retries generated additional workload;
• a scheduled process started running;
• concurrency increased;
• a query began processing more data;
• data distribution changed;
• statistics changed;
• a maintenance task started.

In other words, high CPU may be the cause of the performance issue, but it may also be the result of another condition. I have seen similar situations after failovers, application restarts, and scaling operations. For example, the available details may indicate: Performance returned to normal after a failover.

The failover is an important part of the investigation, but it may not, by itself, fully explain the original cause.

A failover may also:

• disconnect blocking sessions;
• cause queries to compile again;
• refresh application connections;
• reset a temporary condition;
• clear some cached state;
• coincide with a reduction in workload.

For this reason, I normally try to distinguish between:

• what was initially reported;
• what has been confirmed by telemetry;
• what is currently considered a possible explanation;
• what has already been validated.

This distinction helps prevent an early assumption from becoming the final conclusion before sufficient evidence is available.

Building a Timeline

When timestamps and historical information are available, I normally try to build a timeline. A timeline is often one of the most useful parts of an investigation because several events may initially appear related until they are placed in the correct order. Consider the following example:

If the initial service request description mentions only the CPU peak and the application timeouts, CPU may appear to be the most likely starting point.

However, the timeline shows that the number of active sessions and Data IO utilization increased before CPU reached 95%. This does not immediately confirm the root cause, but it changes the questions that should be asked.

For example:

• Why did the number of sessions increase?
• Did requests start taking longer because of IO pressure?
• Did application retries contribute to the workload increase?
• Was the CPU peak the initial cause, or was it a consequence of the accumulating workload?
• Did the application deployment change request volume or execution patterns?

Depending on the situation, the timeline may include information from:

• Azure Monitor metrics;
• Query Store;
• application logs;
• deployment history;
• audit events;
• scaling operations;
• failovers;
• configuration changes;
• maintenance processes;
• network changes.

The objective is not to collect every piece of information available. The objective is to identify the sequence of events that may explain what happened before, during, and after the reported incident.

Using Each Data Source for the Right Question

Another lesson I have learned is that no single source of telemetry normally explains the complete situation.

Different data sources answer different questions.

Azure Monitor

Azure Monitor can help identify whether the database reached limits related to:

• CPU;
• Data IO;
• log write;
• sessions;
• workers;
• storage;
• connection failures;
• deadlocks.

These metrics are very useful for identifying when resource pressure occurred. However, a database-level metric may not directly identify the query, application, or operation responsible for that resource usage.

Query Store

Query Store may help identify:

• changes in query duration;
• increased CPU consumption;
• changes in execution count;
• increased logical reads;
• execution-plan changes;
• query regressions.

It can be especially useful when a database-level metric needs to be correlated with specific query activity.

Dynamic Management Views

Dynamic Management Views may provide information about:

• active requests;
• waits;
• blocking;
• sessions;
• open transactions;
• memory grants;
• current resource consumption.

This information is especially valuable while the issue is occurring.

However, some of the data may no longer be available after the event has ended.

Application Logs

Application logs may help identify:

• connection timeouts;
• command duration;
• retry behavior;
• connection-pool exhaustion;
• authentication errors;
• dependency failures;
• changes in request volume.

These logs often provide context that may not be visible from the database side.

Deployment and Configuration History

Deployment and configuration history may help explain why the behavior began at a particular time.

This may include:

• application releases;
• schema changes;
• index operations;
• compatibility-level changes;
• connection-string updates;
• service-tier changes;
• network changes;
• security configuration changes.

The important point is to choose the evidence according to the question being investigated.

For example, if I am investigating whether a workload is limited by Data IO rather than CPU, reviewing only CPU percentage may not provide sufficient information.

I would also want to understand:

• when Data IO reached its limit;
• whether query duration increased during the same period;
• which queries generated the highest number of reads;
• whether the workload started reading more data;
• whether the same degradation occurred when Data IO remained below its limit;
• whether additional IO capacity changed the result.

The investigation becomes more effective when every piece of telemetry is connected to a specific technical question.

Collecting more data does not automatically produce a better conclusion.

Keeping More Than One Possible Explanation Open

It is easy to identify one technically reasonable explanation and begin searching only for evidence that supports it. I normally try to avoid doing this, especially during the first stages of the investigation. For example, imagine that a database is migrated from a DTU-based service tier to a vCore-based service tier and the workload subsequently performs more slowly.

One possible explanation is that the new environment does not provide sufficient CPU.

However, other possibilities may include:

• lower Data IO capacity;
• lower transaction-log throughput;
• different memory availability;
• an execution-plan change;
• statistics changes;
• a compatibility-level difference;
• increased concurrency;
• a change in request volume;
• a change in data size or distribution;
• an application change unrelated to the migration.

Each possible explanation requires different evidence. If CPU remains moderate while Data IO repeatedly reaches its limit, increasing the number of vCores without reviewing the storage characteristics may not address the main constraint.

If Query Store shows a plan regression immediately after the migration, the selected service tier may not be the primary cause. If execution count doubled after an application deployment, the database may simply be processing more work than before. Keeping several possible explanations open does not make the investigation less decisive.  It reduces the risk of reaching a conclusion before the relevant evidence has been reviewed.

Defining What Would Challenge the Hypothesis

One of the most useful questions I ask during troubleshooting is: What result would demonstrate that my current hypothesis may not be correct?

Suppose the current hypothesis is: The performance degradation is caused by Data IO saturation.

Evidence supporting this hypothesis may include:

• Data IO reaches 100% during the affected periods;
• query duration increases at the same time;
• the affected queries perform a high number of physical reads;
• CPU remains below its limit;
• performance improves when the workload runs with additional IO capacity.

However, I should also look for results that may challenge the hypothesis.

For example:

• Does the same slowdown occur while Data IO remains low?
• Are there periods with high Data IO but normal application performance?
• Are queries with very few reads also affected?
• Does additional IO capacity consistently improve the workload?
• Does changing another variable produce a larger improvement?
• Does the issue occur in an environment where the same IO pressure is not present?

If an investigation only searches for supporting evidence, almost any initial theory may appear correct.

Actively looking for evidence that challenges the current explanation makes the final conclusion stronger and more reliable.

Conclusion

After working on many support investigations, one of the most important lessons I have learned is that the initial service request details and the final technical conclusion naturally serve different purposes. The initial description provides the first available information about the observed behavior and its impact. At that stage, some of the relevant logs, metrics, timestamps, configuration details, or historical context may not yet be available.

A reliable investigation begins with the initial service request details and develops progressively as additional evidence and technical context become available.