SQL Server 2008 Stored proc - Optimizer thinks my parameter is nullable
===

Optimizer seems to be getting confused about the null-ability of a varchar parameter and I'm not sure I understand why. I'm using SQL Server 2008 btw. All columns being queried are indexed. The TDate column is a clustered, partitioned index. The FooValue column is indexed, non-nullable column.

Example:

    CREATE PROCEDURE dbo.MyExample_sp @SDate DATETIME, @EDate DATETIME, @FooValue VARCHAR(50)
    AS
    SET NOCOUNT ON

    --To avoid parameter spoofing / sniffing
    DECLARE @sDate1 DATETIME, @eDate1 DATETIME
    SET @sDate1 = @sDate
    SET @eDate1 = @eDate

    SELECT
        fd.Col1,
        fd.Col2,
        fd.TDate,
        fl.FooValue,
        fd.AccountNum
    FROM dbo.FooData fd
    INNER JOIN dbo.FooLookup fl
        ON fl.FL_ID = fd.FL_ID
    WHERE fd.TDate >= @sDate1
        AND fd.TDate < @eDate1
        AND fl.FooValue = @FooValue


Running this as a query works as expected. All indexes are seeks, no spoofing etc. Running this by executing the sproc takes 20 times longer - same query - same parameters. However, if I make the following change (very last line) everything works again.

    
    CREATE PROCEDURE dbo.MyExample_sp @SDate DATETIME, @EDate DATETIME, @FooValue VARCHAR(50)
    AS
    SET NOCOUNT ON

    --To avoid parameter spoofing / sniffing
    DECLARE @sDate1, @eDate1
    SET @sDate1 = @sDate
    SET @eDate1 = @eDate

    SELECT
        fd.Col1,
        fd.Col2,
        fd.TDate,
        fl.FooValue,
        fd.AccountNum
    FROM dbo.FooData fd
    INNER JOIN dbo.FooLookup fl
        ON fl.FL_ID = fd.FL_ID
    WHERE fd.TDate >= @sDate1
        AND fd.TDate < @eDate1
        AND fl.FooValue = ISNULL(@FooValue, 'testthis')

It's like the optimizer is getting confused about whether the parameter is nullable or not?  Also, adding a default value to the parameter doesn't make any difference. It still takes forever for the sproc to run unless I use = isnull(@parameter, 'some constant')

I'm happy I figured this out. But, I'd like to understand why this is happening and if there was a more elegant way to resolve the issue.