A documentação sobre os benefícios do particionamento lista o seguinte como o primeiro benefício de desempenho do particionamento de tabelas
o otimizador de consultas pode processar consultas de junção equivalente entre duas ou mais tabelas particionadas mais rapidamente quando as colunas de particionamento são as mesmas que as colunas nas quais as tabelas são unidas.
A mesma página fala um pouco mais sobre esse tópico mais adiante , mas não chega a nada além de dizer que unir tabelas particionadas que não são particionadas exatamente da mesma forma não obtém as mesmas otimizações que unir tabelas particionadas equivalentemente. Isso é, claro, irrelevante para o que a seção anterior alegou; qualquer comparação de desempenho entre duas formas de particionar tabelas não tem nada a ver com os benefícios do particionamento quando comparado a não particionar.
Isso me faz perguntar: unir tabelas particionadas equivalentemente é mais rápido do que unir duas tabelas com a mesma chave de índice clusterizado líder? Eu ficaria feliz com uma resposta experimental ou uma que usasse a teoria relevante para chegar a uma conclusão.
Eu li sobre os internos e li muitos white papers e blogs, mas acho que não vi isso abordado em lugar nenhum. Meu entendimento dos internos me leva a concluir que a abordagem particionada deve ser mais lenta do que a abordagem não particionada. Afinal, tudo o que o particionamento realmente faz é colocar outra chave de índice na frente da sua lista de chaves. Meus próprios experimentos descobriram o mesmo.
Pode ser, mas como qualquer coisa relacionada a particionamento, depende muito das circunstâncias. Você pode ver melhor ou pior desempenho na prática.
Em todo caso, é mais uma questão de habilidades de otimizador de consulta do que algo realmente fundamental. Como diz o último link da pergunta (ênfase adicionada):
O formato do plano nesse link é:
Há menos trocas neste plano, comparado a um hash regular ou merge join. A troca única mostrada usa particionamento Demand para distribuir um novo id de partição para um thread paralelo, conforme necessário.
A junção é realizada em uma partição por vez, com um thread por partição. Se houver 64 partições para processar no DOP 8, cada thread pode acabar processando 8 partições, uma após a outra. Qualquer outra distribuição de trabalho em tempo de execução é possível, dependendo da quantidade de trabalho necessária por partição e de quanto tempo o thread obtém em seu planejador.
Além da memória e dos threads liberados pela falta de trocas de reparticionamento em ambas as entradas de junção, a junção hash requer no máximo memória para 8 partições a qualquer momento: Cada thread pode reutilizar a memória de construção hash que usou para a partição anterior. Portanto, o requisito geral de memória pode ser muito menor do que processar a operação inteira em oito threads de uma só vez.
Claro, não há nada de mágico sobre a ideia fundamental. Pode-se escrever uma junção colocada manualmente com tabelas não particionadas, assumindo que há um conjunto adequado de intervalos conhecidos antes do tempo; no entanto, é preciso ter cuidado com os detalhes ao implementar essa ideia.
Uma das preocupações é que ele funciona melhor quando os dados são distribuídos uniformemente entre as partições e cada thread recebe o mesmo tempo em um agendador. As desvantagens do modelo thread-per-partition usado no SQL Server 2005 e anteriores são uma das razões pelas quais a abordagem de prefixo de índice foi desenvolvida e melhorias foram feitas na distribuição paralela de threads, conforme observado em seu link.
Você pode encontrar análises de desempenho e mais detalhes no meu artigo, Melhorando o desempenho de junções de tabelas particionadas .
Note que tudo isso se aplica principalmente a planos de execução de modo de linha . Planos de modo de lote distribuem lotes entre threads dinamicamente e não usam trocas. Você ainda pode encontrar algum benefício em reduzir o requisito máximo de memória.