Procedure sa ulaznim parametrima se koriste da bi se poboljšale performanse sistema, kada postoji mnogo zajedničkih upita istog oblika. Prilikom prvog izvršavanja procedure (pod prvim se podrazumijeva izvršavanje nakon (re)kompajliranja), SQL Server kreira keširani plan izvršenja (execution plan), koji koristi prilikom sledećeg poziva. Ovakvo ponašanje (koje predstavlja ponašanje SQL Servera po dizajnu) je poznato pod nazivom parameter sniffing.
Kreirani plan je prilagođen prvoj prosleđenoj vrijednosti parametra i u zavisnosti od njene raspoređenosti na izvoru, može biti izabran tako da koristi npr. nonclustered index seek. Takav plan može da odgovara ostalim vrijednostima parametra, ali može da se desi da nekoj vrijednosti parametra više odgovara korišćenje clustered index scan, a ipak će koristiti keširani plan.
Tada procedura traje mnogo duže nego što je uobičajeno, dešava se da se izvršavanje poveća sa minut ili dva na preko sat vremena. Kada pokušate da zaključite koji upit u proceduri je iznenada počeo da pravi problem, zaključićete da se tijelo procedure, sa proslijeđenom vrijednošću parametra pušteno kao upit izvršava za isto vrijeme kao procedura ranije i da problem nije u tijelu procedure.
Problem je opisano ponašanje SQL Servera, odnosno parameter sniffing.

Postoji nekoliko načina da se ovakvo ponašanje zaobiđe.

1. Rekompajliranje procedure prilikom svakog izvršavanja

Pošto problem nastaje keširanjem plana izvršenja prvim izvršavanjem procedure, onda rešenje može da bude postavljanje opcije da se procedura rekompajlira prilikom svakog izvršavanja, čime se plan izvršenja kreira tj. mijenja svaki put. Ovo se postiže naredbom WITH RECOMPILE, koja ide odmah nakon navođenja ulaznih parametara, koju možete da dodate nakon dropovanja procedure i ponovnog kreiranja.

Primjer:

CREATE PROC dbo.TestKefaloWithRecompile
  @CrtacID INT
WITH RECOMPILE
AS
SELECT Naziv, Opis
  FROM CrtaniFilmovi
  WHERE CrtacID =@CrtacID;

Na ovaj način se troše dodatni sistemski resursi za kompajliranje.
Moguće je kompajlirati samo dio procedure koji koristi parametar. U prethodnoj proceduri bi to imalo smisla ukoliko bi bilo još upita nakon navedenog i izgledalo bi:

CREATE PROC dbo.TestKefaloWithRecompile
  @CrtacID INT
AS
SELECT Naziv, Opis
  FROM CrtaniFilmovi
  WHERE CrtacID =@CrtacID; 
           OPTION (RECOMPILE)

     …

Kad je dodano rekompajliranje na nivou procedure, onda nije keširan plan ni za jedan dio procedure, dok kompajliranjem određenog podupita, samo plan za taj podupit nije keširan.

2. Korišćenje opcije OPTIMIZE FOR

Ukoliko postoji neki plan izvršenja koji je dovoljno dobar za sve vrijednosti parametra (iako nije idealan za sve vrijednosti), može da se koristi opcija OPTIMIZE FOR. Ova opcija sugeriše SQL Serveru da koristi određenu vrijednost parametra prilikom kreiranja plana izvršenja tj. onu koja generiše optimalan plan za sve.

Primjer:

CREATE PROCEDURE dbo.TestKefaloOptimizeFor
 @CrtacID INT
AS
  SELECT Naziv, Opis
  FROM CrtaniFilmovi
  WHERE CrtacID =@CrtacID
  OPTION (OPTIMIZE FOR (@CrtacID=2));

Može da se koristi i opcija OPTIMIZE FOR (@parameter UNKNOWN), koji koristi prosječnu statistiku distibucije za određeni parametar, pa je manje rizičan od opcije kad se koristi konkretna vrijednost parametra.

3. Kreiranje lokalne varijable

Deklarisanjem lokalne varijable kojoj se dodjeljuje vrijednost ulaznog parametra se postiže efekat sličan korišćenjem opcije OPTIMIZE FOR (@parameter UNKNOWN).

Primjer:

CREATE PROCEDURE dbo.TestKefaloLocalVar
 @CrtacID INT
AS
  DECLARE @LocalCrtacID INT;
  SET @LocalCrtacID = @CrtacID;

  SELECT Naziv, Opis
  FROM CrtaniFilmovi
  WHERE CrtacID = @LocalCrtacID;

Nedostatak ove opcije je što će neki upiti koristiti suboptimal planove izvršenja.

4. Onemogućavanje parameter sniffing-a

Od verzije SQL Server 2005 sp4 je moguće onemogućiti parameter sniffing. Pošto je ovo opcija na nivou servera, to bi značilo da bi se izgubile dobre strane koje donosi, koristili bi se više sistemski resursi zbog pojedinačkih slučajeva, koji mogu da se riješe i na drugi način.

Parameter sniffing nije karakterističan samo za procedure, može da izazove neprilagođen plan izvršenja i kod funkcija, kao i kod izvršavanja dinamičkih sql upita pozivom sistemske procedure sp_executesql.