I have a very slow running query, and I've isolated the problem down to the following SQL.

SELECT
  ...
FROM
    TableA a(nolock) 
    inner join TableB b with(nolock) on (b.Id = a.Id AND b.Date>= isnull(@timestamp_start, '17530101') AND b.Date < isnull(@timestamp_end, '99991231'))

• a = 2million+ rows

• b = 2million+ rows

• b.Id => Index, unique, non-clustered

• a.Id => Index, unique, non-clustered

• b.Date => Index, non-unique, non-clustered

This takes several minutes without returning a result even for a single day date range.

UPDATE:

The estimated execution plan on the production server is showing me this, which shows that a complete scan of a huge table (TableA) is occurring near the beginning of the query, rather than the TableB.date range being used to filter in the WHERE. Why is this happening and how can I force the query to filter first on my date range?

The plan relates to this query, which in detail is:

SELECT
  ...
FROM
    TableA a(nolock) 
    inner join TableB b with(nolock) on (b.Id = a.Id AND b.Date>= isnull(@timestamp_start, '17530101') AND b.Date < isnull(@timestamp_end, '99991231'))
    inner join TableC c with(nolock) on c.Id2 = s.Id2 and c.Id3 = a.Id3 and c.Id = b.Id
    inner join TableD d with(nolock) on (a.DealId = d.Id and d.Id3 = s.Id3 AND (@myparam is null OR d.ProviderName = @myparam))
    inner join TableE e with(nolock) on (e.Id = d.Id AND (@myparam2 is null OR e.Id = @myparam2))