Poor Performing Query

Hi
The answer to this one will be v simple for someone, alas my memory fails
me, what I am after is the term for whats I am experiencing. I have a stored
procedure which has a list of paramaters passed to it, one of these
parameters is set to NULL in the procedure header, within the code at the
begining is a IF NULL statement which then gives the variable a value. This
causes havoc with the execution plan as it believes a NULL value will be use
d
but the procedure uses the value assigned to in the IF NULL statement and th
e
time to execute can be significantly longer using the default. I am aware of
this and I know there is a term associated to this but for the life of me I
can't remember what its called.
anyoneParameter Sniffing:
http://www.google.co.uk/groups?as_e...lic.sqlserver.*
David Portas
SQL Server MVP
--|||"Parameter sniffing". The optimizer sniffs the parameter for the proc when i
t is to create a proc
plan. Apart from that, a DML statement has no context. When the optimizer lo
ok at a batch/proc it
only cares about the SELECT, INSERT, UPDATE ad DELETE statements. From that,
you can probably
realize that the SET command haven't executed and the value you passed to th
e proc is the one used
by the optimizer (parameter sniffing).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MysticMart" <MysticMart@.discussions.microsoft.com> wrote in message
news:6A43A4CB-F8DE-41C8-B53E-FB830599E976@.microsoft.com...
> Hi
> The answer to this one will be v simple for someone, alas my memory fails
> me, what I am after is the term for whats I am experiencing. I have a stor
ed
> procedure which has a list of paramaters passed to it, one of these
> parameters is set to NULL in the procedure header, within the code at the
> begining is a IF NULL statement which then gives the variable a value. Thi
s
> causes havoc with the execution plan as it believes a NULL value will be u
sed
> but the procedure uses the value assigned to in the IF NULL statement and
the
> time to execute can be significantly longer using the default. I am aware
of
> this and I know there is a term associated to this but for the life of me
I
> can't remember what its called.
> anyone