I've got this fairly simple reporting procedure that searches through inventory transaction data (stuff being received, moving around the warehouse, etc). The whole thing is listed below - I've only anonymized the procedure name. It's pulling data from Dynamics GP (explaining the awful table/field names), and an internal data warehouse.

    @strItem varchar(20) = NULL,
    @strSN varchar(20) = NULL,
    @strStartDate date,
    @strEndDate date

SET @strItem = ISNULL(@strItem, '') + '%'
SET @strSN = ISNULL(@strSN, '') + '%'

    d.unitcost AS cost,
    d.unitprice AS price,
    d.qty AS quantity, 
    RTRIM(d.itemnmbr) as item, 
    RTRIM(iv.ITEMDESC) AS itemdesc,
    RTRIM(d.serltnum) AS serltnum,
    RTRIM(d.docnumbr) AS docnumbr,
    RTRIM(d.refnumbr) AS refnumbr,
    RTRIM(d.username) AS username,
    RTRIM(d.from_place) AS from_place,
    RTRIM(d.to_place) AS to_place
FROM dw..dw_invtrans d
    INNER JOIN IV00101 iv
        ON d.itemnmbr = iv.ITEMNMBR
WHERE d.itemnmbr LIKE @strItem
    AND d.serltnum LIKE @strSN
    AND d.txdate BETWEEN CAST(@strStartDate AS datetime) AND CAST(@strEndDate AS datetime)
ORDER BY d.itemnmbr, d.txdate

There's obviously parameter sniffing going on, that much I can tell. The dw_invtrans table has 4,711,806 rows. There are non-clustered indexes on (itemnmbr, txdate) and (serltnum, txdate).

If I execute the procedure like this, it takes 17-20 seconds:

EXEC InvTrans @strItem = '', @strSN = '23-A', @strStartDate = '1/1/1999', @strEndDate = '2/21/2012'

If I execute it like this, it take less than 1 second, and returns the same results:

EXEC InvTrans @strItem = NULL, @strSN = '23-A', @strStartDate = '1/1/1999', @strEndDate = '2/21/2012'

As far as I can tell, when SQL Server sees one of the two item or serial number parameters is null, it generates an execution plan that ignores that filter completely (since the query uses it for a LIKE, it's assuming it won't match any rows, but the procedure turns it into '%'). If either of those parameters is an empty string, it considers that filter (which will end up matching every row, since it turns into '%'), thus hash-matching the small number of rows with a matching serial number to a totally unfiltered dw_invtrans (4.7 million rows).

What would be the best trick to have the execution plan ignore either of the two filters if it's null or a blank string? Bear in mind that I'm going to bind this to Reporting Services - normally I'd just have it dynamically select which query to run inside the procedure, but that often screws up Reporting Services' output column enumeration since it runs the procedure with FMTONLY on.

  • As an aside, your BETWEEN logic is faulty (unless your txdate column does not include time, ever, in which case it should be DATE not DATETIME). Usually better to use >= @Start AND < DATEADD(DAY, 1, @End). Feb 21, 2012 at 15:25
  • That's correct, txdate NEVER includes time, since it's taken from a Dynamics GP column (which almost never uses time in its 'datetime' columns). This data warehouse table was created way before the 'date' data type existed (possibly as early as SQL Server 6.5). I would have at least used 'smalldatetime', but I didn't work here 13 years ago. :)
    – db2
    Feb 21, 2012 at 15:52
  • when using set statistics time on, how much cpu time does each of the two alternatives take?
    – cairnz
    Feb 21, 2012 at 18:27

1 Answer 1


The sniff comes from the NULL or the empty string. The addition of % is ignored.
You can fix this one of 2 ways:

  1. Use parameter masking, by using another variable internally, say @IstrItem
    SET @IstrItem = ISNULL(@strItem, '') + '%'

  2. Add OPTIMIZE FOR UNKNOWN hint to the query

Other observations:

  • Why not an index on all 3 columns? One of these is probably best
    serltnum, txdate, itemnmbr or txdate, serltnum, itemnmbr
  • Do datatypes and length match? eg txdate is datetime, itemnmbr is varchar(20) etc
    If not, indexes will be ignored
  • So many non-key columns will require a key lookup, or the indexes are being ignored
    See Simple Talk's covering indexes article for more
  • I tried using another internal variable for the parameters, but that causes index seeks for both parameters regardless of input, like passing in ''. Data types in the table are actually char(31) and char(21) for itemnmbr and serltnum respectively. It's still using the correct indexes, though. The index join seems to perform well when both parameters are specific enough. I'm not worried about the key lookups, since there are usually few rows returned. Hadn't heard of OPTIMIZE FOR UNKNOWN, I'm going to read up on that one.
    – db2
    Feb 21, 2012 at 14:37
  • Interesting. If I use OPTIMIZE FOR UNKNOWN, it seeks the index on txdate and filters that based on @strItem and @strSN. Then it sorts and merge-joins that result to IV00101. This makes any execution with that particular date range (13 years) take 4 seconds.
    – db2
    Feb 21, 2012 at 14:48
  • BTW: "index seek" is good. Also, try making the internal variables the exact datatype as the columns and see what happens
    – gbn
    Feb 21, 2012 at 15:27
  • Index seeks are usually good, except in this case where it will happily "seek" all 4.7 million rows in the table. :)
    – db2
    Feb 21, 2012 at 15:55
  • @db2: seek is a drill into data. "all rows" is a scan
    – gbn
    Feb 21, 2012 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.