X hits on this document





16 / 48



The color of the operator icon also has meaning. If the operator is red, this indicates a warning of some kind—for example, it could be telling you that the statistics on the table are missing. Yellow icons are used to represent cursor operations. Green icons represent language elements such as IF and WHILE. Blue icons are used for the remainder of the physical and logical operations.

Concluding this recipe, to stop the Query Editor from returning the Actual Execution Plan each time a query is executed, go to the Query menu and deselect Include Actual Execution Plan.

How It Works

In this recipe, I walked through how to view the estimated and actual query execution plans in a graphical format. This is by far the easiest way to visually understand what operations are taking place within your query. If a query is taking too long to execute, you can check the output for the higher cost query batches and within that query batch, the higher cost operators. Once you find a high percentage operator, you can hover your mouse pointer over it to see the ToolTip.

You don’t, however, have to use the graphical tools in order to view a query’s execution plan. SQL Server 2005 includes Transact-SQL commands which can also provide this information in result set form, as you’ll see in the next recipe.

Viewing Estimated Query Execution Plans Using Transact-SQL Commands

In SQL Server 2005, there are three commands that can be used to view detailed information about a query execution plan for a SQL statement or batch: SET SHOWPLAN_ALL, SET SHOWPLAN_TEXT, and SET SHOWPLAN_XML. The output of these commands helps you understand how SQL Server plans to process and execute your quer , identifying information such as table join types used and the indexes accessed. For example, using the output from these commands, you can see whether SQL Server is using a spe- cific index in a query, and if so, whether it is retrieving the data using an index seek (nonclustered index is used to retrieve selected rows for the operation) or index scan (all index rows are retrieved for the operation).

When enabled, the SET SHOWPLAN_ALL, SET SHOWPLAN_TEXT, and SET SHOWPLAN_XML commands provide you with the plan information without executing the query, allowing you to adjust the query or indexes on the referenced tables before actually executing it.

Each of these commands returns information in a different way. SET SHOWPLAN_ALL returns the estimated query plan in a tabular format, with multiple columns and rows. The output includes information such as the estimated IO or CPU of each operation, estimated rows involved in the operation, operation cost (relative to itself and variations of the query), and the physical and logical operators used.

  • Note Logical operators describe the conceptual operation SQL Server must perform in the query execution.

Physical operators are the actual implementation of that logical operation. For example a logical operation in a query, INNER JOIN, could be translated into the physical operation of a Nested Loop in the actual query execution.

The SET SHOWPLAN_TEXT command returns the data in a single column, with multiple rows for each operation. Introduced in SQL Server 2005, you can also return a query execution plan in XML format using the SET SHOWPLAN_XML command. The syntax for each of these commands is very similar:


Document info
Document views171
Page views171
Page last viewedMon Jan 23 14:52:56 UTC 2017