Lessons Learned #558: What an Execution Plan XML Can Tell Us in Azure SQL Database

While investigating performance issues in Azure SQL Database, we asked for the execution plan to get insights into the query’s operations. As we examined the findings, the customer posed a thoughtful question: What do you typically look for when examining the XML of an execution plan?

This was a great question. Although the graphical plan provides a clear view of the data flow, the XML version contains crucial information about parameters, statistics, warnings, and execution details that can clarify why a query behaved a certain way. Instead of delving into every single attribute, I usually start with a few key checks that link the customer’s issues with the query’s operations.

In this article, I’ll guide you through those checks: examining the type of plan, reviewing parameter values, comparing estimated and actual rows, investigating statistics, identifying missing indexes, looking into implicit conversions, and understanding why we might have a serial plan. The XML snippets below are simplified versions derived from previous discussions in the Lessons Learned troubleshooting series or based on fictitious values.

When it comes to checks related to actual rows, I request an actual execution plan. In SQL Server Management Studio (SSMS), you can enable the “Include Actual Execution Plan” option or retrieve it with the command SET STATISTICS XML. Use either method. The second option runs the query and provides its execution plan. Ensure you have the necessary permissions to execute the command and the SHOWPLAN right.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

-- Execute the SELECT that we’re investigating here.

SET STATISTICS XML OFF;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Once you run the query and obtain the execution plan, save it as a .sqlplan file and keep the Messages output. Make sure to note when it was captured, the parameters used, and any symptoms that were reported. A later execution might not reflect the same conditions. Be careful to review query literals, parameter values, and object names before sharing the file.

A batch or procedure may have multiple statements, so I focus on locating the relevant StmtSimple and its QueryPlan before interpreting any warnings. The StatementId and operator NodeId assist in associating observations with the correct statement and operator; however, the NodeId is not a consistent identifier across different compilations.

Next, I check the ParameterList. This section, when available, displays both the value used during plan compilation and the value during the actual execution.

In this example, the plan was compiled with a CustomerId of 42 but executed with 900. If one customer has a few orders while another has thousands, I’d examine their data distribution and actual plans. This variance in values warrants an investigation into parameter sensitivity; it doesn’t necessarily confirm that plan reuse caused the problem. If the runtime value isn’t available, it does not imply it matched the compiled value. Furthermore, eligible queries can purposely utilise different plan variants through Parameter Sensitive Plan optimization.

If the query is slow in the application but runs quickly in SSMS, I would compare the SQL text, parameter types and values, database context, and session options. The StatementSetOptions reveals options involved during compilation.

Recognising differences in context can clarify why the application and SSMS produce different cached plans. I capture both for comparison rather than assuming that a quick SSMS execution reflects the application. We still need to connect the differences to the measured performance.

I trace the rows through the plan, searching for the first significant discrepancy between what the optimizer anticipated and what the operator processed during execution.

In this scenario, the operator estimated 100 rows but returned 20,000, leading to an extensive underestimate. Additionally, it read 80,000 rows. I would want to understand why the estimate was so off, along with why the operator read significantly more rows than it returned. These are two distinct issues.

For operators that are executed multiple times or in parallel, it’s essential to take note of the number of executions and threads before making any comparisons. Don’t simply sum the rows from all operators to determine the overall query result size.

A common question that follows relates to whether the statistics were outdated. The OptimizerStatsUsage section identifies which statistics were consulted during compilation.

I examine the relevant statistics object, considering its LastUpdate, SamplingPercent, and ModificationCount. An outdated timestamp or low sampling percentage alone does not confirm that it caused the estimate to be inaccurate. It’s important to remember that updated statistics do not account for every discrepancy; factors like parameter values, predicates, and data distribution play significant roles too.

The XML provides information on the statistics consulted during compilation. To check their current status, I use sys.dm_db_stats_properties as shown below. Remember, a subsequent update may not modify the evidence already captured in a saved plan.

-- Run in the affected database and replace with the actual table name.
SELECT st.name AS statistics_name,
       sp.last_updated, sp.rows, sp.rows_sampled,
       sp.modification_counter
