[ Team LiB ] Recipe 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 | Team LiB Recipe 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 Namespaces variables and constants using System using using using using . . . StringBuilder sb new StringBuilder Open a new connection. SqlConnection conn new SqlConnection Sql_ConnectString Create and execute the command to retrieve the plan. SqlCommand cmd new SqlCommand SET SHOWPLAN_TEXT ON conn Create the command to get the plan for. SELECT FROM Customers WHERE Country USA ORDER BY CompanyName Retrieve the plan into DataReader. SqlDataReader dr Iterate over all result sets and all rows to get plan. do while 0 while Create and execute the command to retrieve query results. cmd new SqlCommand SET SHOWPLAN_TEXT OFF conn Discussion The SQL SET statement alters current session handling of specific information. Table 104 describes the categories of SET statements. Table 10-4. SET statement categories Category Description Date and Time Alters current session settings for handling of date and time data Locking Alters current session settings for handling SQL Server locking Miscellaneous Alters current session settings for miscellaneous SQL Server functionality Query Execution Alters current session settings for query execution and processing SQL-92 Settings Alters current session settings for using .
