LINQ-to-Entities get rid of sp_executesql

Question

I am trying to optimize my database using the Database Engine Tuning Advisor , and the problem that I encountered is that my SQL Profiler trace shows tons of queries executed using sp_executesqland the adviser cannot process them. It seems that these queries come from the LINQ-to-Entities that I use, so it's just curious if there is a way to get LINQ-to-Entities to invoke statements directly.

Answer 1

Agree with Adam. Using sp_executesqlentity framework / ADO.net is intentional. The queries are then executed in the same way as the parameterized stored proc and the SQL optimizer, which can reuse the query plan.

If you want to customize your database, you should also consider these queries. I suggest you make a backup of your database, capture queries using the trace trace template (in your SQL profiler), restore your database, launch your configuration advisor, configure your workload for this trace

Answer 2

LINQ/.NET SQL-, sp_executesql SQL-. , , SQL Server, , .

"" sp_executesql, , .

• ( ) SQL Server "" > "" > " SQL Server" > " Transact-SQL...", a. SQL , SQL.
• .SQL (, Notepad ++ 6 - http://notepad-plus-plus.org/) Find " " sp_executesql TSQL. : ^EXEC[\s]+SP_EXECUTESQL[\s]+[N]*'((''|[^'])*)'[\s]*,[\s]*[N]*'((''|[^'])*)'[\s]*,[\s]*([^\n]+)$ : BEGIN\nDECLARE \3\nSELECT \5\n\1\nEND
• "unboxed".SQL, Advisor Engine Tuning Advisor, , .

! , Notepad ++ 6 ( ), , .

Answer 3

, RPC . : ( 0x01) RPC ( 0x03), TDS. SQL , RPC 0x03 0x0A, sp_executesql.

, , , , , , , , sp_executesql. ODBC, OleDB, SqlClient, Sql Native Client, , . Entity Framework, ADO.Net, sp_executesql ( , , , . , @parameter .

Answer 4

sp_executesql - ADO.NET, - Entity Framework. , .