Can sp_executesql be configured / used by default?

Question

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?

Answer 1

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:

• https://blogs.technet.microsoft.com/mdegre/2011/11/06/what-is-parameter-sniffing/
• http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql
• http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different
• http://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/
• https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql

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

Answer 2

If this is a .NET application, then it is very likely a result of SqlCommand.ExecuteReader() being called. According to the main SqlCommand class page, in the grid of method descriptions in the "Remarks" section, under ExecuteReader it says:

Executes commands that return rows. For increased performance, ExecuteReader invokes commands using the Transact-SQL sp_executesql system stored procedure. Therefore, ExecuteReader might not have the effect that you want if used to execute commands such as Transact-SQL SET statements.

I do not have time now to test this to confirm their description, but it should be easy enough to create a simple console app that does a very simple call, passing in some query text, and including a parameter that is supplied with a SqlParameter. My guess is that ExecuteNonQuery and ExecuteScalar also use sp_executesql since they also allow for passing in parameters, so why would there be a different path for how those are executed?