Preciso ter uma forma de inserir em uma tabela, rápida, síncrona, com duração mínima. O que eu tentei ("cego") é:
- Não tem nenhum índice na mesa
- Alternar para registro simples (de registro completo)
O cenário de teste que uso consiste em 100 conexões, cada uma executando um INSERT e, em seguida, aguardando 0,1 segundo e, novamente, indefinidamente. Cada "insersor" registra o tempo de execução.
Olhando para os tempos de execução, às vezes vejo 1,5 e até 10 segundos em uma inserção (como casos excepcionais), caso contrário, 0,2 segundos típicos é o que vejo.
Contexto adicional:
- SQL Server 2008 R2 Express (de fato, a versão expressa limita apenas o tamanho do banco de dados (4 GB), RAM (1 GB) e CPUs lógicas (1))
- Máquina: Laptop com HDD de 7200 RPM, 8 GB de RAM e 8 CPUs lógicas, hospedando o servidor e os clientes
- A tabela consiste em 1 ID BigInt (identidade), um campo NVARCHAR(100) e NVARCHAR(MAX) (INSERTs neste têm uma carga útil de 10k) e uma(s) coluna(s) NVARCHAR(MAX) (carga útil de 30k em INSERT)
Onde devo procurar melhorias adicionais no desempenho de tal processo?
Muitas pessoas adicionaram muitos pontos positivos nos comentários.
1) Separe seus logs de transação em uma unidade diferente. Isso vai ser difícil com um laptop. Se você não puder fazer isso, compre um SSD para o laptop, e isso deve tornar sua vida consideravelmente melhor.
2) Pré-aumente seus dados e arquivos de log para um valor alvo. Se você espera adicionar 1 GB de dados ao seu banco de dados, crie seus dados e arquivos de log com pelo menos 1,5 GB para começar. O crescimento automático de dados e arquivos de log são assassinos para o desempenho e pode muito bem ser o motivo pelo qual você vê esses "engasgos" de 10 segundos no desempenho. O SQL Management Studio possui alguns relatórios integrados (acredito que você clique com o botão direito do mouse no banco de dados e selecione Relatórios -> Uso do disco), que deve ter uma tabela de todos os seus eventos de crescimento automático.
3) Se você puder agrupar suas inserções por cliente, faça-o usando a instrução SqlBulkCopy ou BULK INSERT.
4) Não deve haver razão para que suas tabelas não tenham uma chave primária agrupada. O SQL Server deve ser capaz de lidar com um índice clusterizado em sua coluna IDENTITY de maneira razoavelmente eficiente.
Meu conselho é sair de um laptop. Coloque isso em uma máquina real, com drives rápidos e muita memória RAM.