O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required