FROM sys.stats AS st
OUTER APPLY sys.dm_db_stats_properties(
    st.object_id, st.stats_id) AS sp
WHERE st.object_id = OBJECT_ID(N'PlanXmlLab.Orders');

The last_updated field indicates the latest statistics update, while rows and rows_sampled detail the population and the sampling at that point. The modification_counter tracks how many changes have occurred to the leading statistics column since the last update. Keep in mind that metadata visibility and your permissions can impact the output.

It’s crucial to compare this information against the same statistics object within the plan. Timestamps alone don’t reveal whether an update was automated or manual. If an update is warranted, it’s best to validate it through controlled comparisons using the same query and representative parameters, then capture a newly compiled plan.

An Index Seek can also engage with many rows. I scrutinise the SeekPredicates, any residual Predicate, and ActualRowsRead where applicable. A broad seek range followed by post-filters can elucidate why the rows read dramatically outnumbered the rows returned.

In addition, a Key Lookup might be cheap under one instance but costly when it’s executed repeatedly for numerous rows. Sometimes, a scan can turn out to be the most suitable option when a large portion of the table fits the criteria. The operator’s name provides a starting point; examining the workload reveals much more.

For our customer, a practical explanation would be invaluable: this operator returned far more rows than anticipated, these were the statistics that were referenced, and this provides the next point of comparison to clarify why that happened. This is more helpful than simply suggesting an update based solely on a timestamp.

Missing-index messages are straightforward to spot. I examine the advice given but also verify if it’s applicable to the query and the existing indexes.

This sample suggests creating an index on CustomerId with Amount included for additional coverage. EQUALITY and INEQUALITY represent predicate columns, while INCLUDE denotes covering columns. However, this suggestion doesn’t answer every detail about index design.

The impact estimate indicates a projected reduction in optimizer cost. A score of 85 doesn’t guarantee an 85% decrease in elapsed time. The XML may include multiple suggestions that the graphical plan does not present. Always review existing indexes and the associated write overhead, then measure the effects of the proposed changes.

In this separate illustration, I would investigate the Code column type and the declaration of the application parameter. Using a varchar column against an nvarchar parameter can result in a conversion issue on the column side because nvarchar takes priority.

The warning indicates something that could potentially impact the plan, but it doesn’t pinpoint a measured root cause. If a conversion hampers an efficient seek really depends on types, collation, and the expression used. Testing an appropriate parameter declaration while maintaining required character semantics, and comparing the results, reads, and CPU usage is essential. A conversion occurring in the output is distinct from one in a search predicate.

Often, customers wonder why a query does not run in parallel even when the database is provisioned with multiple vCores. I check for NonParallelPlanReason in the QueryPlan before assuming that increasing resources or a higher MAXDOP would change the behaviour.

A serial plan may be appropriate for certain queries. While the attribute can indicate a limitation, its lack doesn’t always clarify the optimizer’s rationale. Inlining of scalar UDFs can eliminate an obstacle without ensuring that the plan runs in parallel. A DegreeOfParallelism of “1” does not equate to parallel processing.

In Azure SQL Database, check the database-scoped MAXDOP and any query hints. Simply having more vCores doesn’t bypass every limitation or guarantee that a parallel plan will be selected.

To address the customer’s question, my approach involves a systematic reading process: identify the statement and capture type, inspect parameters, compare estimated and actual workloads, and analyse related statistics and warnings. Each observation ought to lead us towards formulating better questions and selecting relevant tests.

For comparing before and after, retain the query, parameters, data, and pertinent settings. Assess returned results, logical reads, CPU usage, and duration. A plan reflects a single compilation or execution instance; Query Store can be invaluable in determining if performance has changed over time.

• Ensure the plan belongs to the affected statement and execution.
• Verify parameter values and the application context.
• Compare estimated versus actual rows, considering repeated workload.
• Investigate compilation statistics and their current update details.
• Look for missing indexes, implicit conversions, and reasons for serial plans.
• Confirm explanations with measured comparisons.