Eu tenho feito alguns testes de um novo servidor (virtual) que substitui um servidor de produção existente. Suspeitamos que o servidor de produção atual está sobrecarregado e, portanto, estamos ajustando os componentes virtuais do novo servidor (RAM, CPUs, etc.) de um nível baixo, até que o desempenho seja adequado para lidar com a carga de trabalho atual.
Embora apreciando que o verdadeiro teste da capacidade do novo servidor de lidar com a carga de trabalho do servidor existente é testar essa carga de trabalho no novo servidor, fiz alguns testes simples e arbitrários no servidor existente e no novo para começar por interesse:
- Restaurar banco de dados - velocidade de leitura
- Banco de dados de backup - velocidade de gravação
- DBCC CHECKDB em um banco de dados grande - tempo gasto
(o mesmo backup de banco de dados foi usado em ambos os servidores para que todos os testes acima fossem consistentes)
Todos os itens acima foram a favor do novo servidor (leituras e gravações mais rápidas, CHECKDB mais rápido)
O teste básico final que fiz foi testar o tempo necessário para executar um simples SELECT *
em uma das maiores tabelas em um cache frio e em um cache quente para obter uma indicação adicional da velocidade de leitura.
O código para o teste está abaixo do qual rodei nos dois servidores
USE StackOverflow
SET STATISTICS IO, TIME ON
CHECKPOINT
DBCC DROPCLEANBUFFERS
SELECT * FROM Users /* cold cache run */
SELECT * FROM Users /* warm cache run */
O IO estatístico é o seguinte:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 12 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 266 ms.
(14080580 rows affected)
Table 'Users'. Scan count 1, logical reads 250190, physical reads 1, page server reads 0, read-ahead reads 250200, page server read-ahead reads 0, lob logical reads 1387, lob physical reads 58, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 24047 ms, elapsed time = 508456 ms.
(14080580 rows affected)
Table 'Users'. Scan count 1, logical reads 250190, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 1385, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 23297 ms, elapsed time = 478627 ms.
Os resultados foram semelhantes em ambos os meus servidores, no entanto, o que me impressionou foi que o tempo decorrido foi apenas 30 segundos mais lento ao ler do disco, em vez da RAM.
Meu entendimento de por que o SQL Server lê tudo no pool de buffers foi que a RAM é mais rápida que o disco - neste teste, a RAM foi apenas 6% mais rápida.
Com base no exposto, se minha consulta "da vida real" estiver a 2.000 milissegundos do cache, a leitura dos dados da RAM levará 3.200 milissegundos. Indiscutivelmente, esta não é uma diferença perceptível.
Por que, então, nos esforçamos para garantir os dados e que os servidores tenham o máximo de RAM possível para armazenar dados em cache quando parecem ser apenas 6% mais rápidos?
Minha pergunta é deliberadamente irônica, pois sinto que há algo que estou ignorando aqui e aprecio que meu teste seja de natureza muito simples.
Pensamentos sobre coisas que estou negligenciando:
- A contenção de disco em um servidor ocupado é reduzida, se os dados estiverem na RAM
- Eu aprecio que a RAM seja usada para outras coisas no SQL Server, em vez de apenas o pool de buffers
- O SQL Server provavelmente teve essa arquitetura desde os primeiros dias, quando os discos eram muito mais lentos que os SSDs modernos
A consulta de teste que você executou para medir a diferença de um cache frio e quente passará a maior parte do tempo de execução aguardando ASYNC_NETWORK_IO . O envio de 14 milhões de linhas para o SSMS provavelmente leva cerca de 450 segundos no seu caso. Você provavelmente descobrirá que a consulta de cache frio demora cerca de duas vezes mais do que a consulta de cache quente se você subtrair esse tempo de espera.
O SQL Server também possui vários truques para reduzir o tempo de espera de E/S. Um desses truques é a leitura antecipada . O tipo de consulta que você executou provavelmente se beneficiará bastante com a leitura antecipada. Quase todo o I/O foi feito através do mecanismo de leitura antecipada:
Nem todas as consultas poderão obter uma vantagem tão grande das leituras antecipadas. Por acaso escrevi um post recente no blog que tem um exemplo de desempenho de consulta muito ruim devido a esperas de E/S. Reproduzindo as seções relevantes:
Voltando ao seu sistema, você pode considerar emitir um
COUNT_BIG(*)
contra a tabela. Isso reduzirá o tempo de espera do cliente para quase nada e mostrará mais diretamente a diferença entre um cache quente e um cache frio. Se você quiser testar a E/S no novo servidor sem o benefício de leituras antecipadas, tente executar o código de demonstração mencionado nesta postagem do blog :Além do post de Joe:
O backup e a restauração não usam o Buffer Pool (BP). Checkdb provavelmente lê tantos dados, então o potencial de reutilização do cache não é alto.
Quanto ao seu SELECT: quantos dados ele lê em comparação com quanto você cabe no cache?
Além disso, qual é o seu padrão de carga neste SQL Server?
O que você provavelmente deseja fazer é ter uma carga na qual você lê uma quantidade realista de dados, com chance de reutilização de cache. Você pode jogar com a memória máxima do servidor, ajustá-lo para cima e para baixo para ajustar a quantidade de dados que você pode ter no BP e com base nisso ver a diferença. Execute isso em ambos os servidores, com uma memória de servidor máxima alta e baixa.
Acima levará muito mais tempo do que o que você já fez. Talvez um dia ou dois para criar a carga de trabalho. Então, no final, tudo se resume a saber se você acha que vale a pena esse tempo.
Se você deseja um ponto de partida com uma carga que faz pesquisas de índice nas quais você tem tabelas largas e estreitas e também consultas com alta e baixa seletividade, você pode usar a carga que desenvolvi para minha série de postagens de blog "a fragmentação importa". Você teria que ajustá-lo para atender às suas necessidades, mas pode economizar algum tempo: http://sqlblog.karaszi.com/fragmentation-the-final-installment/
Para uma carga de trabalho OLAP, ter um bufferpool grande por si só pode não fornecer desempenho aprimorado. Uma mesa de 1 TB nunca caberá em 200 GB de RAM, não importa como você a veja. Portanto, alguma parte dessa tabela terá que ser extraída do disco em cada varredura. Mas ter toda essa RAM ainda é útil. Com grandes tabelas vêm grandes compilações de sondas de junção de hash, grandes classificações, grande espaço de trabalho para funções de janelas e grande tudo o mais. Quanto mais daqueles que permanecerem na memória e não vazarem para o disco, mais rápido a carga de trabalho geral será concluída.
Sua observação sobre as raízes arquitetônicas também é válida. Antigamente não era tudo muito mais lento, havia muito menos disso. Dados residiam em disco. Ele foi trazido para a memória para ser processado, e um pouco foi mantido por algum tempo, caso fosse útil. Principalmente o disco é onde os dados viviam. Hoje em dia um servidor OLTP respeitável tem memória comparável ao tamanho do banco de dados. Os dados vivem na memória. O disco está lá apenas para backup, no caso de falha de energia. A arquitetura antiga não atende bem ao novo hardware. Michael Stonebreaker tem muitos artigos fundamentados e opinativos sobre este tópico.