Disable Parameter Sniffing for a Specific SQL Server QueryĬreate SQL Server Stored Procedures using the WITH RECOMPILE Option.Disable SQL Server Parameter Sniffing at the Instance Level.Use Dummy Variables on SQL Server Stored Procedures.Use the SQL Server Hint OPTION (OPTIMIZE FOR).Use the SQL Server Hint OPTION (RECOMPILE).Create SQL Server Stored Procedures using the WITH RECOMPILE Option.Now being exposed to the problem here are a few methods to address it: Workarounds for SQL Server Parameter Sniffing ![]() Now I will demonstrate a parameter sniffing issue with an example. In most cases the database workload is homogeneous so parameter sniffing won’t be a problem but on a small number of cases this becomes a problemĪnd the outcome can be dramatic. The problem arises when a query uses a previously generated plan optimized for a different data distribution. This is not an issue in fact it is needed to build the best plan. When the optimizer creates an execution plan it sniffs the parameter values. Query plan takes into consideration the cardinality estimation based on input parameters and with the help of statistics. ![]() For example, if I ask you which is best, an index seek followed by a key lookup or a table scan you may answer the first, but it depends on the number of lookups. The way SQL Server choses the best plan is by cost estimation. SQL Server attempts to create the best query plan to retrieve the data,īut what may seem obvious is not always the case with the plan cache. What is Parameter Sniffing in SQL Server?Įvery batch you execute, either ad-hoc or stored procedure, generates a query plan that is kept in the plan cache forįuture usage. Of course there are a lot of things to consider when answering this questioning and one that you should not overlook is Parameter Sniffing. How many times when a process takes longer than expected you have listened to the same complaint: It was working yesterday? This is a tough complaint to face, especially when you need to explain to someone with little knowledge about
0 Comments
Leave a Reply. |