.NET Parametrized queries and sp_executesql performance improvements

If you fetching performance problems by executing sp_executesql consider to use in your query hint RECOMPILE. Note, that sp_executesql is used all the times when you execute parametrized queries in .NET for example. So, the need for this hint is much bigger than you think.
Initally your query can be something like

select col1 from Test_table where col1 = @param1
order by col1

what is transfered to sql server like

exec sp_executesql N'select col1 from Test_table where col1 = @param1
order by col1', N'@param1 int'

So your query should looks like

select col1 from Test_table where col1 = @param1
order by col1 OPTION (RECOMPILE)

Notethat OPTION(RECOMPILE) has been added to the query. This option in some cases can boost your query performances.

Main problem here is well known in SQL Server and is called parameter sniffing, what means that SQL Server looks at the input parameters to build a query plan and if the parameters are fine for the first invocation of the query, than you are fine with the performances. But if parameters dramatically changes, than you might need a new execution plan which will not be any more index seek but index scan. With the option RECOMPILE you are requesting from SQL SErver ptimizer to generate a new plan for your query and to ignore the existing one. Thus, you ghet proper plan. Note, that using always option RECOMPILE will decrease query performances if the proper plan will be found in the cashed plans.

In my environement, this option increases some of the queries more than 100 times.

Leave a Reply

Your email address will not be published. Required fields are marked *