Non Clustered Index Scan Properties window - Predicate order different than query order

Question

SQL 2012 Data Warehouse type environment...

I have a Non Clustered Index on a Type 2 Dimension table, which I'll just call DimStudent.

The index is defined as below, with data types and other info in parentheses for context:

(

 [StudentID] ASC (VARCHAR(10))

,[EffectiveStartDate] ASC (SMALLDATETIME)

,[EffectiveEndDate] ASC (SMALLDATETIME)

)

INCLUDE [StudentKey] (INT, IDENTITY, PK).

The query in question is being run during an SSIS ETL package, and is defined as below. It is specified in a Lookup Transformation using a partial cache, in the Advanced tab using the Custom query section, which is mostly auto-generated by Visual Studio (which put the [refTable] alias and SELECT * into the query) with some modifications to the query for SCD2 lookup usage:

select * from (SELECT

 LTRIM(RTRIM(StudentID)) AS StudentID_Trimmed

,StudentKey

,EffectiveStartDate

,EffectiveEndDate

FROM dbo.DimStudent

)
AS [refTable]

where [refTable].[StudentID_Trimmed] = @P1

and [refTable].[EffectiveStartDate] <= @P2

and [refTable].[EffectiveEndDate] > @P3

Note that the order of the columns in the query is the student ID (@P1), then the Start date (@P2) and then End Date (@P3).

Also note that I am currently TRIMMing the StudentID. I have determined that this is no longer necessary (it was necessary at some point for some reason), and I'm already working on changing the ETL to remove that TRIM aspect.

In the query plan for this query (using Adam Macahanic's sp_WhoIsActive), I've confirmed it's doing a Non Clustered Scan using this index. When I view Properties of the scan operator, it lists the Predicates as: (db and schema removed)

[DimStudent].[EffectiveStartDate] <= **[@P2]**

AND [DimStudent].[EffectiveEndDate] > **[@P3]**

AND ltrim(rtrim([DimStudent].[StudentID])) = **[@P1]**

My questions are:

1. Why are the predicates in the query plan in a different order (@P2, @P3, @P1)than how the query was written? (@P1, @P2, @P3) Caused by the TRIM?

2. Why is it a scan and not a seek as the index was designed to do for this query? Caused by the TRIM (not SARGable)?

Answer 1

SQL Server thinks that the dates are more selective because it knows what the value in the Start and End date fields look like. Because of the trimming on the StudentId column it has no idea how many rows there are. Once you fix that to not use the trimmed computed column it should work as expected.

Answer 2

You have a couple of things going on here. First since you are using a trim it's likely that the optimizer is using the date ranges first since it will have a better idea of where to look and how many rows will be returned.

Second. You are getting a scan likely because you've asked for a partial cache so it's not matching on individual rows. If you were to no a no cache lookup I think you'd begin to see index seeks.

Third. If your dimension table is relatively large and you fact table is large, it probably makes sense to simply do a full cache on the students table.