Por que uma tabela com um Índice Columnstore Clusterizado teria muitos rowgroups abertos?

Com inserções de gotejamento constantes, você pode muito bem acabar com vários rowgroups deltastore abertos. A razão para isso é que quando uma inserção é iniciada, um novo rowgroup é criado se todos os existentes estiverem bloqueados. De Stairway a Columnstore Índices Nível 5: Adicionando Novos Dados a Índices Columnstore

Qualquer inserção de 102.399 ou menos linhas é considerada uma "inserção de gotejamento". Essas linhas são adicionadas a um deltastore aberto se houver um disponível (e não bloqueado), ou então um novo rowgroup deltastore é criado para elas.

Em geral, o design do índice columnstore é otimizado para inserções em massa e, ao usar inserções trickle, você precisará executar a reorganização periodicamente.

Outra opção, recomendada na documentação da Microsoft, é passar para uma tabela de preparo (heap) e, quando tiver mais de 102.400 linhas, insira essas linhas no índice de columstore. Consulte Índices Columnstore - Diretrizes de carregamento de dados

De qualquer forma, após a exclusão de muitos dados, uma reorganização é recomendada em um índice columnstore para que os dados sejam realmente excluídos e os rowgroups deltastore resultantes sejam limpos.

Por que uma tabela com um Índice Columnstore Clusterizado teria muitos rowgroups abertos?

Existem muitos cenários diferentes que podem causar isso. Vou passar a responder à pergunta genérica em favor de abordar seu cenário específico, que acho que é o que você deseja.

É possivelmente pressão de memória ou contenção entre a inserção e a exclusão?

Não é pressão de memória. O SQL Server não solicitará uma concessão de memória ao inserir uma única linha em uma tabela columnstore. Ele sabe que a linha será inserida em um rowgroup delta para que a concessão de memória não seja necessária. É possível obter mais rowgroups delta do que se poderia esperar ao inserir mais de 102.399 linhas por INSERTinstrução e atingir o tempo limite de concessão de memória fixo de 25 segundos. Esse cenário de pressão de memória é para carregamento em massa, não para carregamento lento.

Bloqueios incompatíveis entre DELETEe INSERTé uma explicação plausível para o que você está vendo com sua mesa. Lembre-se de que não faço inserções lentas na produção, mas a implementação de bloqueio atual para excluir linhas de um rowgroup delta parece exigir um bloqueio UIX. Você pode ver isso com uma demonstração simples:

Jogue algumas linhas no armazenamento delta na primeira sessão:

DROP TABLE IF EXISTS dbo.LAMAK;

CREATE TABLE dbo.LAMAK (
ID INT NOT NULL,
INDEX C CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.LAMAK
SELECT TOP (64000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Exclua uma linha na segunda sessão, mas não confirme a alteração ainda:

BEGIN TRANSACTION;

DELETE FROM dbo.LAMAK WHERE ID = 1;

Bloqueios para o DELETEpor sp_whoisactive:

<Lock resource_type="HOBT" request_mode="UIX" request_status="GRANT" request_count="1" />
<Lock resource_type="KEY" request_mode="X" request_status="GRANT" request_count="1" />
<Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="OBJECT.INDEX_OPERATION" request_mode="S" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="ROWGROUP" resource_description="ROWGROUP: 5:100000004080000:0" request_mode="UIX" request_status="GRANT" request_count="1" />

Insira uma nova linha na primeira sessão:

INSERT INTO dbo.LAMAK
VALUES (0);

Confirme as alterações na segunda sessão e verifique sys.dm_db_column_store_row_group_physical_stats:

Um novo rowgroup foi criado porque a inserção solicita um bloqueio IX no rowgroup que ele altera. Um bloqueio IX não é compatível com um bloqueio UIX. Esta parece ser a implementação interna atual, e talvez a Microsoft a mude com o tempo.

Em termos do que fazer e como corrigi-lo, você deve considerar como esses dados são usados. É importante que os dados sejam o mais compactados possível? Você precisa de uma boa eliminação de rowgroup na [CreationDate]coluna? Tudo bem se novos dados não aparecerem na tabela por algumas horas? Os usuários finais prefeririam que as duplicatas nunca aparecessem na tabela em vez de existirem nela por até quatro horas?

As respostas para todas essas perguntas determinam o caminho certo para abordar o problema. Aqui estão algumas opções:

1. Execute um REORGANIZEcom a COMPRESS_ALL_ROW_GROUPS = ONopção no columnstore uma vez por dia. Em média, isso significa que a tabela não excederá um milhão de linhas no armazenamento delta. Essa é uma boa opção se você não precisar da melhor compactação possível, não precisar da melhor eliminação de rowgroup na [CreationDate]coluna e quiser manter o status quo de excluir linhas duplicadas a cada quatro horas.

2. Divida o DELETEem separado INSERTe DELETEdeclarações. Insira as linhas a serem excluídas em uma tabela temporária como primeira etapa e exclua- TABLOCKXas na segunda consulta. Isso não precisa estar em uma transação com base em seu padrão de carregamento de dados (somente inserções) e no método que você usa para localizar e remover duplicatas. A exclusão de algumas centenas de linhas deve ser muito rápida, com boa eliminação na [CreationDate]coluna, o que você obterá com essa abordagem. A vantagem dessa abordagem é que seus rowgroups compactados terão intervalos apertados para [CreationDate], supondo que a data dessa coluna seja a data atual. A desvantagem é que suas inserções trickle serão bloqueadas por talvez alguns segundos.

3. Grave novos dados em uma tabela de preparo e libere-os no columnstore a cada X minutos. Como parte do processo de limpeza, você pode pular a inserção de duplicatas, para que a tabela principal nunca contenha duplicatas. A outra vantagem é que você controla a frequência com que os dados são liberados para que você possa obter rowgroups da qualidade desejada. A desvantagem é que novos dados serão atrasados ​​para aparecer na [dbo].[NetworkVisits]tabela. Você pode tentar uma visualização que combine as tabelas, mas você deve ter cuidado para que seu processo de liberação de dados resulte em uma visualização consistente dos dados para os usuários finais (você não deseja que as linhas desapareçam ou apareçam duas vezes durante o processo).

Por fim, não concordo com outras respostas de que um redesenho da tabela deva ser considerado. Você está inserindo apenas 9 linhas por segundo em média na tabela, o que não é uma taxa alta. Uma única sessão pode fazer 1.500 inserções singleton por segundo em uma tabela columnstore com seis colunas. Você pode querer mudar o design da tabela assim que começar a ver números em torno disso.

Isso parece um caso extremo para os índices de armazenamento de colunas em cluster e, no final, é mais um cenário HTAP sob a consideração atual da Microsoft - o que significa que um NCCI seria uma solução melhor. Sim, imagino que perder a compactação Columnstore no índice clusterizado seria muito ruim em termos de armazenamento, mas se seu armazenamento principal for Delta-Stores, você estará executando não compactado de qualquer maneira.

Também:

• O que acontece quando você diminui o DOP das instruções DELETE?
• Você tentou adicionar índices não clusterizados Rowstore secundários para diminuir o bloqueio (sim, haverá impacto na qualidade da compactação)