2011-03-15Getting an SQL Server Execution Plan

SQL Server has multiple ways to get execution plans. The two most important methods are:

Graphically

The graphical explain plan is generated with one of the two buttons shown in Figure A.2.

Figure A.2. SSMS Execution Plan Generation

The left button explains the highlighted statement directly. The right will capture the plan the next time a SQL statement is executed.

In both cases, the graphical representation of the execution plan appears in the Execution Plan tab of the Results pane.

Figure A.3. SSMS Graphical Execution Plan

The graphical representation is easy to read with a little bit practice. It is, however, only showing the most fundamental information: the operations and the table or index they act upon.

The Management Studio shows more information when the mouse is moved over an operation (mouseover/hover). That makes it hard to share an execution plan with all its details.

Tabular

The tabular representation of an SQL Server execution plan is collected by profiling statement execution. The following command enables it:

SET STATISTICS PROFILE ON

Once enabled, each executed statement produces an extra result set. E.g., select statements produce two result sets—first the result of the statement; then the execution plan.

The tabular execution plan is hardly usable in SQL Server Management Studio because the StmtText is just too wide:

Figure A.4. Tabular Explain Plan in SSMS

The benefit of the that representation is that it can be copied without loosing any information. That is very handy if you like to post an SQL Server execution plan to a forum, or the like. In that case, it’s often enough to copy the StmtText column and format it a little bit:

select COUNT(*) from employees;
  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(...))
       |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
            |--Index Scan(OBJECT:([employees].[employees_pk]))

Finally, you can disable the profiling again:

SET STATISTICS PROFILE OFF