SQL Server – Configure sp_executesql as Default

dynamic-sqlsql serversql-server-2008

I am looking at an application that uses highly dynamic sql queries against SQL Server. Looking at the queries that are constructed in very weird and complicated ways, but that's a different story, I tell it to give a good reason for me being not able (too stupid) to find things out myself… I can't see any code where the queries are wrapped with sp_executesql.

But when I trace, I can see a lot of queries coming in wrapped with sp_executesql. The whole application solution does not even contain the command sp_executesql at all.

So I wondered if there is kind of a configuration I do not know yet that forces the software to wrap queries with sp_executesql by default?

What could cause this behaviour?

Best Answer

The reason the SQL statements are getting wrapped with sp_executesql is the setting of the SqlCommand.Commandtype property and passing any Parameters to the command.

SqlCommand cmd = new SqlCommand("proc1", con);
cmd.CommandType = CommandType.StoredProcedure;                
cmd.Parameters.AddWithValue("@param1", 1);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

The code above ends with this T-SQL:

exec proc1 @param1=1

SqlCommand cmd = new SqlCommand("proc1", con);
cmd.CommandType = CommandType.Text;                
cmd.Parameters.AddWithValue("@param1", 1);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

This code ends with execution of the following T-SQL:

exec sp_executesql N'proc1',N'@param1 int',@param1=1

Addition 23.12.15: Using a CommandType.Text command, the results are similar: As soon a parameter is added to the command object, .NET will wrap the whole query into sp_executesql and pass the parameters to it.

Addition: After diving deeper into sp_executesql, parameter sniffing and plan caching this behavior of the .NET classes totally makes sense in order to avoid high frequent query compilation and number of plans. So it's basically designed to ensure a better SQL Server performance in general while it at the same time could lead to poor performance of some queries (parameter sniffing issue) that are used with different parameter values than the initial created query plan.

See:

The above sample were created using .NET Framework 4.5 and SQL Server 2008 Developer Edition.

Related Question