É possível forçar o otimizador a eliminar tabelas irrelevantes nesta visão particionada?

Habilite o sinalizador de rastreamento 4199.

Eu também tive que emitir:

UPDATE STATISTICS dbo.ObservationDates 
WITH ROWCOUNT = 73049;

para obter os planos mostrados abaixo. As estatísticas desta tabela estavam ausentes no upload. O valor de 73.049 veio das informações de cardinalidade da tabela no anexo do Plan Explorer. Usei o SQL Server 2014 SP1 CU4 (compilação 12.0.4436) com dois processadores lógicos, memória máxima definida como 2.048 MB e nenhum sinalizador de rastreamento além de 4.199.

Você deve então obter um plano de execução que apresenta eliminação de partição dinâmica:

select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
where 
    od.[Year] >= 2000 and od.[Year] < 2006
group by 
    od.[Year]
option (querytraceon 4199);

Fragmento do plano:

Isso pode parecer pior, mas os filtros são todos filtros de inicialização . Um exemplo de predicado é:

Por iteração do loop, o predicado de inicialização é testado e somente se ele retornar verdadeiro é que a busca de índice clusterizado abaixo dele é executada. Portanto, eliminação de partição dinâmica.

Isso talvez não seja tão eficiente quanto a eliminação estática, especialmente se o plano for paralelo.

Pode ser necessário tentar dicas como MAXDOP 1, FAST 1ou FORCESEEKna exibição para obter o mesmo plano. As opções de custo do otimizador com exibições particionadas (como tabelas particionadas) podem ser complicadas.

O ponto é que você precisa de um plano que apresente filtros de inicialização para obter a eliminação dinâmica de partições com exibições particionadas.

Consultas com USE PLANdicas incorporadas: (via gist.github.com):

• plano de série
• plano paralelo

Minha observação sempre foi que você deve especificar o valor (ou intervalo de valores) para a coluna de partição explicitamente na consulta para obter "eliminação de tabela" em uma exibição particionada. Isso se baseia na experiência de usar exibições particionadas na produção do SQL Server 2000 até o SQL Server 2014.

O SQL Server não tem um conceito de operador de junção de loop no qual o mecanismo pode direcionar dinamicamente a busca diretamente para a tabela adequada no lado interno do loop com base no valor da linha no lado externo do loop. No entanto, como explica a resposta de Paul , existe a possibilidade de um plano com filtros de inicialização para pular dinamicamente tabelas irrelevantes no lado interno do loop em tempo constante (em oposição ao logarítmico ao realizar a busca).

Observe que para tabelas particionadas, no entanto, esse tipo de busca (para uma partição específica) é suportado.

Se você está determinado a usar exibições particionadas, outra opção é dividir sua consulta em várias consultas, como:

-- Gather than the min/max values for the partition column
DECLARE @minDateKey INT,
        @maxDateKey INT
SELECT @minDateKey = MIN(DateKey),
        @maxDateKey = MAX(DateKey)
FROM dbo.ObservationDates od
WHERE od.[Year] >= 2000 and od.[Year] < 2006

-- Since I have a stats-only copy of the database, simulate having run the query above
-- (You can comment this out since you have the actual data.)
SELECT @minDateKey = 20000101, @maxDateKey = 20051231

-- Adjust the query to use the min/max values of the partition column
-- rather than filtering on a different column in the dimension table
select 
    od.[Year], 
    AvgValue = avg(ObservationValue)
from dbo.v_Observation o 
join dbo.ObservationDates od
    on o.ObservationDateKey = od.DateKey
WHERE od.DateKey >= @minDateKey AND od.DateKey <= @maxDateKey
group by od.[Year]
-- Must use OPTION RECOMPILE; otherwise the plan will touch all tables because it
-- must do so in order to be valid for all values of the parameters!
OPTION (RECOMPILE)

Isso produz o seguinte plano. Agora há uma consulta extra que atinge a tabela de dimensão, mas a consulta sobre a tabela de fatos (presumivelmente muito maior) é otimizada.