SQL Server - Partitioned Table scanning whole table instead of given range

Question

I've got the following query that is performing extremely slow on a Partitioned Table and I have no clue why. It seems that it is scanning the whole table instead of just the partitioned values. The _ve table is the one that is slow and is partitioned and Clustered on Date.

The non partitioned table takes about a minute and the partitioned table takes almost an hour.

DECLARE @PreviousTestDate DATE = '31 Jul 2015'

SELECT *
FROM TBL1 _rr 
LEFT JOIN TBL2 _ve 
       ON _ve.Date = _rr.Date
       AND _ve.Code= _rr.Code

WHERE _rr.Date > @PreviousTestDate

Seek Predicate for the Non-Partitioned Table:

<SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <StartRange ScanType="GT">
                                <RangeColumns>
                                  <ColumnReference Database="[DB]" Schema="[dbo]" Table="[TBL2_NonPartitioned]" Alias="[_ve]" Column="Date" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[Expr1010]">
                                    <Identifier>
                                      <ColumnReference Column="Expr1010" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </StartRange>
                              <EndRange ScanType="LT">
                                <RangeColumns>
                                  <ColumnReference Database="[DB]" Schema="[dbo]" Table="[TBL2_NonPartitioned]" Alias="[_ve]" Column="Date" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[Expr1011]">
                                    <Identifier>
                                      <ColumnReference Column="Expr1011" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </EndRange>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>

Seek Predicate for Partitioned Table:

                  <SeekPredicateNew>
                    <SeekKeys>
                      <Prefix ScanType="EQ">
                        <RangeColumns>
                          <ColumnReference Column="PtnId1002" />
                          <ColumnReference Database="[DB]" Schema="[dbo]" Table="[TBL2]" Alias="[_ve]" Column="Date" />
                        </RangeColumns>
                        <RangeExpressions>
                          <ScalarOperator ScalarString="RangePartitionNew([TBL].[Date] as [_rr].[Date],(0),'2008-07-31 00:00:00.000','2008-08-01 00:00:00.000','2008-08-02 00:00:00.000','2008-08-03 00:00:00.000','2008-08-04 00:00:00.000',<!--continues till arbitrary-->: '2012-01-07 00:00:00.000">
                            <Intrinsic FunctionName="RangePartitionNew">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[DB2]" Schema="[dbo]" Table="[TBL]" Alias="[_rr]" Column="Date" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="(0)" />
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="'2008-07-31 00:00:00.000'" />
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="'2008-08-01 00:00:00.000'" />
                              </ScalarOperator>

                   <!--           ... continues for range of partition which is 2025-12-31 00:00:00.000 -->
                            </Intrinsic>
                          </ScalarOperator>
                          <ScalarOperator ScalarString="[TBL].[Date] as [_rr].[Date]">
                            <Identifier>
                              <ColumnReference Database="[DB2]" Schema="[dbo]" Table="[TBL]" Alias="[_rr]" Column="Date" />
                            </Identifier>
                          </ScalarOperator>
                        </RangeExpressions>
                      </Prefix>
                    </SeekKeys>
                  </SeekPredicateNew>

Answer 1

Your query doesn't use @TestDate. Why did you include this variable into the question?

Do you really need LEFT JOIN in your query?

If you can use INNER JOIN, then adding explicit (seemingly redundant) filter into WHERE would help optimizer:

DECLARE @PreviousTestDate DATE = '2015-07-31'

SELECT *
FROM 
    TBL1 _rr 
    INNER JOIN TBL2 _ve 
       ON _ve.Date = _rr.Date
       AND _ve.Code= _rr.Code
WHERE 
    _rr.Date > @PreviousTestDate
    AND _ve.Date > @PreviousTestDate
OPTION(RECOMPILE);

OPTION(RECOMPILE) again helps optimizer, because it will know the actual value of variable when generating the plan. Since your query runs for hour having this option would not hurt.

If you need to use LEFT JOIN, then I'd try to perform this filter on _ve.Date first, then join with the result:

WITH
CTE
AS
(
    SELECT *
    FROM TBL2 AS _ve
    WHERE _ve.Date > @PreviousTestDate
)
SELECT *
FROM 
    TBL1 AS _rr 
    LEFT JOIN CTE AS _ve 
       ON _ve.Date = _rr.Date
       AND _ve.Code = _rr.Code
WHERE 
    _rr.Date > @PreviousTestDate
OPTION(RECOMPILE);