SQL - Parameter Sniffing

If a SQL query has parameters, SQL Server creates an execution plan tailored to them to improve performance, via a process called 'parameter sniffing'.

This plan is created the first time ( whenever SQL Server is forced  to compile or recompile  ) a stored procedure is executed and is stored and reused since it is usually the best execution plan. Just occasionally, it isn't, and you can then hit performance problems.

Every subsequent call to the same store procedure with the same parameters will also get an optimal plan, whereas calls with different parameter values may not always get an optimal plan.

How to Deal With Parameter Sniffing ISSUES:

Option 1: With Recompile

The problem with parameter sniffing is the fact that the first set of parameter values are used to determine the execution plan.  To overcome this problem, all you need to do is to recompile the stored procedure every time it is executed. 

 This can be accomplished by using the “WITH RECOMPILE” options when you create a stored procedure

The drawback of this option is the store procedure is recompiled with every execution.

Option 2: Disabling Parameter Sniffing

change the way the parameter values were used within the stored procedure.  By creating two different local variables  inside my procedure, setting those variables to the passed parameters, 

Option 3: Creating Multiple Stored Procedures

This option uses multiple stored procedures where each stored procedure can be optimize for a specific type of parameters values. 

To clear procedure cache (execution plan)
For SQLServer:
DBCC FREEPROCCACHE

For Oracle
alter system flush shared_pool;

https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/