Tài liệu Getting a SQL Server Query Plan pdf

[ Team LiB ]

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' " +