Recipe 10.9 Getting a SQL Server Query Plan

Problem

You need to retrieve information about how query statements are executed by the SQL Server.

Solution

Use the SET SHOWPLAN_TEXT statement.

The sample code executes the SET SHOWPLAN_TEXT statement, using the ExecuteNonQuery( ) method of the Command object, to retrieve how query statements are executed by the SQL Server.

The C# code is shown in Example 10-9.

Example 10-9. File: ShowPlanForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;

//  . . . 

StringBuilder sb = new StringBuilder( );

// Open a new connection.
SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);

// Create and execute the command to retrieve the plan.
SqlCommand cmd = new SqlCommand("SET SHOWPLAN_TEXT ON", conn);
conn.Open( );
cmd.ExecuteNonQuery( );

// Create the command to get the plan for.
cmd.CommandText = "SELECT * FROM Customers WHERE Country='USA' " +
    "ORDER BY CompanyName";

// Retrieve the plan into DataReader.
SqlDataReader dr = cmd.ExecuteReader( );

// Iterate over all result sets and all rows to get plan.
do
{
    while (dr.Read( ))
        sb.Append(dr.GetString(0) + Environment.NewLine);
    sb.Append(Environment.NewLine);
} while(dr.NextResult( ));
dr.Close( );

// Create and execute the command to retrieve query results.
cmd = new SqlCommand("SET SHOWPLAN_TEXT OFF", conn);
cmd.ExecuteNonQuery( );

conn.Close( );

resultTextBox.Text = sb.ToString( );

Discussion

The SQL SET statement alters current session handling of specific information. Table 10-4 describes the categories of SET statements.

Table 10-4. SET statement categories

Category	Description
Date and Time	Alters current session settings for handling of date and time data
Locking	Alters current session settings for handling SQL Server locking
Miscellaneous	Alters current session settings for miscellaneous SQL Server functionality
Query Execution	Alters current session settings for query execution and processing
SQL-92 Settings	Alters current session settings for using SQL-92 default settings
Statistics	Alters current session settings for displaying statistics
Transactions	Alters current session settings for handling SQL Server Transactions

When SHOWPLAN_TEXT (from the Query Execution category) is ON, SQL Server returns a result set containing detailed information about how the SQL statements are going to be executed rather than actually executing the statements. Two result sets are returned for each statement, both containing a single column StmtText. The first result set contains the SQL statement while the second contains rows detailing the plan. For batch SQL statements, the result sets alternate between statement and plan for each statement in the batch.

SHOWPLAN_TEXT does not need to be explicitly set to OFF. It only affects the command issued subsequent to the statement in which it is SET ON, not all of the commands executed while the connection object is open.

SHOWPLAN_ALL returns more information about the plan than just the StmtText column but is turned on and off in the same way.

For more information about the SET statement, SHOWPLAN_TEXT, or SHOWPLAN_ALL, see the topic "SET" in Microsoft SQL Server Books Online.