3

SQL Server 2016, Enterprise Edition in Windows

Is there anyway to display the execution plan of a stored procedure created in C# with Visual Studio Data Tools? This is a question that someone with Visual Studio experience probably knows the answer to. My searches on Google haven't turned up anything useful.

Here is the stored procedure in c#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    static public void Addition (SqlInt32 a1, SqlInt32 a2, out SqlInt32 sum)
    {
        sum = a1 + a2;
    }


}

Here is the execution in SSMS 2017 v17.3

USE CLR1;

declare @sum int
exec dbo.Addition 3, 4, @sum output
select @sum

I have also been able to find the execution plan in cache. Even though there is a plan_handle available in dm_exec_procedure_stats, I can't seem to use a cross apply to [dm_exec_query_plan] to view it.

6
  • 2
    You might have to capture it via trace (profiler or XE). executionplan.blogspot.com/2012/02/… – SqlWorldWide Oct 26 '17 at 12:02
  • I second this. Perhaps you can profile it to get the actual SQL being executed. At which point you could run manually and view the estimated/actual query plans. – pim Oct 26 '17 at 12:53
  • What kind of execution plan are you expecting to see for this? It is just EXEC-ing a black box. – Martin Smith Oct 27 '17 at 9:50
  • @MartinSmith, Actually just discovering this for the first time so not getting back an execution plan was unexpected. I simply wondered to myself, if you inherit one of these stored procedures, how do you troubleshoot performance issues without the plan. – Craig Efrein Oct 27 '17 at 15:19
  • Thank you @SqlWorldWide, I will take a look at that link. – Craig Efrein Oct 27 '17 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.