CODEWITHSUNDEEP
c#sql-server
Jonathan Allen
Jonathan Allen

Reputation: 70317

How do I get an estimated execution plan from SQL Server using C#?

I know how to get it using SSMS, but how would I get the execution plan using C# and ADO.NET? (Assume I know how to handle normal queries in C#.)

Upvotes: 3

Views: 2466

Answers (1)

Rahul
Rahul

Reputation: 77886

Looks like you can use the below query taken from Are there any way to programmatically execute a query with Include Actual Execution Plan and see whether any index suggestion or not

command.CommandText = "SET STATISTICS XML ON";

Also, you can use SHOWPLAN_ALL option and can set it in your stored procedure probably like SET SHOWPLAN_ALL ON (Though I have never tested that personally. So just a suggestion)

When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, information about all subsequent Transact-SQL statements are returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_ALL is ON, SQL Server returns an error message from a subsequent SELECT statement involving that same table, informing users that the specified table does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.

Upvotes: 4

Related Questions