Início / dba / Perguntas / 119983
Accepted
John G Hohengarten
Asked: 2015-11-04 10:12:36 +0800 CST

Janela Non Clustered Index Scan Properties - ordem de predicado diferente da ordem de consulta

  • 772

Ambiente do tipo SQL 2012 Data Warehouse...

Eu tenho um índice não clusterizado em uma tabela de dimensão tipo 2, que chamarei apenas de DimStudent.

O índice é definido conforme abaixo, com tipos de dados e outras informações entre parênteses para contexto :

(

 [StudentID] ASC (VARCHAR(10))

,[EffectiveStartDate] ASC (SMALLDATETIME)

,[EffectiveEndDate] ASC (SMALLDATETIME)

)

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

A consulta em questão está sendo executada durante um pacote SSIS ETL e é definida conforme abaixo. Ele é especificado em uma transformação de pesquisa usando um cache parcial, na guia Avançado usando a seção de consulta personalizada, que é gerada automaticamente pelo Visual Studio (que coloca o alias [refTable] e SELECT * na consulta) com algumas modificações para a consulta para uso de pesquisa SCD2:

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

Observe que a ordem das colunas na consulta é a ID do aluno (@P1), depois a Data de início (@P2) e a Data de término (@P3).

Observe também que atualmente estou TRIMMing o StudentID. Eu determinei que isso não é mais necessário (foi necessário em algum momento por algum motivo) e já estou trabalhando na alteração do ETL para remover esse aspecto do TRIM.

No plano de consulta para esta consulta (usando sp_WhoIsActive de Adam Macahanic), confirmei que está fazendo uma verificação não agrupada usando este índice. Quando visualizo as Propriedades do operador de verificação, ele lista os Predicados como: (db e esquema removidos)

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

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

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

Minhas perguntas são:

  1. Por que os predicados no plano de consulta estão em uma ordem diferente (@P2, @P3, @P1) de como a consulta foi escrita? (@P1, @P2, @P3) Causado pelo TRIM?

  2. Por que é uma varredura e não uma busca, como o índice foi projetado para fazer para esta consulta? Causado pelo TRIM (não SARGable)?

execution-plan

2 respostas

  1. Best Answer

    O SQL Server acha que as datas são mais seletivas porque sabe como é o valor nos campos de data inicial e final. Por causa do corte na coluna StudentId, ele não tem ideia de quantas linhas existem. Depois de corrigir isso para não usar a coluna computada aparada, ela deve funcionar conforme o esperado.

    • 1

  2. Você tem algumas coisas acontecendo aqui. Primeiro, como você está usando um corte, é provável que o otimizador esteja usando os intervalos de datas primeiro, pois terá uma ideia melhor de onde procurar e quantas linhas serão retornadas.

    Segundo. Provavelmente, você está obtendo uma verificação porque solicitou um cache parcial, portanto, não corresponde a linhas individuais. Se você não fizesse uma pesquisa sem cache, acho que começaria a ver buscas de índice.

    Terceiro. Se sua tabela de dimensões for relativamente grande e sua tabela de fatos for grande, provavelmente faz sentido simplesmente fazer um cache completo na tabela dos alunos.

    • 1

relate perguntas