SQL poor stored procedure execution plan performance - parameter sniffing

Question

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?

Answer 1

Basically yes - parameter sniffing (in some patch levels of) SQL Server 2005 is badly broken. I have seen plans that effectively never complete (within hours on a small data set) even for small (few thousand rows) sets of data which complete in seconds once the parameters are masked. And this is in cases where the parameter has always been the same number. I would add that at the same time I was dealing with this, I found a lot of problems with LEFT JOIN/NULLs not completing and I replaced them with NOT IN or NOT EXISTS and this resolved the plan to something which would complete. Again, a (very poor) execution plan issue. At the time I was dealing with this, the DBAs would not give me SHOWPLAN access, and since I started masking every SP parameter, I've not had any further execution plan issues where I would have to dig in to this for non-completion.

In SQL Server 2008 you can use OPTIMIZE FOR UNKNOWN.

Answer 2

One way I was able to get around this problem in (SQL Server 2005) instead of just masking the parameters by redeclaring local parameters was to add query optimizer hints.

Here is a good blog post that talks more about it: Parameter Sniffing in SqlServer 2005

I used: OPTION (optimize for (@p = '-1'))