Existe uma tabela de "consulta em lote" que possui duas colunas (BatchID, RecordID) que são ambas do tipo TSQL 'uniqueidentifier'. A chave de cluster exclusiva está ativada (BatchID, RecordID) e não há índices secundários.
Usamos SqlBulkCopy para inserir rapidamente milhares ou milhões de IDs na tabela em um único ID de lote, para que várias consultas possam se unir a ele para realizar a filtragem usando esse conjunto de IDs de registro associados a esse lote. Essa abordagem é muito mais rápida do que tentar várias consultas que enviam listas de IDs na string de consulta. Assim que as consultas forem concluídas, o lote de IDs de registro será excluído. A tabela fica vazia em média quando nenhuma consulta está em execução, mas pode ter centenas de lotes ativos, pois processamos milhares de solicitações de API por segundo que podem usar a tabela.
Minha pergunta é, que tipo de etapas de manutenção, se houver, são exigidas de um DBA para manter essas tabelas. Por exemplo, ele se beneficiaria da reconstrução periódica do índice ou o SQL Server lidaria com a limpeza das linhas excluídas (liberando blocos de página etc.) sozinho. Além disso, existe alguma configuração especial da tabela ou índice clusterizado que ajudaria, especialmente considerando que os identificadores são Guids aleatórios (identificadores exclusivos).
Geralmente usamos a versão mais recente do SQL Server, Enterprise Edition. Alguns servidores estão no Azure, alguns na AWS.
Também estou interessado em ouvir alternativas. Por exemplo, suponho que essa abordagem seja melhor com uma tabela permanente, mas também posso criar uma tabela temporária local de sessão como parte de uma transação que é excluída quando concluída, em vez de inserir e excluir linhas de uma tabela permanente. Só não sei como isso funcionaria em comparação.
Em minha experiência com o SQL Server 2016 Enterprise Edition em um processo semelhante em que uma tabela permanente é usada como uma fila de mensagens - portanto, tem uma taxa muito alta de inserções e exclusões - descobrimos que a reconstrução de índices a cada 5 a 10 minutos é benéfica para forçar limpeza de registro deletado. Isso melhorou drasticamente o desempenho de leitura na mesa.
Em nosso caso, o nível de isolamento do banco de dados é um instantâneo confirmado de leitura, e o processo de remoção da fila/exclusão estava excluindo registros mais rapidamente do que o processo de limpeza fantasma estava limpando-os. Com contagens altas de registros fantasmas, selecionar um registro às vezes levaria de 1 a 3 segundos, em vez de 5 a 10 ms, porque o SQL estava tendo que ler todos os registros fantasmas, indicados por uma contagem de varredura alta (com
set statistics io on).Para verificar o número de registros fantasmas:
https://learn.microsoft.com/en-us/sql/relational-databases/ghost-record-cleanup-process-guide?view=sql-server-2017#ghost-record-cleanup-task
Acho que a parte "no máximo 10 páginas" foi nosso fator limitante, pois a alta taxa de exclusões excedia a quantidade de páginas que o processo de limpeza poderia tocar em cada execução.
Eu recomendaria usar tabelas com otimização de memória . Pelo que entendi, você não precisa preservar o conteúdo na reinicialização do servidor, portanto, use-o no modo não persistente. Eles geralmente podem ser 5 a 10 vezes mais rápidos do que tabelas temporárias ou variáveis de tabela. - piotr