4

Using sql server 2008, I have a simple stored procedure, the contents of which are

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

In a recent code review, the DBA said I should "add parameter sniffing" which I believe means I should account for parameter sniffing. I've never done this in the past and I am not having any performance issues with the query so I would think it is unnecessary.

While I presume the answer may be user preference, would it be best practice to account for parameter sniffing? Is it necessary if the stored procedure is called on a small dataset, used infrequently and is having no performance issues?

edit
Is this only applicable to parameters used in a WHERE clause, or for example, would you possibly need to account for all parameters in an INSERT statement?

earthling
  • 5,084
  • 9
  • 46
  • 90

2 Answers2

3

A simple search for a single value like this shouldn't be vulnerable to parameter sniffing. It's more of a concern when the parameters passed in result in widely different results and the optimal execution plan varies from the one that has been previously produced.

As an example, think of a query that looks for rows where a date column is between a @start_date and an @end_date. Calling the procedure with a date range of 2 days may produce/cache an execution plan that is not optimal for a date range of 1 year.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Doesn't that depend on whether `Code` is the PK of `LookUpPermissions`, or whether `LookUpPermissions` is a detail table with 100000 entries where 99% have a single `Code` value and the other 1% have a variety of other `Code`s? In that second situation I would expect the same behaviour as in your date case - the initial choice of `Code` value could affect the query plan. – Tao Dec 27 '11 at 14:42
  • @senloe Parameters used in an INSERT (`INSERT INTO MyTable (Col1, Col2) VALUES (@p1, @p2)`) would have absolutely no impact on the execution plan produced, so there's no chance of parameter sniffing in that case. – Joe Stefanelli Jan 04 '12 at 22:55
-1

Parameter Sniffing is another built-in "smarty thingy" (remember inline spell/grammar cheking) that Microsoft uses to optimize SQL queries. By sniffing the input parameters SQL Server makes it's best educated guess about which cached query plan would be the best plan to use. It does not always make the correct choice.

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

Michael Riley - AKA Gunny
  • 5,074
  • 4
  • 42
  • 89
  • 3
    They didn't ask for a vague explanation of what parameter sniffing is or how to take precautions to avoid it. They asked **when** to take these precautions. – Martin Smith Dec 27 '11 at 20:52