Lessons Learned #549: Reproduce, Challenge, and Validate – Testing Technical Assumptions

During an Azure SQL investigation, the available evidence may point to a technically reasonable explanation.

For example:

• A network change may appear to explain a connectivity issue.
• High CPU may appear to explain a performance degradation.
• A query plan change may appear to explain a longer execution time.
• A failover may appear to explain why the behavior disappeared.

These explanations may be correct. However, before considering them confirmed, I normally try to reproduce the behavior and validate the conditions under which it occurs. A reasonable explanation is still a hypothesis until the available evidence supports it consistently.

Reproduce the Smallest Useful Scenario

A reproduction does not always need to recreate the complete production environment. In many cases, a smaller test provides a clearer result.

For a connectivity issue, the test may require only:

• one client;
• one database endpoint;
• the same authentication method;
• the relevant network path;
• a clearly identified timestamp.

For a performance issue, it may require:

• one representative query;
• the same parameters;
• the relevant database configuration;
• a controlled execution period.

The objective is to isolate the behavior being investigated while reducing unnecessary variables. The smaller the scenario, the easier it normally becomes to understand why the result changes.

Define the Expected Result

Before running a test, I try to define what result I expect. For example:

• Hypothesis: The connection behavior depends on a specific network path.
• Expected Result: The behavior should occur when the connection uses that path and should not occur when an alternative path is used.
• Result That Would Challenge the Hypothesis: The same behavior occurs independently of the network path.

The same approach can be used for performance investigations.

• Hypothesis: The query slowdown is caused by Data IO saturation.
• Expected Result: Query duration should increase when Data IO reaches its service-level limit.
• Result That Would Challenge the Hypothesis: The same slowdown occurs while Data IO remains low.

Defining the expected result before the test helps avoid interpreting every outcome as confirmation of the initial explanation.

Change One Variable at a Time

When several conditions are changed simultaneously, it may be difficult to determine which one affected the result.

For example, suppose a performance test includes:

• a larger service objective;
• updated statistics;
• a new index;
• lower concurrency.

If performance improves, the result is positive, but it may not clearly identify which change produced the improvement.

A more useful approach is to test each relevant change separately.

This does not mean that every investigation requires an extensive test matrix.

The objective is simply to avoid changing several important variables at the same time when we need to understand which one explains the behavior.

Compare Where the Behavior Occurs and Where It Does Not

Understanding where a behavior does not occur can be as useful as reproducing where it does.

For example:

• Does the connection fail from one application server but succeed from another?
• Does the query perform normally with different parameters?
• Does the issue occur only through one network path?
• Does the previous execution plan perform better?
• Does the behavior disappear when concurrency is reduced?

These comparisons help define the boundaries of the problem.

They may not immediately identify the complete root cause, but they help determine which conditions are relevant and which ones are less likely to explain the result.

Challenge the Explanation

Once a test appears to support a hypothesis, I normally try to challenge it. For example, if performance improves after scaling the database, it may be tempting to conclude that CPU was insufficient.

However, scaling may also provide:

• more memory;
• higher Data IO capacity;
• greater transaction-log throughput;
• additional workers;
• different resource limits.

The improvement is important evidence, but additional information may still be required to identify which resource was actually limiting the workload.

Similarly, if a failover restores normal performance, it may also have:

• disconnected blocking sessions;
• refreshed application connections;
• caused query recompilation;
• reset a temporary condition.

The action that restores normal operation may not, by itself, fully explain the original cause.

Document the Result and Its Limitations

Not every test produces a definitive answer.

A useful conclusion should describe:

• which conditions were tested;
• what result was expected;
• what result was observed;
• whether the result was repeatable;
• which differences from the original scenario remained;
• what the evidence did and did not allow us to confirm.

For example:

The behavior was reproduced only when the connection used the affected network path. The same authentication method and database endpoint worked successfully through an alternative path. Based on these tests, the network path was confirmed as a relevant condition. The available evidence did not identify the specific network component responsible for the behavior.

This conclusion is useful because it clearly separates what was validated from what remains unknown.

Conclusion

Reproduction is one of the most valuable troubleshooting tools available to an engineer.

However, its purpose is not simply to make an error occur again.

A useful reproduction should help us understand:

• which conditions are required;
• which conditions are not relevant;
• what result supports the hypothesis;
• what result challenges it;
• how consistently the behavior can be observed.

The most effective tests are often simple:

• reproduce the smallest useful scenario;
• define the expected result;
• change one variable at a time;
• compare affected and unaffected conditions;
• document the limitations.

Reproduction does not only confirm that a behavior exists. It helps define the conditions under which the technical conclusion can be trusted.