Is there a way to overcome parameter sniffing in SQL Server?

Question

I came across the sniffing parameter when one of my requests took much longer than expected. When I delved into this problem, I found out that:

When the first time query is executed, it (SQL Server) creates a execution plan for this query, and for another n times, when the same query is executed, and if it has a large variance in the result set at the first start, there is a problem with the sniff parameter "

It was in my script.

Now my question is: is there a way or workaround to overcome the sniffing option in SQL Server in these scripts?

• I know by running sp_updatestats , I can check if this is happening or not.

• And also I know, to catch the problem, I need to monitor the procedure cache, query_hash and query_plan_hash sys.dm_exec_query_stats I can do this.

• I do not want to use the RECOMPILE in SET section, as this will create new execution plans each time the query is executed.

But instead of checking the problem, I want to overcome it by doing something in the request itself, I mean, for example, "Detecting a problem at runtime and creating a new execution plan only as needed (not every time)."

I often encounter the problem of sniffing parameters, so every useful suggestion and help will be greatly appreciated. Thanks in advance!

Answer 1

You can apply the OPTION(OPTIMIZE FOR UNKNOWN) option to queries that use parameters so that the query is optimized to use statistics and not specific parameters. This circumvents the sniffing option.

This is explained (briefly) in query prompts for T-SQL :

Optimize for Unknown

Instructs the query optimizer to use statistics instead of the initial values ​​for all local variables when compiling and optimizing the query, including parameters created with forced parameterization.

Answer 2

You can assign parameters to local variables.

 CREATE PROCEDURE SP_NAME @param1 INT, @param2 INT AS DECLARE @local_param1 INT DECLARE @local_param2 INT SET @local_param1 = @param1 SET @local_param2 = @param2 ... 

As explained in this post . Assigning parameters to local variables tells SQL Server to use static densities instead of static histograms, which avoids the problem of intercepting parameters.