This blog is part of the series The TSQL Classes: Stored Procedure

The link to previous post are given below.

http://www.sqlservergeeks.com/sql-server-tsql-the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-caching/

http://www.sqlservergeeks.com/sql-server-stored-procedures-the-tsql-classes-stored-procedures-a-primer/

http://www.sqlservergeeks.com/sql-server-tsql-the-tsql-classes-stored-procedures-parameter-sniffing-resolution/g

In last class, I talked about parameter sniffing; a plan cache side effect. In this class I’ll talk about ways to resolve parameter sniffing.

The methods to resolve parameter sniffing issues are listed below.

  1. Using local variables with in procedures.
  2. Using with recompile option when creating a procedure.
  3. Using optimize for query hint.

Let’s take them one by one.

1. Using local variables with in procedures.

The above script creates and fills a table fruit with values.

The above query creates two procedures usp_fetchfruits and usp_fetchfruits_localvar. The procedure usp_fetchfruits_localvar uses local variable in where clause. Let’s now compare the execution plan of the above procedures.

1_The_TSQL_Classes_Stored_Procedures_Parameter_Sniffing_Resolution

The above snapshot compares the execution plan of the two procedures. The difference in optimizers estimated and actual number of rows in the 2nd plan is because the optimizer doesn’t knows the actual parameter values passed to the where clause, thus the optimizer creates a generic plan based on available statistics. The table fruit has a total of 10010 rows and the optimizers estimated number of rows is 5005 which is exactly the half of the total count.

This method does resolves performance issues but it might not be that effective if actual number of rows are way greater than the average estimated rows.

2. Using with recompile option when creating a procedure.

The parameter sniffing problem occurs because the cached plan from the first execution is used in future procedure executions. If the procedure is being compiled and plan is prepared again for each and every execution than parameter sniffing will be resolved. This is what With Recompile does when used in procedure definition. It recompiles the procedure to create a new plan every time procedure is executed.

2_The_TSQL_Classes_Stored_Procedures_Parameter_Sniffing_Resolution

In above snapshot, in both the execution plans the optimizer’s estimated and actual numbers of records are equal because the plan has been recompiled for each of the parameter values instead of cached plan reuse for the second execution.

The problem with this method is that resources are being used to recompile procedure every time. The plan is even recompiled for parameters values which might have benefited from the cached plan.

3. OPTIMIZE FOR query hint.

Using OPTIMIZE FOR query hint we can have SQL Server optimize a query based on a specific parameter value.

In the above procedure, no matter what parameter value is supplied the select query will be optimized for the value “Mango”.

3_The_TSQL_Classes_Stored_Procedures_Parameter_Sniffing_Resolution

The above snapshot compares the execution plan for parameter values “Apple” and “Mango”. In both the plans the estimated values remain 10000 which is the total count for parameter “Apple”.

The SQL Server 2008 extends the OPTIMIZE FOR query hint so as to optimize a query for an unknown value.  This is somewhat similar to the method using local variable.

The above procedure uses OPTIMIZE FOR UNKNOWN query hint  to get rid of parameter sniffing issue.

4_The_TSQL_Classes_Stored_Procedures_Parameter_Sniffing_Resolution

The above snapshot compares the execution for parameter values Apple and Mango. The optimizer estimates an average number of rows returned as it did when using local variables.

Another way is to create multiple stored procedures and call them up based on the parameter values. For example, In this case we can create 2 different procedures one for parameter value Mango and one for parameter value Apple and call them up based on the parameter value supplied.

Summary

In this class, I talked about ways to improve performance caused because for parameter sniffing issue.

 

Regards

Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook