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?
Eu sei que é uma pergunta antiga, mas isso ainda pode ajudar os pesquisadores e é um problema que aparece de vez em quando.
A principal razão pela qual você está atingindo um teto de desempenho sem ver nenhum gargalo de recurso é porque você atingiu o limite do que é possível processar em um único encadeamento de sessão. O loop não é processado em paralelo, mas todas as inserções são feitas em série.
No meu caso, leva 36 segundos para inserir 3 milhões de linhas. Isso significa 36/30000000 = 0,000012 segundos por linha. Isso é muito rápido. No meu sistema, basta 0,000012 para passar por todas as etapas necessárias.
A única maneira de fazê-lo mais rápido é iniciar uma segunda sessão em paralelo.
Se eu iniciar 2 sessões em paralelo, ambas fazendo 15 milhões de inserções. Ambos terminam em 18 segundos. Eu poderia expandir mais, mas minha configuração de teste atual está atingindo 95% da CPU com duas sessões paralelas, portanto, fazer 3 distorceria os resultados, pois atingiria um gargalo da CPU.
Se eu iniciar 2 sessões paralelas, ambas inserindo 3 milhões de linhas, ambas terminarão em 39 segundos. então agora são 6 milhões de linhas em 39 segundos.
Ok, isso ainda nos deixa com a espera NETWORK_IO aparecendo.
As esperas NETWORK_IO são adicionadas pelo fato de você estar usando eventos estendidos para rastreá-los. No meu caso, a inserção leva 36 segundos (em média). Ao usar o modo de evento estendido (do link acima no primeiro comentário), isso é o que é registrado:
Você pode ver que 68 segundos de NETWORK_IO estão registrados. Mas, como o loop de inserção é uma ação de encadeamento único que leva 36 segundos, isso não pode ser. (Sim, vários threads são usados, mas as operações são seriais, nunca em paralelo, então você não pode acumular mais tempo de espera do que a duração total da consulta)
Se eu não usar eventos estendidos, mas apenas as estatísticas de espera DMVs em uma instância silenciosa (com apenas eu executando a inserção), recebo isto:
Portanto, o NETWORK_IO que você estava vendo no log de eventos estendidos não estava relacionado ao seu loop de inserção. (Se você não ativasse o nocount, teria grandes esperas de IO de rede assíncrona, +1 Martin)
No entanto, não sei por que o NETWORK_IO aparece no rastreamento de evento estendido. Certamente, a gravação em um destino de arquivo assíncrono dos eventos acumula ASYNC_NETWORK_IO, mas certamente tudo isso é feito em um SPID diferente daquele que estamos filtrando. Eu posso fazer isso como uma nova pergunta para mim mesmo)
Normalmente, você começa olhando
sys.dm_exec_requests, especificamente parawait_time,wait_typeewait_resourcepara sua(s) solicitação(ões) INSERT. Isso dará uma indicação clara do que está bloqueando seu INSERT. Os resultados indicarão se há contenção de bloqueio, eventos de crescimento de arquivo, esperas de descarga de log, contenção de alocação (manifesta como contenção de trava de página PFS) etc etc etc. Depois de medir, atualize sua pergunta de acordo. Eu recomendo fortemente que você pare agora e leia a metodologia de solução de problemas de esperas e filas antes de prosseguir.Executei o script de teste na página vinculada no OP com o BEGIN TRAN / COMMIT ao redor do loop. Na minha máquina, demorou 1:28 para concluir a primeira vez.
Em seguida, movi esses dois comandos para fora do loop:
Ele completou em 28 segundos depois disso.
Não sei ao certo o que está acontecendo, mas acho que pode haver algum tipo de suspensão no
RAND()código, talvez como parte do algoritmo que eles estão usando para gerar entropia (melhores números aleatórios).FWIW, os SSDs nem sempre são a melhor tecnologia para aplicativos pesados de gravação. Para obter o melhor desempenho, certifique-se de que o log do banco de dados esteja em uma letra de unidade diferente dos dados do banco de dados, que o arquivo de log tenha crescido previamente até seu tamanho máximo e nunca trunque o log.
Outro DMV que uso para identificar lentidão é o sys.dm_os_waiting_tasks . Se sua consulta não for intensiva em CPU, você poderá encontrar mais informações sobre as esperas deste DMV.
Estou verificando a lista de eventos de espera para sql 2008 e não vejo NETWORK_IO listado: http://technet.microsoft.com/en-us/library/ms179984(v=sql.100).aspx
Achei que NETWORK_IO agora estava apenas listado como ASYNC_NETWORK_IO, então gostaria de perguntar se você poderia verificar sua versão do SQL novamente, porque estou simplesmente curioso para saber como/por que esse evento de espera está aparecendo para essa versão.
Quanto à espera da rede, sim, isso pode acontecer mesmo se você estiver trabalhando em um servidor autônomo. Você verificou as configurações de suas placas de rede? Eu estou querendo saber se eles são um problema.
No final do dia, existem apenas alguns gargalos de recursos possíveis: memória, CPU, E/S de disco, rede e bloqueio. Você indicou que a CPU e a E/S não são o problema e tem um evento de espera de NETWORK_IO, portanto, sugiro que verifique primeiro essas placas NIC.