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

Question

I came across parameter sniffing when one of my queries took a much longer time to execute than expected. When I delved a little deeper into this problem I came to know that:

When first time query gets executed it (SQL Server) creates execution plan for that query and for other n number of times same query executed and if it has large variance in result set with first time execution then parameter sniffing problem occurs".

This was in my scenario.

Now my question is, is there any way or workaround to overcome parameter sniffing in SQL Server in these scenarios?

• I know by running sp_updatestats I can verify whether it is happening or not.

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

• I don't want to use RECOMPILE in SET variable section as this will create new execution plans each time query is executed.

But instead of verifying the problem I want to overcome it by doing something in query itself, I mean for example "Detect the problem at run time and create new execution plan only as and when required(Not every time)".

I face parameter sniffing problem quite frequently, so every helpful suggestion and help will be greatly appreciated. Thanks in advance!

Nodose

Post the query as well – Catenoid

Assign the parameters to local variables inside the proc. – Frasch

You can also try the OPTIMIZE FOR (@x UNKNOWN) query hint. blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/… – Armillas

@Frasch That's the old-school way (SQL Server 2005 and earlier) of bypassing parameter sniffing. For the new-school way, see my answer. – Mannuela

Thanks @Mannuela . Looks strange that it was the first thing that came to my mind even though I never worked in 2005 and earlier versions – Frasch

@Frasch Well it does work still in newer versions. I still use this "assign to local variable" trick for software that has to support multiple SQL Server versions with SQL Server 2005 one of those versions. – Mannuela

Answer 1

You can assign the 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 on this post. Assigning parameters to local variables tells SQL Server to use static densities instead of static histograms, hence avoiding the parameter sniffing problem.

Dunfermline

Answer 2

You can apply the option OPTION(OPTIMIZE FOR UNKNOWN) to queries using parameters to have the query optimized to use statistics, rather than being optimized for specific parameters. This bypasses parameter sniffing.

This is explained (summarily) in Query Hints for T-SQL:

OPTIMIZE FOR UNKNOWN

Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization.

Mannuela