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#.)
Asked
Active
Viewed 1,826 times
3
-
Why would you want to do that in C#? – Rahul Jul 23 '16 at 06:57
-
Mostly for performance testing. I want to setup my code so that the integration tests capture the estimated cost and flags any test that exceeds its budget. I might capture missing indexes as well. – Jonathan Allen Jul 23 '16 at 07:00
1 Answers
4
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.
-
1Looks like `SET SHOWPLAN_XML` is the one to use if you want details and `SET SHOWPLAN_ALL` if you just want a summary. – Jonathan Allen Jul 23 '16 at 07:14