Do you always have to anticipate problems caused by sniffing parameters?

Question

Using SQL Server 2008, I have a simple stored procedure whose contents

DELETE FROM [ABC].[dbo].[LookUpPermissions] WHERE Code = @Code 

In a recent code review, the database administrator said that I should “add the sniffing parameter”, which I believe means that I should consider the sniffing parameter. I have never done this in the past, and I have no performance issues with the query, so I think this is not necessary.

While I suggest that the answer might be preferable for users, would it be best practice to consider the sniffing option? Is it necessary if the stored procedure is called in a small data set, is used infrequently and does not have performance problems?

---

change
Is this only applicable to the parameters used in the WHERE clause, or, for example, you may need to consider all the parameters in the INSERT statement?

Answer 1

A simple search for a single value like this should not be vulnerable to sniffing parameters. This is of great concern when the transmitted parameters lead to different results, and the optimal execution plan differs from the one that was previously produced.

As an example, consider a query that searches for rows where the date column is between @start_date and @end_date. Calling a procedure with a date range of 2 days can lead to the creation / caching of an execution plan that is not optimal for a date range of 1 year.

Answer 2

The Sniffing parameter is another built-in “smarty thingy” function (remember the built-in spell / grammar cheking) that Microsoft uses to optimize SQL queries. Sniffing the input parameters, SQL Server makes the best informed assumption about which caching plan is the best use plan. This does not always make the right choice.

Read this for information on how to trick SQL into not using the pramater framework .