Getting a SQL Server Query Plan

[ 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
Nội dung trích xuất từ tài liệu:
Getting a SQL Server Query Plan[ Team LiB ]Recipe 10.9 Getting a SQL Server Query PlanProblemYou need to retrieve information about how query statements are executed by the SQLServer.SolutionUse the SET SHOWPLAN_TEXT statement.The sample code executes the SET SHOWPLAN_TEXT statement, using theExecuteNonQuery( ) method of the Command object, to retrieve how query statementsare executed by the SQL Server.The C# code is shown in Example 10-9.Example 10-9. File: ShowPlanForm.cs// Namespaces, variables, and constantsusing 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 = sb.ToString( );DiscussionThe SQL SET statement alters current session handling of specific information. Table 10-4 describes the categories of SET statements. Table 10-4. SET statement categories Category DescriptionDate and Time Alters current session settings for handling of date and time dataLocking Alters current session settings for handling SQL Server locking Alters current session settings for miscellaneous SQL ServerMiscellaneous functionalityQuery Alters current session settings for query execution and processingExecutionSQL-92 Settings Alters current session settings for using SQL-92 default settingsStatistics Alters current session settings for displaying statisticsTransactions Alters current session settings for handling SQL Server TransactionsWhen SHOWPLAN_TEXT (from the Query Execution category) is ON, SQL Serverreturns a result set containing detailed information about how the SQL statements aregoing to be executed rather than actually executing the statements. Two result sets arereturned for each statement, both containing a single column StmtText. The first result setcontains the SQL statement while the second contains rows detailing the plan. For batchSQL statements, the result sets alternate between statement and plan for each statement inthe batch.SHOWPLAN_TEXT does not need to be explicitly set to OFF. It only affects thecommand issued subsequent to the statement in which it is SET ON, not all of thecommands executed while the connection object is open.SHOWPLAN_ALL returns more information about the plan than just the StmtTextcolumn but is turned on and off in the same way.For more information about the SET statement, SHOWPLAN_TEXT, orSHOWPLAN_ALL, see the topic SET in Microsoft SQL Server Books Online.[ Team LiB ]