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 emprimary, orows_filegroup- 1 deles era um
unique, non-clusteredíndice (por uma questão de integridade: definido como umprimary key non-clusteredno script de criação de tabela no controle de origem)
- 1 deles era um
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 umaDateEffectivecoluna nas tabelas de fatos que temos é redundante, pois essa pesquisa é cuidada por nosso processo ETL que carrega oAccountIDvalor da chave correto. Além disso, alguns fatos são declarados em uma granulação que é mais seletiva do que umdatetipo de dados, o queDateEffectiveclaramente é, 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 aDateEffectivecoluna
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).
