Temos um banco de dados para um produto com muita gravação. Acabamos de comprar uma nova máquina servidora com um SSD para ajudar. Para nossa surpresa, as inserções não eram mais rápidas do que em nossa máquina antiga com armazenamento muito mais lento. Durante o benchmarking notamos que a taxa de IO exibida pelo processo do SQL Server era muito baixa.
Por exemplo, executei o script encontrado nesta página , exceto que adicionei um BEGIN TRAN e COMMIT ao redor do loop. Na melhor das hipóteses, pude ver o uso do disco atingir 7Mb/s, enquanto a CPU mal chegava a 5%. O servidor tem 64 Gb instalados e está usando 10. O tempo total de execução foi de 2 minutos e 15 segundos para a primeira chamada para cerca de 1 minuto para as chamadas subsequentes. O banco de dados está em recuperação simples e estava ocioso durante o teste. Larguei a mesa entre cada chamada.
Por que um script tão simples é tão lento? O hardware quase não está sendo usado. As ferramentas de benchmarking de disco dedicadas e o SQLIO indicam que o SSD funciona corretamente com velocidades acima de 500 Mb/s para leitura e gravação. Entendo que as gravações aleatórias são mais lentas do que as gravações sequenciais, mas espero que uma inserção simples como esta, em uma tabela sem indexação em cluster, seja muito mais rápida.
Em última análise, nosso cenário é muito mais complexo, mas sinto que preciso primeiro entender um caso simples. Resumindo, nosso aplicativo exclui dados antigos, então usa SqlBulkCopy para copiar novos dados para tabelas de teste, realiza alguma filtragem e, finalmente, usa MERGE e/ou INSERT INTO, dependendo dos casos, para copiar os dados para as tabelas finais.
--> EDIT 1: Segui o procedimento linkado por Martin Smith e obtive o seguinte resultado:
[Wait Type] [Wait Count] [Total Wait (ms)] [T. Resource Wait (ms)] [T. Signal Wait (ms)]
NETWORK_IO 5008 46735 46587 148
LOGBUFFER 901 5994 5977 17
PAGELATCH_UP 40 866 865 1
SOS_SCHEDULER_YIELD 53279 219 121 98
WRITELOG 5 145 145 0
PAGEIOLATCH_UP 4 58 58 0
LATCH_SH 5 0 0 0
Acho estranho NETWORK_IO levar a maior parte do tempo, considerando que não há resultado para exibir e nenhum dado para transferir para outro lugar que não seja para os arquivos SQL. O tipo NETWORK_IO inclui todos os IO?
--> EDIT 2: criei um disco de 20Gb RAM e montei um banco de dados a partir dele. O melhor tempo que tive no SSD é 48s, com o disco RAM caiu para 37 segundos. NETWORK_IO ainda é a maior espera. A velocidade máxima de gravação no disco RAM foi de cerca de 250 Mb/s, embora seja capaz de fazer vários gigabytes por segundo. Ele ainda não estava usando muita CPU, então o que está atrapalhando o SQL?