0

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)?

  • Special thanks to Kin for editing my post and adding the gray code blocks. This was my first post to SE, so I appreciate the help on that :) – John G Hohengarten Nov 4 '15 at 16:07
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.

1

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.

  • I used a partial cache in the SSIS Lookup Transform because it's required in order to use the Advanced page to modify the query to allow parameterization for SCD2 lookups. AFAIK, you can't do that in Full Cache mode in the Lookup Transform as the Advanced page is disabled (grayed out) when Full Cache (or No Cache) is selected. – John G Hohengarten Nov 4 '15 at 16:26
  • @JohnGHohengarten yeah. If you are to use the full Cache instead it does a select * and then holds the results in memory and then matches on your lookup keys. – Zane Nov 4 '15 at 16:28

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.