top of page

Basic knowledge about MS-SQL-server execution plans

(alle Versionen/all versions)

Mittwoch, 9. Juni 2021

English

The basics

Why are "execution plans" so important?

"Execution plan" types

How to read an "execution plan" ?

What to look for?

Further links



English


The basics

An "execution plan" is basically a strategy by the SQL server, determined by its

Optimizer module, how to access/manipulate the data in the database.


An "execution plan" can be influenced by the developer via "query hints".

The SQL server makes most important decisions based on the "execution plan":


  • Can cached plans be reused?

  • In what order should tables be processed?

  • How to order and group data?

  • How to perform JOIN operations?

  • Which indexes to use?


Why are "execution plans" so important?

The "execution plans" give the central insight into the execution/processing strategy of the executed SQL query.

⚠️It is the foremost option to tune any SQL performance problems at its source.⚠️


The hardware is only sometimes the problem!

A High CPU/IO consumption often indicates poorly tuned SQL or "execution plans".

So, understanding "execution plans" is a prerequisite for performance tuning!


"Execution plan" types

"Estimated execution plans" are created without ever running the query. They use statistics for an estimation. They are still suitable for long-running query tuning.

"Actual execution plans" are created when the actual query runs. They use the real data.

Both types can be different/"grow apart"; e.g. statistics are out of date.


⚠️It is recommended to check the "real execution plans" regularly to make sure that crucial queries run well!⚠️


How to read an "execution plan" ?

To read (i.e. interpret) an SQL "execution Plan" correctly, you should always bear in mind that the flow of the execution starts from the right to the left and top to bottom, with the last operation shown at the top left, which is usually a SELECT operator in most queries, containing the final result of the query.


⚠️This instruction refers to how to read/interpret the plan best! It is not to be confused with how the SQL server actually executes the plan internally.

Execution itself is driven from left-to-right (demand from SELECT), pulling rows from the right-hand operators as needed!


Example

The following T-SQL statement selects address and debtor data from the Sage ERP system "Sage 100" which uses a Microsoft SQL Server database.

SELECT TOP 10 
A.Adresse
 ,D.Kto
 ,A.Matchcode
FROM dbo.KHKAdressen AS A WITH (READUNCOMMITTED)
INNER JOIN dbo.KHKKontokorrent AS D WITH (READUNCOMMITTED)
ON A.Mandant = D.Mandant
AND A.Adresse = D.Adresse
AND D.KtoArt = 'D'
WHERE A.Mandant = 99
 AND A.Matchcode LIKE 'Test%'
ORDER BY A.Matchcode

If you were to activate the display of the execution plan for this query (press default keyboard shortcut CTRL+M), it would most likely look something like the following, afer the query is being executed.

ree

When you reach a join or concatenation operator where multiple branches merge into one operator, you can proceed to the right-most operator of one of the lower branches and start reading right to left again.

Example:

ree

Again, the rule of thumb: read the plan right to the left, top to bottom.

The arrows between the operators represent the direction and the amount of data passed between these operators in the SQL Execution Plan. The arrow's thickness indicates the amount of data passed between the operators.


⚠️This instruction refers to how to read/interpret the plan best! It is not to be confused with how the SQL server actually executes the plan internally.

Execution itself is driven from left-to-right (demand from SELECT), pulling rows from the right-hand operators as needed!



What to look for?

If you hover the mouse over any operator or arrow, you will get more information in a popup window.


⚠️You should always inspect the "cost" information given on each operation.

Also, make sure to check if any "table scans" occur, which are, in general, bad for the performance of the query and should be avoided!⚠️


The "execution plan" of the above SQL query clearly shows that two index checks cause the main costs for this query due to the filter of the account types limiting the results to debtors only and also the SQL LIKE filter on the field "matchcode" of the address (which costs the most).

No table scans were done in this case to select the data 😉👍.


Further links

https://bertwagner.com/posts/5-things-you-need-to-know-when-reading-sql-server-execution-plans/





bottom of page