ADO.NET 3.5 Cookbook, 2nd Edition

11.6. Retrieving 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 in Program.cs in the project RetrieveSqlServerQueryPlan is shown in Example 11-6.

Example 11-6. File: Program.cs for RetrieveSqlServerQueryPlan solution

using System; using System.Data; using System.Data.SqlClient; namespace RetrieveSqlServerQueryPlan { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT * FROM Person.Contact WHERE " + "LastName LIKE 'A%' ORDER BY LastName, FirstName"; SqlConnection connection = new SqlConnection(sqlConnectString); // Create and execute the command to retrieve the plan. SqlCommand command = new SqlCommand("SET SHOWPLAN_TEXT ON", connection); connection.Open( ); command.ExecuteNonQuery( ); // Create the command to get the plan for. command.CommandText = sqlSelect; // Retrieve the plan into DataReader. SqlDataReader dr = command.ExecuteReader( ); // Iterate over all result sets and all rows to get plan. do { while (dr.Read( )) Console.WriteLine(dr.GetString(0)); Console.WriteLine( ); } while (dr.NextResult( )); dr.Close( ); // Create and execute the ...