With recompile sql update#

Use query hints such as 'Keep Plan' or 'KeepFixed Plan' while calling this statement.Īdjust the index on the tables (change from table scan to table seek). Set the database Parameterization property to Forced.Ĭreate Procedure… with Recompile option and EXEC… with Recompile option. In short, parameter sniffing is one plan for all parameter combinations.Please refer below suggestions and check whether any of them is helpful to you.Ĭheck whether there is any memory issue in your server.īring its schema name (fully qualified object name) instead of the object's own name in your statement. This solution allows us to reuse the execution plans and this is better than other options or solutions such as recompilation of a query or Stored Procedure in terms of performance. Here both sub Stored Procedures have the same procedure body, in other words both are identical.ĮXEC TestStoreProcedureCompany1 IF = 1002 )ĮXEC TestStoreProcedureCompany2 PROCEDURE INT In the preceding example, the Stored Procedure 'TestStoreProcedureDecisionTree' determines if the parameter value is 1001 then it calls a 'TestStoreProcedureCompany1', if the parameter value 1002 is found then it calls a 'TestStoreProcedureCompany2' Stored Procedure. Use Decision Tree algorithm in Stored ProcedureĪ decision tree Stored Procedure decides which Stored Procedure is called and the decision making is based on the parameter value.

With recompile sql update#

To disable the 'Auto Update Statistics', go to the database property and select the 'Option' tab and set the 'Auto Update Statistics' property to false.Īlternatively we can also use the following script to disable the Auto Update Statistics.ĪLTER DATABASE DatabaseName SET AUTO_UPDATE_STATISTICS OFF Frequently, update statistics are a bad option for dealing with parameter sniffing. Using the system Stored Procedure 'sp_updatestats' we can update the statics of the database. In the case when the statistics are very old or invalid or out of date then the optimizer chooses the wrong execution plan, hence query performance is degraded. The optimizer uses this statistical data to estimate the number of rows in the query result and based on the number of rows the optimizer will select the execution plan. Statistics are database objects that contains the information about the value distribution in one or more columns of a table and views. CompanyId = ( OPTIMIZE FOR UNKNOWN )ĭisable auto-update statistics during the batch OPTIMIZE FOR in a query hint allows us to specify a constant that is used by the optimizer while optimizing the query. This is a better way in which only a specific query is recompiled but in the above method entire the Stored Procedure is recompiled on every call. SQL Server offers the new query hint RECOMPILE that forces query recompilation. This solution enforces recompilation of the Stored Procedure on each run, so every time the optimizer creates a new execution plan for the current parameters.ĮXEC TestStoreProcedureNew 1002 WITH RECOMPILE CompanyId = Stored Procedure using 'WITH RECOMPILE' option Use of a dummy variable that is not used as a parameter also ensures the stability of the execution plan. Use dummy variables that are not directly used as parameters

In this article I am using the same example from my previous article: Using following, we may avoid the issue with parameter sniffing. If the value of parameters is not known at the time of compilation then there is nothing to be sniffed. This looks beneficial when good statistcs are maintained and typical parameters are used.ĭisabling the parameter sniffing is helpful when there are several parameters in a Stored Procedure and there is also no favorite combination of parameters. The optimizer will go and search the execution plan that is compiled earlier from the cache. The advantage of this behavior of the optimizer, is that the optimizer needs not re-create the execution plan to execute the same query multiple times. The cached execution plan may not be well-optimized for the new parameter, hence the second call of the query may be very slow. Parameter sniffing is enabled by default in SQL Server. The query optimizer will use the same execution plan for the next call of the Stored Procedure. If an execution plan is not present in the cache then the query optimizer creates a well-optimized execution plan based on input parameter values. If an execution plan already exists for a Stored Procedure or parameterized query in the cache then the query optimizer will blindly reuse it. Whenever a Stored Procedure or parameterized query is executed, the query optimizer tries to reuse the execution plan.