1.1 when we have parameter sniffing?

• 在数据大的时候，where 中的参数有可能是一条，有可能是好几百条，这样就会造成差异

2. what is

Parameter Sniffing is a process of storing execution plan for a given parameter for a procedure when it was first executed after creating it or altering it. Usually this is a beneficial process as it improves performance. But if the data distribution in the tables used within the procedure is very uneven like for a parameter it retrieves 10 rows for other parameter it retrieves 1000 rows, parameter sniffing causes issues because each of this parameters need different execution plans for retrieving the data.

3.How to resolve Parameter Sniffing

1. Create procedure WITH RECOMPILE option.

• good for sniff; bad for opitimize

2. Local variable for the parameters: Define a local variable (inside procedure) and pass the input parameter values to the local variable and use the local variable in the query. With this option Query Optimizer creates a generic execution plan and not for a specific value.

• better for majority plan

3. Modularize procedure: Developer should have very good understanding of the data in order to handle parameter sniffing with this option. There will multiple procedures involved in this process along with a master procedure which call other procedures based on input parameters.

eg:创建2个sp，再创建第三个，在第三个中把参数姓名选出来，用if条件

4. Frequent Recompiles (or scheduled recompiles): This works good when the issue with parameter sniffing is not really bad
   Ex: if the results range in between few seconds to couple of minutes this process is good. With this option some users might wait for the results for couple of minutes and when the procedure is recompiled other set of users might wait for couple of minutes based on their usage of procedure.

5. WITH RECOMPILE hint?????
   only work we have 10 ?