What is parameter sniffing?

This is done in this way to prevent the process from compiling procedure from being repeated with each call because it is a CPU-intensive task.

However, there is one important detail: in this procedure compilation process, SQL Server seeks to assemble the best possible action plan. The great action plan. In the assembly of this best plan, the compiler considers the parameter value - that's where the parameter sniffing is: SQL Server procedures by looking at the contents of the parameters sent to the procedure to construct and assemble the best action plan to be stored in cache and reused later.

This describes how SQL Server operates normally.

The parameter sniffing issue becomes important when it happens that the "best action plan" for a particular procedure differs considerably from according to the value of the parameter. This has the potential to generate the unpleasant situation: in the first call, when the compilation takes place, a parameter can be used that generates an action plan that may be optimal for that parameter. However, in a later call, it may happen that a different value for the parameter performs terribly by using that action plan that was best for the other value of the parameter passed earlier.

The developer needs to know this parameter sniffing mechanism to guard against the mentioned situation. That is to say, it is a mechanism that at first serves to optimize the performance, but that can turn in its opposite, damaging the performance.

Source: link

(In the material used as a source, solutions to avoid the problems generated by parameter sniffing are addressed.)