score:4

Accepted answer

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

score:2

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.

score: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.

score:6

LINQ/.NET runs SQL queries by sending them as sp_executesql calls to SQL server. However, Database Engine Tuning Advisor can't parse such, by SQL Server Profiler, captured statements, so the result of the tuning session will be poor.

This is how I 'unbox' the sp_executesql statements before feeding them to Database Engine Tuning Advisor so it can analyze them correctly.

  1. After you've run (or opened an existing) tuning session in SQL Server Profiler, use File>Export>Extract SQL Server Events>Extract Transact-SQL Events... to save a .SQL file containing all SQL statements.
  2. Open the .SQL file (in e.g. Notepad++ 6 - http://notepad-plus-plus.org/) and run the following Find & Replacement Regular Expression to unbox all sp_executesql statements to plain TSQL. Find: ^EXEC[\s]+SP_EXECUTESQL[\s]+[N]*'((''|[^'])*)'[\s]*,[\s]*[N]*'((''|[^'])*)'[\s]*,[\s]*([^\n]+)$ Replace: BEGIN\nDECLARE \3\nSELECT \5\n\1\nEND
  3. Next, use the 'unboxed' .SQL filed that we've just constructed in Database Engine Tuning Advisor to find out what indexes you neeed to add.

Happy tuning! Please note that I've selected Notepad++ 6 (or above) since it has a pretty good Regular Expression implementation, other texteditors may fail at running the expression above.


Related Articles