What causes sp_procedure_params_100_managed to be called when a stored procedure with parameters is executed in SQL2012

_{Tuesday, October 21, 2014 5:09 PM | 1 vote}

Hi,

I have been using profiler trace to investigate how our stored procedures are being executed as we try and use accounts from a different domain. Whenever a stored procedure is called that has parameters the sp_procedure_params_100_managed is called by the .Net SqlClient Data Provider. Is this expected and is this extra overhead?

Thanks

Chris

_{Tuesday, October 21, 2014 8:24 PM ✅Answered}

This is used by .Net Entity Framework to get stored proc parameters.

_{Tuesday, October 21, 2014 8:27 PM}

Thanks for the info Tom.

Is there much overhead in doing this on a fairly busy OLTP server? If there is can this be overcome somehow?

Chris

_{Tuesday, October 21, 2014 10:05 PM}

I can't say how the overhead might be. But you could easily aggregate the resource consumption for your trace.

Beside the overhead on the server, this is one extra network trip, which if nothing else is going to hurt, if the database is moved to the could.

As for stop it from happening, you will need to talk with people who know Entity Framework. I don't really see why EF would have to call this procedure everytime, but then again EF is something I stay away from.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Wednesday, October 22, 2014 8:10 PM}

Thanks Erland.

Not good hearing about the extra network trip each time. Maybe I should ask this question in a Net Entity Framework group.

Chris

_{Thursday, October 23, 2014 12:19 AM}

Good day ChrisAVWood

The sp_procedure_params_100_managed SP is just a simple SP which bring back all the information about specific SP. for example:

use AdventureWorks2012
GO
exec sys.sp_procedure_params_100_managed
    @procedure_name = 'uspGetBillOfMaterials'
    ,@group_number = 1
    ,@procedure_schema = 'dbo'
    ,@parameter_name = null
GO

The .Net SqlCommandBuilder.DeriveParameters method use this stored proc and it is not just related to Entity Framework. It is used by many lazy developers, When they have very big amount of parameters in SP and Manually creating parameters in code might take time, then some people think that this is not an option (they are wrong!). SqlCommandBuilder.DeriveParameters is used by those developer to make their life easier. but in some cases this is good solution. This is basically used when you need the use of dynamic SP, or in other words to programmatically retrieve a database's stored procedures and determine their parameters. For example if you develop a generic element that work with unknown SP, and you need to get the information about the SP. ORM use this is well in some cases.

Here is a simple example of using it on the same SP as the code above, and it has nothing to do with EF in this case (this is a simple console application):

using System;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection(@"server=Use_Your_Server_Name;database=AdventureWorks2012;integrated security=sspi;");
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "uspGetBillOfMaterials";
            SqlCommandBuilder.DeriveParameters(cmd); // This is what execute the sp_procedure_params_100_managed
            foreach (SqlParameter p in cmd.Parameters)
            {
                Console.WriteLine(String.Format("name: {0}, type: {1}, ParameterName:{2}", p.TypeName, p.DbType, p.ParameterName));
            }
            Console.ReadLine(); 
        }
    }
}

This is the output:

name: , type: Int32, ParameterName:@RETURN_VALUE
name: , type: Int32, ParameterName:@StartProductID
name: , type: DateTime, ParameterName:@CheckDate

I hope this close the issue, but if you have any more questions, feel free to ask :-)

* Most important! I highly recommend you NOT TO USE ANY ORM in oltp system. No ORM can replace a DBA. Orm used to make developers life easier and not improve performance. I have to say that EF is the best ORM that I checked for SQL Servers, but yet... it will not build the best queries in all cases.

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]

_{Friday, October 24, 2014 2:37 PM}

Thank you Ronen for the detailed information. I am a DBA and not a Developer so I would need to find out how the stored procedure is being called and then review your information.

My concern was as Erland mentioned the extra network trip that must make the original stored procedure use extra resources.

Chris