Por que devo "Evitar tabelas de dimensão de particionamento" no SQL Server?

O SQL Cat tem uma lista de dicas intitulada Top 10 Best Practices for Building a Large Scale Relational Data Warehouse .

Na seção, 4 - Design dimension tables appropriatelyeles afirmam:

Evite particionar tabelas de dimensão.

Eles não mencionam por que isso não deve ser feito, nem consigo encontrar nada na web que aponte explicitamente por que é algo a ser evitado.

Por que devo evitar o particionamento de tabelas de dimensão?

Um exemplo mais concreto é fornecido abaixo para ajudar a facilitar uma resposta e manter uma discussão sobre por que o particionamento não deve ser feito em grandes data warehouses relacionais. Não estou procurando conselhos sobre como melhorar o modelo de dados específico do exemplo concreto. Se o exemplo não ajudar a fornecer nenhuma visão extra sobre por que as dimensões de particionamento não devem ser feitas, ignore-o.

Exemplo: você pode usar para fazer referência em sua resposta sobre por que as dimensões particionadas são uma ideia ruim/abaixo do ideal (se isso ajudar você) ...

Em nosso ambiente temos uma Accountdimensão, esta é particionada DateEffectivee carregada mensalmente . Algumas de nossas consultas envolvem WHERE DateEffective >= @ReportDate, que parece ser um bom candidato para eliminação de partições. Além disso, se precisarmos recarregar os dados do mês, excluiremos os dados de um mês inteiro, o que também parece se beneficiar do particionamento da tabela.

Atualização sobre nosso ambiente desde a postagem da pergunta...

A tabela mencionada acima possui índices não agrupados não alinhados (investigados com o seguinte código Brent Ozar ).

select
    [db_name]               = isnull(db_name(s.database_id),db_name())
    ,[schema_name]          = object_schema_name(i.object_id,db_id())
    ,[object_name]          = o.name
    ,index_name             = i.name
    ,index_type_desc        = i.type_desc
    ,data_space_name        = ds.name
    ,data_space_type_desc   = ds.type_desc
    ,s.user_seeks
    ,s.user_scans
    ,s.user_lookups
    ,s.user_updates
    ,s.last_user_seek
    ,s.last_user_update
from
    sys.objects as o

    inner join sys.indexes as i 
        on o.object_id = i.object_id

    inner join sys.data_spaces as ds 
        on ds.data_space_id = i.data_space_id

    left join sys.dm_db_index_usage_stats as s 
        on  i.object_id     = s.object_id 
        and i.index_id      = s.index_id
        and s.database_id   = db_id()
where 
        o.type      = 'u'
    and i.type      in (1, 2)
    and o.object_id in
    (
         select filter.object_id 
         from
         (
             select ob.object_id, ds.type_desc 
             from 
                sys.objects ob 
                inner join sys.indexes ind on ind.object_id = ob.object_id 
                inner join sys.data_spaces ds on ds.data_space_id = ind.data_space_id
             group by ob.object_id, ds.type_desc 
         ) as filter 
         group by filter.object_id 
         having count(*) > 1
     )
order by
    [object_name] desc
;

Isso mostrou:

• o clusteredíndice no esquema de partição
• 5 de 8 non-clusteredíndices no esquema de partição
• 3 de 8 non-clusteredíndices em primary, orows_filegroup
  • 1 deles era um unique, non-clusteredíndice (por uma questão de integridade: definido como um primary key non-clusteredno script de criação de tabela no controle de origem)

outra atualização

Encontrei esta resposta de Remus Rusanu , que lança alguma luz sobre as complicações com tabelas particionadas que seriam relevantes para as dimensões.

Suas declarações são citadas em bloco com minha interpretação usando meu exemplo acima

índices não alinhados impedem operações eficientes de troca de partições

Portanto, devemos tentar alinhar os índices quando uma tabela é particionada. A troca de partição nem é usada (possivelmente evitada?) para carregar a tabela no meu exemplo, pois há índices não alinhados.

O uso de índices alinhados resolve esses problemas, mas traz seu próprio conjunto de problemas, porque essa opção física de design de armazenamento afeta o modelo de dados

Este certamente parece ser o caso do exemplo que forneci, e algumas alterações seriam necessárias para implementar índices alinhados.

Devido às dimensões que normalmente usam chaves substitutas como primary key(a unique clustered index), isso fornece uma chave estreita cada vez maior (ou seja, tamanho de dados pequeno no disco). Isso é importante porque as buscas da árvore B que ocorrem quando a união entre dimensões e fatos pode ocorrer mais rapidamente. Além disso, o clustered indexserá parte de qualquer non-clustered indexes criado, o que também evita o inchaço do índice não clusterizado, criando buscas/varreduras de índice mais eficientes aqui também.

Por que isso é importante?

índices alinhados significam que restrições exclusivas não podem mais ser criadas/aplicadas (exceto para a coluna de particionamento)

e

todas as chaves estrangeiras que fazem referência à tabela particionada devem incluir a chave de particionamento

e

isso, por sua vez, requer que todas as tabelas que fazem referência à tabela particionada contenham o valor da coluna da chave de particionamento ... para declarar corretamente a restrição de chave estrangeira.

Os impactos são...

• Uma DateEffectivecoluna precisaria ser adicionada a cada tabela que faz referência à dimensão da conta em nosso ambiente. A implementação de uma DateEffectivecoluna nas tabelas de fatos que temos é redundante, pois essa pesquisa é cuidada por nosso processo ETL que carrega o AccountIDvalor da chave correto. Além disso, alguns fatos são declarados em uma granulação que é mais seletiva do que um datetipo de dados, o que DateEffectiveclaramente é, tornando mais sem sentido incluir essa coluna nas tabelas de fatos (efeitos de ondulação do modelo de dados).
• Alguns dos non-clustered indexes precisariam ser alterados para incluir a DateEffectivecoluna

No entanto ...

• Armazéns de dados normalmente não têm foreign keyrestrições implementadas. Uma boa resposta no SO cobre isso .
• Além disso, desde a edição de 2008, o Sql Server está parallel bitmap filtered hash-joinsdisponível para otimizar junções em estrela (consulte: Otimizando o desempenho da consulta do data warehouse por meio da filtragem de bitmap) e as chaves estrangeiras não são necessárias para essa otimização.
• Isso parece indicar que não há problema em particionar uma tabela de dimensão, pois as alterações necessárias agora são "apenas" incluir a chave de partição nos índices não alinhados, porque o problema de restrição de chave estrangeira não existe em nosso ambiente ( nosso processo ETL gerencia essa integridade).