ADO.NET Cookbook

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 ...