Faz sentido adicionar índices a uma tabela de índice columnstore clusterizado?

Meu entendimento de uma tabela columnstore clusterizada (e corrija-me se estiver errado) é que cada coluna é armazenada de alguma maneira fisicamente ordenada ...

Todas as colunas são classificadas na mesma ordem . Digo isso para distinguir de uma possível leitura de sua afirmação de que cada coluna está classificada da melhor maneira para aquela coluna, o que não é verdade.

Cada coluna é codificada e compactada individualmente em um segmento dentro de cada grupo de linhas . A ordem das operações é:

1. Codifique valores em todas as colunas (por exemplo, deslocamento de valor ou usando um dicionário).
2. Determine a ordenação de linha única 'ótima'.
3. Comprima cada coluna (por exemplo, RLE, pacote de bits).

A ordem de classificação comum escolhida pode ser ótima para alguns segmentos, mas não para outros do ponto de vista da compactação (principalmente codificação de comprimento de execução). O SQL Server usa a tecnologia Vertipaq para escolher uma ordem de classificação que forneça um bom resultado geral de compactação.

...implicando que cada coluna já tem o que equivale a um índice clusterizado nela

Esta não é a imagem mental correta porque os 'índices' de armazenamento de colunas não suportam buscas, apenas b-trees o fazem. O SQL Server pode localizar uma linha individual em um armazenamento de coluna para fins de pesquisa (como nas perguntas e respostas vinculadas), mas isso não é o mesmo que dar suporte a buscas de chave em geral.

Demonstração de uma pesquisa em um índice columnstore clusterizado.

A distinção é entre encontrar a linha #n no grupo de linhas #m versus encontrar linhas com um determinado valor 'x' para uma coluna específica. Um índice de armazenamento de colunas pode fazer o primeiro, mas não o último.

Além disso, os índices b-tree podem retornar dados ordenados; índices de armazenamento de coluna não podem (devido à codificação) -- exceto para o caso trivial de uma pesquisa em que no máximo uma linha será retornada.

Faz sentido adicionar índices a uma tabela de índice columnstore clusterizado?

Índices b-tree adicionais fazem sentido quando precisamos localizar um único valor ou um pequeno intervalo de valores. A alternativa seria varrer o índice de armazenamento de colunas (embora com possível eliminação de grupo de linhas se as estrelas se alinharem corretamente - bem como eliminação de partição se a tabela for particionada).

De forma mais geral, os índices b-tree suportam consultas do tipo OLTP; armazenamento de coluna é para análise. Fornecer ambos na mesma mesa permite uma abordagem híbrida (HTAP - Hybrid Transactional/Analytic Processing).

Do Microsoft Research Paper Columnstore e da árvore B+ – Os designs físicos híbridos são importantes? (PDF):

As árvores B+ superam as colunas de armazenamento quando os predicados de consulta são seletivos mesmo quando todos os dados são residentes na memória; e a troca muda ainda mais em favor das árvores B+ quando os dados não são residentes na memória. Da mesma forma, as árvores B+ podem ser uma opção melhor para fornecer dados em ordem ordenada quando a memória do servidor é restrita. Por outro lado, os columnstores geralmente são uma ordem de magnitude mais rápidos para grandes varreduras, independentemente de os dados serem ou não residentes na memória. Para atualizações, as árvores B+ são significativamente mais baratas. Os armazenamentos de colunas secundárias incorrem em um custo de atualização muito menor em comparação com os índices de armazenamentos de colunas primários, mas ainda são muito mais lentos do que as árvores B+. Este estudo empírico indica que, para determinadas cargas de trabalho, os designs físicos híbridos podem fornecer ganhos de desempenho significativos.

Pode fazer sentido fazê-lo, sim. Às vezes, um requisito de negócios precisará ser aplicado por meio de uma chave primária ou uma restrição exclusiva. Ambos resultam em um índice subjacente em uma tabela com um índice columnstore clusterizado.

Um índice columnstore clusterizado não é uma boa estrutura de dados para alguns tipos de consultas. Exemplos em que um desempenho extremamente ruim é possível incluem agregação de cadeia de caracteres no SQL Server 2016 , consultas recursivas e junções sem uma condição de igualdade. De maneira mais geral, qualquer consulta que exija uma pesquisa rápida de uma pequena quantidade de dados de uma tabela pode não obter o melhor desempenho apenas de um índice columnstore clusterizado. É verdade que a eliminação de rowgroup às vezes pode levar a um desempenho semelhante em relação a um índice não clusterizado, mas isso requer o carregamento dos dados em uma determinada ordem e nem todos os tipos de dados são suportados.

Há desvantagens na criação de índices não clusterizados em tabelas com um índice columnstore clusterizado. A inserção paralela não está mais disponível, os índices ocupam espaço de armazenamento adicional e o SQL Server terá menos opções para ordem de compactação em alguns cenários.

Na minha experiência, descobri que é melhor definir uma barra mais alta do que o normal ao considerar adicionar um índice não clusterizado a uma tabela com um índice columnstore clusterizado. Se você realmente precisa, tente adicioná-lo, mas mantenha as desvantagens em mente.