SQL Server 2014: Melhorias de performance no TEMPDB

Já a algum tempo eu acompanho o blog de engenheiros de SQL CSS SQL Server Engineers (http://blogs.msdn.com/b/psssql/). Hoje li mais um dos interessantes artigos do Bob Dorr (sempre vale a pena ler o que ele fala) sobre uma mudança de comportamento do TEMPDB no SQL Server 2014. O artigo é SQL Server 2014: TEMPDB Hidden Performance Gem (http://blogs.msdn.com/b/psssql/archive/2014/04/09/sql-server-2014-tempdb-hidden-performance-gem.aspx) algo como “Tesouro escondido de performance no TEMPDB”, em tradução livre.

O artigo em questão trata da mudança de comportamento de um conceito chamado Eager Writer. Trata-se de uma das três formas de se escrever uma página suja para o disco, onde inclui também Lazy Writer e Checkpoint. Mais informações aqui (http://technet.microsoft.com/en-us/library/aa337560(v=sql.105).aspx).

A explicação do comportamento eu deixo para vocês lerem no artigo original. O ponto que vou tratar aqui é justamente a comprovação prática do que está sendo dito. Não que o que o Bob Dorr fale tenha que necessariamente ser testado, na verdade, se ele falou acredite! Rs… Mas, mesmo a título de curiosidade, os testes são válidos. Vamos começar!

A mudança ocorreu para operações de Bulk realizadas no TEMPDB, como por exemplo, um SELECT INTO. Farei alguns testes simples para demonstrar a diferença entre as versões anteriores ao SQL Server 2014. Antes, irei descrever alguns detalhes do ambiente utilizado para os testes.

Servidor 1

Servidor 2

SQL 2012 (11.0.2100) Enterprise Edition

SQL 2014 (12.0.1524) CTP2 Enterprise Evaluation

Max Server Memory Ilimitado

Max Server Memory Ilimitado

Windows Server 2008 R2 SP1

Windows Server 2012

8GB RAM

64GB RAM

8 CPUs

8 CPUs

Levem em consideração que ambos os servidores são utilizados somente para testes, não havendo concorrência com outros processos ou uma carga de trabalho sobrecarregando os recursos do servidor.

Dito isto, vamos aos testes. Abaixo segue a consulta utilizada para gerar o volume de dados para o teste. Limitei o resultado a 100.000 registros para ter uma base comparativa igual.

query_syscolumns

 

 

 

 

A escolha da sys.syscolumns foi totalmente aleatório. A intenção foi de apenas ter um volume significativo de registros para o teste. Em cada um dos servidores foram feitas 5 execuções na sequência, no estado em que o servidor se encontrava. A única configuração alterada foi habilitar as estatísticas de tempo na execução. (SET STATISTICS TIME ON). A cada execução a tabela temporária gerada era dropada e o mesmo SELECT INTO era executado novamente. Abaixo os resultados obtidos em cada servidor.

SQL Server 2012

Microsoft SQL Server 2012 – 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

SQL Server Execution Times:

CPU time = 3229 ms,  elapsed time = 11562 ms.
CPU time = 3276 ms,  elapsed time = 10214 ms.
CPU time = 1966 ms,  elapsed time = 3222 ms.
CPU time = 3042 ms,  elapsed time = 8711 ms.
CPU time = 2808 ms,  elapsed time = 10278 ms.

Tempo médio de execução: 8797 ms.

 

SQL Server 2014

Microsoft SQL Server 2014 (CTP2) – 12.0.1524.0 (X64)
Oct  3 2013 19:00:26
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

SQL Server Execution Times:

CPU time = 1077 ms,  elapsed time = 1075 ms.
CPU time = 1108 ms,  elapsed time = 1298 ms.
CPU time = 1170 ms,  elapsed time = 1357 ms.
CPU time = 1077 ms,  elapsed time = 1081 ms.
CPU time = 1076 ms,  elapsed time = 1074 ms.

Tempo médio de execução: 1177 ms.

 

Já nas comparações individuais podemos perceber o ganho no tempo de execução do SELECT INTO, chegando a ser praticamente 10x mais rápido. Mesmo na terceira execução, onde no SQL Server 2012 foi executado em 3.2 segundos, isso foi 3x mais lento que a mesma execução no SQL Server 2014. Na média, o SQL Server 2014 foi praticamente 8x mais rápido que o SQL Server 2012.

A conclusão que chego é que a alteração neste comportamento do SQL Server gerou um ganho significativo de performance, e o melhor de tudo, não será necessária nenhuma alteração de código, basta migrar suas bases para o SQL Server 2014. Esse exemplo vale também para os mais céticos que não acreditam que o simples fato de migrar de versão pode gerar melhorias para o ambiente. Fica a dica!

 

Bons Estudos!
Erickson Ricci

3 Respostas para “SQL Server 2014: Melhorias de performance no TEMPDB

  1. Mas para o teste se tornar mais confiável, não teria que ser a mesma configuração de servidor? o hardware e o SO pode influenciar e muito no desempenho final.

    • Olá Celso,

      Sim, você tem razão. O melhor seria testar em servidores exatamente iguais. Infelizmente eu não tinha servidores iguais mas com as duas versões do SQL Server (2012 e 2014).

      No entanto, neste caso especificamente o impacto da diferença de servidores não é tão significativo já que os servidores eram apenas de testes e não havia pressão no Buffer Pool e nem uma carga de trabalho excessiva afetando os processadores.

      Mas, como disse e concordo com você, os testes seriam mais precisos e confiáveis se feitos em máquinas com as mesmas configurações.

      Obrigado pelo comentário.
      Erickson Ricci

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s