Way to display query execution plan(Text) from c#

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 = 1
Union ALL
Select rn +1 from CTE where rn < 100 )
Select * from cte
GO
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 = 1
Union ALL
Select rn +1 from CTE where rn < 100 )
Select * from cte
GO

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 ON
GO
Create Procedure TestProcedure AS
GO
With CTE AS(
Select rn = 1
Union ALL
Select rn +1 from CTE where rn < 100 )
Select * from cte
GO
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.