By: Tibor Nagy
Overview
When SQL Server executes a query it uses a query plan to determine how to access data and complete the query. SQL Server offers DBAs and developers the ability to see these query plans to assist in query optimization. Without query plans it would be difficult to figure out how to tune and optimize your queries.
One such version of query plans is Graphical Execution Plans which allows you to see a graphical representation of the processing steps of your query to find optimization opportunities.
Explanation
The Query Execution Plans describe the steps and the order used to access or modify data in the Microsoft SQL Server database. In this tutorial we will use the terms Execution Plan, Query Plan and Query Execution Plan interchangeably.
Briefly, the Query Plan defines how SQL statements are physically executed by the server. The Query Plan describes the data retrieval and storage methods that are used by the Query Optimizer to execute a specific query. For example, it includes whether the whole table should be read or if an index is used to read a small number of records.
The Execution Plan consists of different operations and each operation has one output which is called the result set. The operations can have one or more inputs such as join operations that have two inputs. Each result set (output) will be the input for the next operation until the SQL statement is finished executing. Therefore the data flow can be drawn as a connection between operators from right to left.
There are many potential ways to execute a query thus SQL Server has to choose the most beneficial one. In the case of very complex queries where there can be many variations, so SQL just picks a plan that is good enough.
The executed Query Plans are also stored in the Procedure Cache, so they can be retrieved and reused if a similar query is executed.
SQL Server can create plans in two ways:
- Actual Execution Plan - created after execution of the query and contains the steps that were performed
- Estimated Execution Plan - created without execution of the query and contains an approximate execution plan
In the next sections we will look at sample execution plans and how to read them.
Additional Information
- Query Plans Tips category on MSSQLTips
- SQL Server Query Execution Plans in SQL Server Management Studio article on MSSQLTips.com
Last Update: 8/15/2011
