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 ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.