Query Plans and Parameter Sniffing

It's not uncommon (and often quite annoying) for an SQL query to "flip". This is where the optimiser decides to use a different query plan even though the query code has not changed. This could be due to many things, but most commonly changes in statistics (for example, the table is now so large that an index is preferable to a table scan) but it could also be due to "parameter sniffing". This is where the optimiser "sniffs" the parameter values for the execution invoking the compile and uses these values (along with data statistics) to determine an appropriate query plan. Since query plans are cached, this can mean that the first execution of a procedure will generate a plan that is inappropriate for subsequent executions with different parameters. I don't want to cause anxiety - it is generally a good thing that the optimiser dynamically takes parameters, data statistics, physical resources etc into account in determining the best query plan. However, consider a procedure that has two parameters, @AccountNumber and @AccountType. When the procedure is first called, it may use @AccountNumber and @AccountType values to determine appropriate query plans. If AccountType='X' is very rare in the database, the optimiser might, quite rightly, choose a query plan using an index on AccountType. However, this index would be inappropriate when @AccountType='A', which is the value for 99 % of the data records. This is problematic, as the cached query plan could be favouring either parameter value, randomly determined by the "first" execution. Here are some a few techniques to help with this issue.

1. Keep the base procedure, but have it call one of two other procedures depending on @AccountType value. Each of these sub procedures will be compiled on appropriate values.
2. Use WITH RECOMPILE on the procedure, so that it recompiles with every execution.
3. Declare a new parameter in the procedure @AccountType2, which is set to @AccountType within the procedure and @AccountType2 is the one referenced in the query. This way, when the optimiser "sniffs" the @AccountType2 parameter it will always be the same initial value. Note, the sniffing happens when the proc is called, not when the statement is about to be executed.
4. Part of the solution might be to disable automatic updating of statistics and to update statistics manually (followed by performance and plan flipping tests). Note, there is a lot more to statistics and this is probably only appropriate for a tight OLTP environment. Disabling automatic updating can negatively impact performance of ad hoc queries that are using recently inserted key ranges. However, note; updated statistics will trigger recompilation of dependent query plans.
5. Use the OPTIMISE FOR hint. However this often is not appropriate as there is no appropriate value that will continue to be appropriate in the future. Note, since 2008, you can OPTIMIZE FOR UNKNOWN, which is similar to point 3 above.
6. Use the KEEPFIXED PLAN hint, which inhibits recompiles due to optimality issues.
7. Use a plan guide. Technically, this solution works, but it does add to operational overhead and the plan guide may not be appropriate in the future.
8. Enable Trace Flag 4136, which will disable parameter sniffing. Since parameter sniffing is generally a performance benefit, this is a bit drastic.
9. Force the use of a particular join and/or index. This is frowned upon for good reason.

Note, SET options can affect plan creation and reuse. This means that if you want to use a plan in Management Studio the same as the application, you will need to ensure your SET options are the same as the application. SET options that affect reuse include ANSI_NULL_DFLT_ON, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, DATEFIRST, DATEFORMAT, FORCEPLAN, LANGUAGE, NO_BROWSETABLE, NUMERIC_ROUNDABORT, and QUOTED_IDENTIFIER. The SET options used in the compile can be found in the query_plan under the tag StatementSetOptions. 

Again, plan flipping is not something that should cause you anxiety. However, it is good practice for database developers to recognise plan flipping possibilities and use the techniques above to minimise its causing performance issues. Most plans are very good and better than the above average developer. I encourage developers to make use of point 1 above, where there are different "classes" of parameter values.

Here is a useful query to see the query plans for a particular procedure. Click on the query_plan to see a graphical plan view. Note, this query is looking for procedures only. You can also view p.objtype='Adhoc' for ad hoc queries that are compiled as "temporary procs". You are able to view the parameter values used for compilation of the query plan from the query_plan of the first query above. Simply copy out the query_plan xml and search for ParameterList.

   SELECT p.*,st.text, qp.query_plan

      FROM sys.dm_exec_cached_plans p

            CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

            cross apply sys.dm_exec_query_plan(p.plan_handle) as qp

      WHERE st.text LIKE '%MyProcedureName%'

        and p.objtype='Proc'

Here is a useful query to see the performance of multiple query plans

   SELECT * FROM sys.dm_exec_query_stats

      WHERE plan_handle in

                  (SELECT plan_handle FROM sys.dm_exec_cached_plans p  

                        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 

                        WHERE text LIKE '%MyProcedureName%'

                          and p.objtype='Proc')

Here is a query to list all the procedures that have more than one query plan.

   SELECT db_name(st.dbid) DbName, st.text, count(distinct plan_handle)

      FROM sys.dm_exec_cached_plans p

            CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

            cross apply sys.dm_exec_query_plan(p.plan_handle) as qp

      WHERE p.objtype='Proc'

       group by db_name(st.dbid),st.text having count(distinct plan_handle) >1

       order by 1,2