Pablicio,
Eu também não utilizei muito este tipo de funcionalidade, sempre trabalhei com participionamento de filegroup.
Mas respondendo as suas dúvidas, baseando no meu conhecimento e informações fornecidas por alguns colegas, podem resaltar algumas coisas:
1 - Em relação a performance, você poderá obter realmente um ganho e perceber diferenças em relação a tables com um volume muito grande de dados.
2 - Valer a pena, sempre vai depender da sua necessidade, muitas vezes este tipo de funcionalidade acaba sendo deixada de lado por falta de objetivos e exigências da estrutura de hardware e software, muitas vezes mais relacionado ao software.
Mas eu acredito que é uma ótima alternativa separar os dados em locais diferentes, implementando um planejamento muito bem definido, para saber o deverá ou não ser particionado.
3 - O custo de processamento para o banco de dados, eu imagino que deverá ser baixo, pois como os dados estão distribuídos em porções menores isso ajuda em muito no momento da busca de informações, mas é importante sempre trabalhar com índices, procurando sempre a melhora da performance.
4 - O comportamento em relação a busca de informações é realiza de forma intuitiva, ou seja, o particionamento de uma table é definido no momento da criação da table, com isso, é criado um vínculo entre a table principal e seus particionamento, este vínculo é armazenado dentro de uma system view no SQL Server.
No momento da busca, é realizado uma consulta a esta system view, indicando a table principal direcionamento a busca para parte particionada que possui os dados.
5 - Como já informei ainda não utilizei em produção, somente em testes, mas com uma table muito pequena.
