We may sometime need to display the execution plan . I got one such requirement recently and thought of sharing the same here.
I will take two examples into consideration for demonstrating the application . The first with a normal query and then the same with a stored procedure .
Introduction
We may sometime need to display the execution plan . I got one such requirement recently and thought of sharing the same here.
Demonstration
I will take two examples into consideration for demonstrating the application . The first with a normal query and then the same with a stored procedure
Case 1: Display execution plan with a normal query
Well it is a simple recursive CTE that will generate a number table
SET
SHOWPLAN_ALL ON;GO
With
CTE AS(Select
rn = 1Union
ALLSelect
rn +1 from CTE where rn < 100 )Select
* from cteGO
SET
SHOWPLAN_ALL OFF;
/* Result
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
With CTE AS( Select rn = 1 Union ALL Select rn +1 from CTE where rn < 100 ) Select * from cte 1 1 0 NULL NULL 1 NULL 2 NULL NULL NULL 6.254E-06 NULL NULL SELECT 0 NULL
|--Index Spool(WITH STACK) 1 2 1 Index Spool Lazy Spool WITH STACK NULL 2 0 1E-08 11 6.254E-06 [Expr1007], [Recr1003] NULL PLAN_ROW 0 1
|--Concatenation 1 3 2 Concatenation Concatenation NULL [Expr1007] = ([Expr1004], [Expr1006]), [Recr1003] = ([Expr1000], [Expr1002]) 2 0 2E-09 11 4.664E-06 [Expr1007], [Recr1003] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE:([Expr1004]=(0))) 1 4 3 Compute Scalar Compute Scalar DEFINE:([Expr1004]=(0)) [Expr1004]=(0) 1 0 2E-08 11 2E-08 [Expr1004], [Expr1000] NULL PLAN_ROW 0 3
| |--Constant Scan(VALUES:(((1)))) 1 5 4 Constant Scan Constant Scan VALUES:(((1))) NULL 1 0 1.157E-06 11 1.157E-06 [Expr1000] NULL PLAN_ROW 0 1
|--Assert(WHERE:(CASE WHEN [Expr1006]>(100) THEN (0) ELSE NULL END)) 1 10 3 Assert Assert WHERE:(CASE WHEN [Expr1006]>(100) THEN (0) ELSE NULL END) NULL 2 0 1.68E-07 11 3.505E-06 [Expr1006], [Expr1002] NULL PLAN_ROW 0 3
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1006], [Recr1001])) 1 11 10 Nested Loops Inner Join OUTER REFERENCES:([Expr1006], [Recr1001]) NULL 2 0 1.68E-07 11 3.505E-06 [Expr1006], [Expr1002] NULL PLAN_ROW 0 3
|--Compute Scalar(DEFINE:([Expr1006]=[Expr1005]+(1))) 1 12 11 Compute Scalar Compute Scalar DEFINE:([Expr1006]=[Expr1005]+(1)) [Expr1006]=[Expr1005]+(1) 1 0 2E-08 11 2E-08 [Expr1006], [Recr1001] NULL PLAN_ROW 0 3
| |--Table Spool(WITH STACK) 1 13 12 Table Spool Lazy Spool WITH STACK NULL 1 0 2E-08 11 2E-08 [Expr1005], [Recr1001] NULL PLAN_ROW 0 3
|--Compute Scalar(DEFINE:([Expr1002]=[Recr1001]+(1))) 1 17 11 Compute Scalar Compute Scalar DEFINE:([Expr1002]=[Recr1001]+(1)) [Expr1002]=[Recr1001]+(1) 1 0 1E-07 11 3.317E-06 [Expr1002] NULL PLAN_ROW 0 2
|--Filter(WHERE:(STARTUP EXPR([Recr1001]<(100)))) 1 18 17 Filter Filter WHERE:(STARTUP EXPR([Recr1001]<(100))) NULL 1 0 4.8E-07 9 3.117E-06 NULL NULL PLAN_ROW 0 2
|--Constant Scan 1 19 18 Constant Scan Constant Scan NULL NULL 1 0 1.157E-06 9 2.157E-06 NULL NULL PLAN_ROW 0 2
*/
Looking into the simple query we can find that we are using GO command which is the batch separator.
But from the ADO.net’s SqlCommand object it will not be able to parse the same.
GO is not a T-SQL command.SSMS uses it to indicate that a block of transactions should be executed
Henceforth, if we write as
try{
using (SqlConnection conn = new SqlConnection(connectionString)){
// Open the SqlConnection. conn.Open();
string cmdText = "SET SHOWPLAN_ALL ON;"+
Environment.NewLine+
"GO" + Environment.NewLine+
"Select * from TestTable"+
Environment.NewLine+
"GO"+
Environment.NewLine+
"SET SHOWPLAN_ALL OFF;";using (SqlCommand cmd = new SqlCommand(cmdText, conn)){
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex){
throw ex;}
We will receive an error
Incorrect syntax near 'GO'.
The SET SHOWPLAN statements must be the only statements in the batch.
Then how can we go ahead?
In the following code we will see that
using
System;using
System.Data;using
System.Data.SqlClient;using
System.Linq;namespace
ConsoleApplication1{
class Program{
static void Main(string[] args){
SqlDataAdapter Adpt = new SqlDataAdapter();DataTable dt = new DataTable();string executionPlanString = string.Empty;string connectionString = "Data Source=[ServerName];Initial Catalog=[DataBase Name];Integrated Security=True;User Id=[User Name];Password=[Password]";try{
using (SqlConnection conn = new SqlConnection(connectionString)){
// Open the SqlConnection. conn.Open();
using (SqlCommand cmd = new SqlCommand("SET SHOWPLAN_ALL ON", conn)){
cmd.CommandType =
CommandType.Text;cmd.ExecuteNonQuery();
cmd.CommandText = GetCommandText();
cmd.CommandType =
CommandType.Text;cmd.ExecuteNonQuery();
using (SqlDataReader reader = cmd.ExecuteReader()){
dt.Load(reader);
}
cmd.CommandText =
"SET SHOWPLAN_ALL OFF";cmd.CommandType =
CommandType.Text;}
Enumerable.Range(1, dt.Rows.Count - 1)
.ToList()
.ForEach(i => executionPlanString +=
dt.Rows[i][
"StmtText"].ToString() +Environment.NewLine);
Console.WriteLine(executionPlanString);Console.ReadKey();}
}
catch (Exception ex){
throw ex;}
}
private static string GetCommandText(){
/*With CTE AS(
Select rn = 1
Union ALL
Select rn +1 from CTE where rn < 100 )
Select * from cte
*/
System.Text.
StringBuilder sb = new System.Text.StringBuilder();sb.AppendLine(
"With CTE AS(");sb.AppendLine(
"Select rn = 1");sb.AppendLine(
"Union ALL");sb.AppendLine(
"Select rn +1 from CTE where rn < 100 )");sb.AppendLine(
"Select * from cte");return sb.ToString();}
}
}
/*
Output
------
|--Index Spool(WITH STACK)
|--Concatenation
|--Compute Scalar(DEFINE:([Expr1004]=(0)))
| |--Constant Scan(VALUES:(((1))))
|--Assert(WHERE:(CASE WHEN [Expr1006]>(100) THEN (0) ELSE NULL END))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1006], [Recr1001]))
|--Compute Scalar(DEFINE:([Expr1006]=[Expr1005]+(1)))
| |--Table Spool(WITH STACK)
|--Compute Scalar(DEFINE:([Expr1002]=[Recr1001]+(1)))
|--Filter(WHERE:(STARTUP EXPR([Recr1001]<(100))))
|--Constant Scan
*/
Explanation
Consider the line
using
(SqlCommand cmd = new SqlCommand("SET SHOWPLAN_ALL ON", conn)){
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
.............................
..............................
..............................
..............................
}
This is the first batch which is equivalent to
SET
SHOWPLAN_TEXT ON;GO
Now consider the next code snippet
cmd.CommandText = GetCommandText();
cmd.CommandType =
CommandType.Text;cmd.ExecuteNonQuery();
This is the second batch which is equivalent to
With
CTE AS(Select
rn = 1Union
ALLSelect
rn +1 from CTE where rn < 100 )Select
* from cteGO
Then we are loading the value from the datareader to the datatable
using
(SqlDataReader reader = cmd.ExecuteReader()){
dt.Load(reader);
}
After that, we are executing the next statement
cmd.CommandText =
"SET SHOWPLAN_ALL OFF";cmd.CommandType = CommandType.Text;
Which is equivalent to
SET
SHOWPLAN_TEXT OFF;
And finally we are displaying the output to the console
Enumerable
.Range(1, dt.Rows.Count - 1).ToList().ForEach(i => executionPlanString += dt.Rows[i]["StmtText"].ToString() + Environment.NewLine);Console
.WriteLine(executionPlanString);
Case 2: Display execution plan with a stored procedure
This time the plan will be generated for
SET
SHOWPLAN_ALL ONGO
Create
Procedure TestProcedure ASGO
With
CTE AS(Select
rn = 1Union
ALLSelect
rn +1 from CTE where rn < 100 )Select
* from cteGO
SET
SHOWPLAN_ALL OFF
The below code will do that
using
System;using
System.Data;using
System.Data.SqlClient;using
System.Linq;namespace
ConsoleApplication1{
class Program{
static void Main(string[] args){
SqlDataAdapter Adpt = new SqlDataAdapter();DataTable dt = new DataTable();string executionPlanString = string.Empty;string connectionString = "Data Source=[ServerName];Initial Catalog=[DataBase Name];Integrated Security=True;User Id=[User Name];Password=[Password]";try{
using (SqlConnection conn = new SqlConnection(connectionString)){
// Open the SqlConnection. conn.Open();
using (SqlCommand cmd = new SqlCommand("SET SHOWPLAN_ALL ON", conn)){
cmd.CommandType =
CommandType.Text;cmd.ExecuteNonQuery();
cmd.CommandText =
"Create Procedure TestProcedure AS";cmd.CommandType =
CommandType.Text;cmd.CommandText = GetCommandText();
cmd.CommandType =
CommandType.Text;cmd.ExecuteNonQuery();
using (SqlDataReader reader = cmd.ExecuteReader()){
dt.Load(reader);
}
cmd.CommandText =
"SET SHOWPLAN_ALL OFF";cmd.CommandType =
CommandType.Text;}
Enumerable.Range(1, dt.Rows.Count - 1)
.ToList()
.ForEach(i => executionPlanString += dt.Rows[i][
"StmtText"].ToString() + Environment.NewLine);Console.WriteLine(executionPlanString);Console.ReadKey();}
}
catch (Exception ex){
throw ex;}
}
private static string GetCommandText(){
System.Text.
StringBuilder sb = new System.Text.StringBuilder();sb.AppendLine(
"With CTE AS(");sb.AppendLine(
"Select rn = 1");sb.AppendLine(
"Union ALL");sb.AppendLine(
"Select rn +1 from CTE where rn < 100 )");sb.AppendLine(
"Select * from cte");return sb.ToString();}
}
}
As we can find out that, we have added one more line as
cmd.CommandText =
"Create Procedure TestProcedure AS";cmd.CommandType =
CommandType.Text;
since it is the batch seperator.
Conclusion
So in this article we have seen as how to display query execution plan using C#.Hope this helps.