Graphical Execution Plans
Graphical execution plans are accessed through the query window inside Management Studio in SQL Server 2005/2008 or through Query Analyzer in SQL Server 2000. To a large degree, the functionality of graphical plans is the same in SQL Server 2000 as it is in SQL Server 2008. However, there are some fundamental differences. For this reason, the different versions will be discussed individually in order to be more specific to the differences between the versions of SQL Server. Differences between SQL Server 2005 and 2008 will be highlighted within the section.
In all versions there are two fundamental types of execution plan, the estimated execution plan and the actual plan. The estimated plan does not require the query to be run while the actual plan is an output from the query engine showing the plan used to execute the query. Most of the time these two plans are identical, but there are circumstances where they will be different.
All graphical plans are read from the right to the left and from the top to the bottom. That's important to know so that you can understand other concepts such as understanding how a hash join works. Each icon represents an operation. Some operations are the same between the Estimated and Actual plans and some vary between the two plans. Each operator is connected by an arrow that represents a data feed. The data feed is the output from one operator and the input for the next. The thickness of the data feed varies per the amount of the data it represents. Thinner arrows represent fewer rows and thicker arrows represent more rows.
Operators represent various objects and actions within the execution plan. A full listing of operators is available in the Books Online.
SQL Server 2005/2008
Estimated Execution Plan
There are several ways to generate an estimated execution plan:
• Select the "Display Estimated Execution Plan" from the tool bar
• Right click within the query window and select "Display Estimated Execution Plan"
• Select the Query menu and then the "Display Estimated Execution Plan" menu choice
• Press CTL-L
When any of these actions is performed, an estimated, graphical, execution plan is created for the query in the query window. The query is not executed. That is an important point. The query is merely run against the query optimizer within the SQL Server system and the output from the optimizer is displayed as a graphical execution plan. If objects that don't exist, such as temporary tables, are part of the query, the estimated plan will fail.
Actual Execution Plan
An actual execution plan requires the query to be executed. To enable the generation of the actual execution plan:
• Select the "Include Actual Execution Plan" button from the tool bar
• Right click within the query window and select "Include Actual Execution Plan"
• Select the Query menu and then the "Include Actual Execution Plan" menu choice
• Press CTL-M
After the query executes, the actual execution plan will be available in a different tab in the results pane of the query window.
Graphical execution plans are accessed through the query window inside Management Studio in SQL Server 2005/2008 or through Query Analyzer in SQL Server 2000. To a large degree, the functionality of graphical plans is the same in SQL Server 2000 as it is in SQL Server 2008. However, there are some fundamental differences. For this reason, the different versions will be discussed individually in order to be more specific to the differences between the versions of SQL Server. Differences between SQL Server 2005 and 2008 will be highlighted within the section.
In all versions there are two fundamental types of execution plan, the estimated execution plan and the actual plan. The estimated plan does not require the query to be run while the actual plan is an output from the query engine showing the plan used to execute the query. Most of the time these two plans are identical, but there are circumstances where they will be different.
All graphical plans are read from the right to the left and from the top to the bottom. That's important to know so that you can understand other concepts such as understanding how a hash join works. Each icon represents an operation. Some operations are the same between the Estimated and Actual plans and some vary between the two plans. Each operator is connected by an arrow that represents a data feed. The data feed is the output from one operator and the input for the next. The thickness of the data feed varies per the amount of the data it represents. Thinner arrows represent fewer rows and thicker arrows represent more rows.
Operators represent various objects and actions within the execution plan. A full listing of operators is available in the Books Online.
SQL Server 2005/2008
Estimated Execution Plan
There are several ways to generate an estimated execution plan:
• Select the "Display Estimated Execution Plan" from the tool bar
• Right click within the query window and select "Display Estimated Execution Plan"
• Select the Query menu and then the "Display Estimated Execution Plan" menu choice
• Press CTL-L
When any of these actions is performed, an estimated, graphical, execution plan is created for the query in the query window. The query is not executed. That is an important point. The query is merely run against the query optimizer within the SQL Server system and the output from the optimizer is displayed as a graphical execution plan. If objects that don't exist, such as temporary tables, are part of the query, the estimated plan will fail.
Actual Execution Plan
An actual execution plan requires the query to be executed. To enable the generation of the actual execution plan:
• Select the "Include Actual Execution Plan" button from the tool bar
• Right click within the query window and select "Include Actual Execution Plan"
• Select the Query menu and then the "Include Actual Execution Plan" menu choice
• Press CTL-M
After the query executes, the actual execution plan will be available in a different tab in the results pane of the query window.
No comments:
Post a Comment