The Execution Plan Saga #1: Getting Started

When we need to troubleshoot a query that is having performance issues, there are two possible routes: one is to empirically modify the query, pushing buttons and knobs until, eventually, things get better. The other is to analyze the execution plan and take specific, focused actions, based on the identified problematic areas. Sometimes both approaches can lead to similar outcomes and the first can even be faster, especially when we’re not used to analyze execution plans. Nevertheless, the latter is a much more consistent approach, simply because there will be times when blindly applying modifications won’t fix the problem and you’ll need to figure out the root cause, in order to solve it.

An execution plan is a blueprint, a set of algorithms and properties that SQL Server processes in order to retrieve the requested data. If we are able to understand these algorithms and their inherent properties, putting them into context, we will be incredibly more efficient solving problems.

Elements of an Execution Plan

As already mentioned, an execution plan comprehends operators, with their respective properties and data flows. Operators represent specific algorithms chosen by SQL Server to perform the logical operations that materialize the specified query. Hence, they serve multiple purposes:

  • Read Data
  • Combine Data
  • Group / Sort Data
  • Manipulate Data
  • Modify Data
  • Performance-Related

On the other hand, arrows represent the data flow between operators. But just these operators and arrows alone tell us little, we must analyze properties to have a proper understanding of what’s happening. Each operator has its own specificities, so, inevitably, they will hold distinct properties, that are relevant in the context of that specific operation.

Despite that, we can make some conclusions just by looking at the execution plan e.g. the width of data flow arrows, the cost of each operator, or the time spent on each operation. 

It’s important to understand that we can look to the plan in distinct moments:

  • Before Execution: Estimated Execution Plan
  • After / During Execution: Actual Execution Plan

The names may suggest that two separate plans are created, but that’s not the case. It’s actually the same plan, the only thing that differs is the amount of information available.

In the Estimated Execution Plan, we can see, for example, how many rows SQL Server estimates that a particular operator will return, but as this is prior to execution, we obviously won’t be able to see the actual number of rows returned.

To compare the estimated vs actual row count, we’ll need to look into the Actual Execution Plan. Since it comprehends runtime statistics, it’s evidently preferable to use it rather than the estimated plan to troubleshoot problems, however, in those dark moments when for some reason we’re unable to get the Actual Execution Plan, having the Estimate Execution Plan is much better than nothing.

Troubleshooting Guidelines

Examining an execution plan can be overwhelming if we don’t know what to look for. The following guidelines are based on a set of sequential steps proposed by Grant Fritchey on his book “SQL Server Execution Plans”

Warnings

When a yellow warning sign or a red exclamation mark arises attached to an operator, it means SQL Server is telling us that there is something we should check. Despite the fact that sometimes the behavior related to the warning message is not the root cause of the problem we’re trying to solve, often it is and it’s just there at plain sight – easy buck.

To view the warning message, you can hover the operator’s icon or open its properties.

First Operator

The first operator (typically SELECT, UPDATE, INSERT or DELETE) holds very relevant information about the entire plan. The following list include some properties that will frequently be helpful to us:

  • QueryTimeStats: Values for CPU time and elapsed time (in milliseconds).
  • MemoryGrantInfo: Information about query memory requirements. More details about each property can be viewed in a previous post.
  • WaitStats: Top wait stats on the query execution, including the number of waits (WaitCount) and the total wait time (WaitTimeMs).
  • Parameter List: All parameters, including data type, value at compile time (if the parameter was sniffed), and value at runtime (in an actual execution plan). These values will help us to understand how parameter sniffing impacted the query.
  • QueryHash: Hash value resultant from the query text. It is used by SQL Server to find plans for similar or equal queries.
  • QueryPlanHash: Hash value resultant from the execution plan. It is used by SQL Server to find plans for similar or equal execution plans.
  • Set Options: Values of specific SET options at compilation time. It’s important to look at these options because different value combinations generate different plans. Sometimes these variations are innocuous and almost invisible, but other times they might shape a completely different plan. For example, ARITHABORT is one of those options that can trigger very distinct behaviors on the execution plan, depending on its value.
  • Type of optimization: Can either be “TRIVIAL” or “FULL”. A “TRIVIAL” optimization means that there were no meaningful execution alternatives for the optimizer to evaluate. It is important to stress that this not related to workload volume. If we execute SELECT * FROM HugeTable, the query can run for minutes, still the optimization level will show “TRIVIAL”, just because SQL Server hadn’t any other choice but to do a full table/index scan. In a “FULL” optimization SQL Server will evaluate the maximum alternatives possible, given the time and resources limitations. This optimization level also unlocks other properties, e.g.:
    • Reason For Early Termination Of Statement Optimization: Specifies why the optimizer gave up on optimizing the plan. The possible values are:
      • Good Enough Plan Found: The Optimizer found a plan with a cost below the threshold.
      • Time Out: Compilation time exceeded the maximum optimization time, defined at the start of the optimization phase.
      • Memory Limit Exceeded: Compilation stopped due to memory shortage.

Operator Cost

Commonly, we’ll see expensive operators standing out from the rest. When the cost is mainly in one or a couple operators, if we manage to improve those specific areas, it will have a substantial impact on the whole execution. Just remember that these costs provide guidelines and aren’t always accurate, some operators even have a fixed cost, which can be very misleading. Another visual hint that is quite useful in the Actual Execution Plan is the time displayed below each operator. This value can be cumulative (including time spent on child operators) or individual when dealing with Batch Mode operators.

Data Flow

Still regarding these visual hints, the thickness of data flow arrows is one of the coolest. Their width is proportional to the row count (based on actual row count when available, or on estimated row count otherwise).

Additionally, we must analyze abrupt transitions, i.e. where wide arrows become narrow, because that may indicate that the filtering is occurring too late. We want to “push” filters as far to the right side of the plan as possible.

Estimates Vs Actuals

As already covered in a previous post, statistics are absolutely vital in the process of creating an execution plan. Estimates underpin some algorithm choices and consequently, bad estimates can lead to bad choices. A particular algorithm might be efficient when dealing with few rows, but extremely inefficient when dealing with a huge number of rows. Nested Loops is a great example of that. That’s why it is so important to keep an eye on estimated vs actual row counts. If a large gap between estimated and actual row count led to a catastrophic algorithm choice, we’ll need to figure out how to improve those estimates or how to hint SQL Server to choose differently.    

Read Operators

Analyzing read operations is an important part of troubleshooting execution plans. Althought there is no such thing as an evil operator and we can’t state that a scan is a bad thing or a seek is always good, patterns like a scan that returns few rows or a seek that returns a huge amount of rows might be worth checking.

Posteriorly, we’ll analyze read data operators in greater detail.🖖

Aventura-profissional PNG from pngtree.com

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão /  Alterar )

Google photo

Está a comentar usando a sua conta Google Terminar Sessão /  Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão /  Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão /  Alterar )

Connecting to %s