I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in:
CREATE PROCEDURE MyProc
@MyDate DATETIME = NULL
AS
IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
-- Do Something using @MyDate
I'm having problems whereby if @MyDate
is passed in as NULL
when the stored procedure is first compiled, the performance is always terrible for all input values (NULL
or otherwise), wheras if a date / the current date is passed in when the stored procedure is compiled performance is fine for all input values (NULL
or otherwise).
What is also confusing is that the poor execution plan that is generated in is terrible even when the value of @MyDate used is actually NULL
(and not set to CURRENT_TIMESTAMP
by the IF statement)
I've discovered that disabling parameter sniffing (by spoofing the parameter) fixes my issue:
CREATE PROCEDURE MyProc
@MyDate DATETIME = NULL
AS
DECLARE @MyDate_Copy DATETIME
SET @MyDate_Copy = @MyDate
IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP
-- Do Something using @MyDate_Copy
I know this is something to do with parameter sniffing, but all of the examples I've seen of "parameter sniffing gone bad" have involved the stored procedure being compiled with a non-representative parameter passed in, however here I'm seeing that the execution plan is terrible for all conceivable values that SQL server might think the parameter might take at the point where the statement is executed - NULL
, CURRENT_TIMESTAMP
or otherwise.
Has anyone got any insight into why this is happening?