Entity Framework sp_executesql Parameter Sniffing

You may ever find the Entity Framework does not use sp_executesql to execute in .Net 4.0. However, the sp_executesql works to use cached plans to get more performance. Then, what does it happen when Entity Framework does not use sp_executesql and how to fix it? Well, let’s find out about it and keep staying on this page.

Entity Framework sp_executesql Parameter Sniffing

To note, the use of sp_executesql is totally the only way to execute a parameterized dynamic SQL statement in SQL Server. Indeed, there are two major execution primitives in TDS batch and RPC. Here a batch request just contains a T-SQL string. It can be used whenever there is no need for parameters.

More info: How to Solve could not open /dev/vmmon: Broken Pipe Vmware Fusion on macOS Catalina

While an RPC request is to take a procedure name and a set of parameters. Furthermore, it can be used to execute a stored-procedure directly. None of them cover executing a parameterized SQL statement. To do that SQL Server has sp_executesql that is a system stored-procedure which takes a SQL string with parameter markers and a variable number of arguments for the parameters. Then, the issue of the RPC client requests to sp_executesql whenever it needs to execute a parameterized statement.

Here are such a query will not use sp_executesql:

var cust = from c in ctx.Customers

where c.CustomerID == “ALFKI”

select c;

Then, a query does use sp_executesql to pass a parameter:

string CustID = “ALFKI”;

var cust = from c in ctx.Customers

where c.CustomerID == CustID

select c;

You also have to know that some major performance problems with simple SQL queries are generated by the Entity Framework (4.2), running against SQL server 2008 R2. While, in some cases (but not all), Entity Framework uses the following syntax:

exec sp_executesql ‘DYNAMIC-SQL-QUERY-HERE’, @param1…

If you are in this situation, it simply executes the raw SQl with the provided parameters entered into the query. The main problem which happens to a lot of users is that the queries executed with the sp_executesql are ignoring all indexes on their target tables so it results in an extremely poor performing query.

After a bit research, it is like that the issue may be caused by parameter sniffing which append the option query hint as follow:

exec sp_executesql ‘DYNAMIC-SQL-QUERY-HERE OPTION(RECOMPILE)’, @param1…

Well, the indexes on the target tables are actually used and query executes fast. Here, some users may activate the trace flag used to disable parameter sniffing on the database instance. But, it did not influence a lot to have any effect whatsoever.

Then, the questions might will appears are as follow:

Is there anyway to append the OPTION(RECOMPILE) query hint to the SQL generated by Entity Framework?
Is there anyway to prevent Entity Framework from using exec sp_executesql, and instead simply run the raw SQL?
Is anyone else running into this problem? Any other hints/tips?

Then, they may mention that if they restart the SQL Server Service through the service management console once they enable trace flag 4136 with the script below!

DBCC TRACEON(4136,-1)

How to Fix Entity Framework of sp_executesql Parameter Sniffing

Well, there is a smart solution that you can take to Fix Entity Framework of sp_executesql Parameter Sniffing. Here, we recommend you to use this following query.

EXEC sp_configure ‘show advanced’, 1;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure ‘optimize for ad hoc’, 1;

RECONFIGURE WITH OVERRIDE

EXEC sp_configure ‘show advanced’, 0;

RECONFIGURE WITH OVERRIDE;

But, if this setting does not seem to solve your problem, then the only solution that you can do is to try the additional support of the trace flag. Those are generally reserved as a last resort. You can set the trace flag using the command line through SQL Server COnfiguration Manager as an opponent in a query window and use the global flag.

What is Parameter Sniffing?

When you are using SQL, you probably will find the term of parameter sniffing. However, this term is to identify a process when SQL Server creates an optimal plan for a stored procedure by using the parameters which are passed the first time when the stored procedure is executed.

In this case, the SQL server exercises to build the execution plan once. It uses it multiple times without recreating the execution plan more and more. Due to the execution of the procedure being so large, of course the compilation process of the stored procedure may take some valuable time. All exercises are made to save those repeated moments recompiling the stored procedure. Even though it works out well in many cases, there is often a bad impact of parameter sniffing for the stored procedure.

Is Parameter Sniffing bad or good?

However, this is one of the most popular questions received by SQL Server experts throughout the years whether parameter sniffing systems are bad or good for the users. However, there have been a ton of different solutions to work with the parameter sniffing issue in the SQL server.

Conclusion, a lot of SQL server experts who argue that the parameter sniffing is a good feature for reusing the execution plan and edge-case scenario surprisingly claim that there is a poor performance of this feature. While, the experts who argue that parameter sniffing is a bad feature evidently degrade the performance of the query for all subsequent runs until it gets recompiled.

Indeed, the debate about parameter sniffing system performance among the people who consider this feature as good or bad definitely has made the entire discussion about the parameter sniffing system very ugly.