LINQ-to-Entities get rid of sp_executesql

Question

I am trying to optimize my database now using Database Engine Tuning Advisor, and the problem I am facing is that my SQL Profiler trace shows tons of queries performed using sp_executesql - and the advisor fails to process those. It seems like these queries come from LINQ-to-Entities I am using, so just curious if there is any way to make LINQ-to-Entities invoke statements directly.

Answer 1

The usage of sp_executesql is an ADO.NET effect, not something specific to the Entity Framework. As far as I am aware, there's no way to instruct it not to use that stored procedure.

Answer 2

Agree with Adam. Use of sp_executesql in entity framework/ADO.net is intentional. The queries are then executed similar to parameterized stored proc and SQL optimizer can reuse the query plan.

If you are looking to tune your DB, you should consider these queries as well. I suggest you,take a backup of your DB, capture the queries using replay trace template (in your SQL profiler) , restore your DB, run your tuning advisor settings its workload to this trace

Answer 3

That is just an artifact of how RPC calls are shown in profiler. There are two main types of client requests: Language (type 0x01) and RPC (type 0x03), as documented by the Free TDS protocol documentation. When the call is a SQL batch with parameters, the RPC call will be of type 0x03 with length 0x0A which is a shortcut for sp_executesql.

So you see, what really happens is that when a client, any client, submits a batch that contains parameters, it will appear as if sp_executesql is being called. This is true with ODBC, with OleDB, SqlClient, Sql Native Client, as I said, any client. So is not Entity Framework, nor ADO.Net that actually calls sp_executesql (in fact, the procedure is not even really called, although the requests executes as if it was called. Is complicated...). It is an artifact of the protocol that happens anytime you add an @parameter to your request.