Digamos que eu tenha uma única tabela que armazena informações de rastreamento de dispositivos móveis (cada registro possui deviceID, informações de transação, uma coluna json com detalhes completos sobre a operação, tempo de operação, usuário, etc...), este é o banco de dados operacional . Alguns usuários podem precisar obter informações de rastreamento de um intervalo de tempo, sobre um determinado usuário ou dispositivo. Portanto, uma simples seleção com filtros não afetará a inserção de dados.
Alguns outros usuários precisarão realizar operações analíticas maiores, agregações ao longo de meses de dados coletados. Li que esse processo de análise deveria ser feito em um banco de dados analítico, para não impactar o banco de dados operacional.
Mas tenho um limite, apenas uma instância do SQLServer disponível.
Então eu deveria escrever um ETL que faça uma cópia periódica em lote dos dados em um novo banco de dados, com a mesma tabela única com mais alguns índices e sem a coluna json (para economizar espaço, porque para o tipo de análise que precisamos é inútil)
Agora minha pergunta é, dado que estamos falando de uma tabela operacional e uma tabela de análise (mais do que um banco de dados operacional e um banco de análise), e nas instalações do meu cliente terei apenas uma única instância do SQLServer, faz sentido manter ambas as tabelas dentro do mesmo banco de dados na mesma instância do SQLServer? Melhorei o desempenho ao usar uma única tabela para consultas operacionais e analíticas? Dizendo de outra forma, tenho duas opções, usando uma instância do SQLServer:
- inserir dados em uma tabela com todos os índices necessários e consultar essa tabela para rastreamento E para análise
- insira dados em uma tabela com poucos índices e copie-os para outra tabela com mais índices e um tamanho geral reduzido e consulte a primeira para rastreamento e a segunda para análise
Se você me disser que se eu colocar tudo no mesmo SQLServer, 1 e 2 não serão muito diferentes em termos de performance, vou ficar com 1 porque é mais fácil.
Se eu fizer agregações de alguns minutos na tabela analítica, a escrita na tabela operacional ficará menos lenta ou o mesmo acontecerá com a análise diretamente na tabela operacional?
Obrigado
Conforme já mencionado nos comentários, a resposta sobre qual implementação teria melhor desempenho para você dependerá dos casos de uso específicos e da proporção de leitura para gravação da tabela em seu banco de dados, bem como da contenção geral geral de recursos.
opção 2
Se houver muitas leituras e gravações simultâneas na tabela, será possível que elas bloqueiem e bloqueiem umas às outras (com o nível de isolamento padrão no SQL Server). Ter uma segunda instância da tabela que dissocie (se possível) a sobreposição de casos de uso de leitura e gravação pode melhorar o desempenho em relação ao bloqueio, nesse cenário. Normalmente, é bom isolar a segunda instância em um servidor separado para dissociar também a contenção de recursos, mas, como você observou, isso não é possível no seu caso.
Mas escrever os mesmos dados duas vezes não é gratuito. E, novamente, estar no mesmo servidor utilizará o dobro dos recursos que você usaria anteriormente se você mantivesse as coisas em uma única tabela. E para maximizar o benefício de dissociar os casos de uso de leitura e gravação, você provavelmente desejaria gravar os dados em lotes, em um momento diferente do momento em que ocorrem os casos de uso de leitura. Isso deixaria sua cópia da tabela obsoleta, em vez de estar sincronizada em tempo real com a tabela original. Há também manutenção extra para manter a estrutura da tabela sincronizada à medida que ocorrem alterações no esquema no original. Não é uma tarefa simples.
Opção 1
Por outro lado, se você não tiver muitas leituras e gravações simultâneas, ou se houver muita simultaneidade, mas não muita consulta e contenção de recursos em geral, então uma única instância da tabela, indexada adequadamente para a maior parte de seus principais casos de uso, pode funcionar perfeitamente. O tempo de bloqueio pode variar de milissegundos a nanossegundos, imensurável no que diz respeito à sua aplicação.
Opção 0
Além de tudo isso, uma terceira opção alternativa é alterar o nível de isolamento do banco de dados para algo mais propício à simultaneidade otimista. No SQL Server, as opções para isso são Read Committed Snapshot Isolation (RCSI) e apenas Snapshot Isolation . Resumindo, a simultaneidade otimista geralmente permite que consultas de leitura e gravação operem na mesma tabela simultaneamente, sem bloquear uma à outra. Os leitores não bloqueiam os escritores e os escritores não bloqueiam os leitores. (Os gravadores ainda bloquearão outros gravadores.) Isso funciona porque um armazenamento de versão mantém cada versão de um registro em alteração ativa na tabela. Quando uma consulta de gravação está fazendo uma alteração em uma linha, sua versão anterior fica disponível para leitura até que a alteração de gravação seja confirmada e o bloqueio nela seja liberado.
Ambas as opções para níveis de isolamento de simultaneidade otimista têm diferentes prós e contras (conforme discutido nos artigos vinculados), mas eu prefiro o RCSI, que permite globalmente a simultaneidade otimista no servidor. Isso é semelhante ao modo como o Banco de Dados SQL do Azure e alguns outros sistemas de banco de dados modernos funcionam imediatamente. E ativá-lo é essencialmente ativar uma propriedade do banco de dados com uma única linha de código.