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

Database States e suas interações

Olá,

O estado do banco de dados (do inglês, state) vai definir algumas de suas características, tais como acessibilidade e disponibilidade dos dados. Todo banco de dados SQL Server está em um determinado estado. No SQL Server, um banco de dados pode assumir 7 estados diferentes, sendo eles:

  • ONLINE
  • OFFLINE
  • RESTORING
  • RECOVERING
  • RECOVERY PENDING
  • SUSPECT
  • EMERGENCY

A descrição de cada um destes estados pode ser encontrada neste link (http://technet.microsoft.com/en-us/library/ms190442.aspx) do BOL (Books Online para quem está se habituando agora com estes termos – em outras palavras, é o Help do SQL Server).

O ponto que quero destacar aqui é como os estados se um banco de dados interagem. A troca de um estado pode ocorrer de duas formas: através de ação manual por parte do usuário ou automaticamente através de uma ação do próprio SQL Server.

Por exemplo, o DBA pode a qualquer momento alterar o estado de um banco de dados para OFFLINE, procedimento realizado manualmente. Por outro lado, no momento que essa base for alterada para ONLINE, o SQL Server a altera para um estado intermediário (RECOVERING), onde o processo de recovery (ANALYSIS-REDO-UNDO, tópico para outro post) é realizado. Se este processo for realizado com sucesso e o SQL Server não detectar nenhum erro, o SQL Server automaticamente altera o estado da base para ONLINE.

A importância de se conhecer a interação entre estes estados está no fato de auxiliar o DBA a saber a origem daquele estado e talvez lhe ajudar a identificar algum tipo de problema.

Um simples consulta pode retornar o estado atual de um banco de dados:

Consulta na sys.databases

Consulta na sys.databases

 

A imagem abaixo lista todas as possíveis interações entre os estados de um banco de dados.

Interações entre os estados do banco de dados

Interações entre os estados do banco de dados

 

Bons estudos!

Erickson Ricci

Comandos DBCC

Olá,

O SQL Server possui uma série de comandos chamados DBCC (Database Console Commands). Mais informações aqui (http://technet.microsoft.com/pt-br/library/ms188796.aspx)

Estes comandos são divididos em quatro categorias:

  • Manutenção (Maintenance)
  • Diversos (Miscellaneous)
  • Informação (Informational)
  • Validação (Validation)

Existem 30 comandos documentados. (Vide link da documentação oficial acima). Porém, existem muitos outros comandos DBCC não-documentados. (e obviamente não suportados).

Para poder visualizar todos os comandos DBCC é preciso habilitar um trace flag. Até o SQL Server 2005 você poderia utilizar o trace flag 2520 mas depois desta edição passou a valer o trace flag 2588, conforme explicado pelo Paul Randal (autor da alteração) neste post (https://www.sqlskills.com/blogs/paul/dbcc-writepage/).

Há um dos comandos DBCC que lista todos os outros: DBCC HELP. Nele você pode informar o nome do comando DBCC que você deseja visualizar, onde será informado os parâmetros utilizados pelo comando, ou informar o parâmetro ‘?’, para listar todos os comandos.

Para visualizar todos os comandos DBCC, execute o script abaixo:

DBCC TRACEON(2588)

GO

DBCC HELP(‘?’)

GO

 

O DBCC TRACEON é utilizado para habilitar o trace flag 2588 somente para a sessão atual que está executando estes comandos. Uma vez que você visualizar todos os comandos DBCC, escolha um deles, e execute o DBCC HELP para visualizar os parâmetros de execução daquele comando, conforme exemplo abaixo. Eu escolhi o comando CHECKDB:

 

DBCC HELP(‘CHECKDB’)

GO

 

O resultado foi o seguinte:

 

dbcc CHECKDB

(

    { ‘database_name’ | database_id | 0 }

    [ , NOINDEX

    | { REPAIR_ALLOW_DATA_LOSS

    | REPAIR_FAST

    | REPAIR_REBUILD

    } ]

)

    [ WITH

        {

            [ ALL_ERRORMSGS ]

            [ , [ NO_INFOMSGS ] ]

            [ , [ TABLOCK ] ]

            [ , [ ESTIMATEONLY ] ]

            [ , [ PHYSICAL_ONLY ] ]

            [ , [ DATA_PURITY ] ]

            [ , [ EXTENDED_LOGICAL_CHECKS  ] ]

        }

    ]

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Você vai perceber que para comandos não documentados a saída do DBCC HELP é ligeiramente diferente:

 

dbcc buffer ( {‘dbname’ | dbid} [, {‘objname’ | objid} [, number [, printopt={0|1|2} ][, dirty | io | kept | rlock | ioerr | hashed ]]])

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

ATENÇÃO: Como é de conhecimento de muitos, o SQL Server possui diversos comandos não-documentados, e como tal, não são suportados pelo suporte Microsoft. Portanto, tenha muito cuidado e atenção ao executar um destes comandos e evite “testar” estes comandos em ambiente de produção. Outro ponto importante é que já que não são suportados, seu funcionamento e existência podem ser alterados a cada versão do SQL Server.

 

Eu realizei alguns testes em quatro versões diferentes do SQL Server. SQL Server 2008 (10.0.4000), SQL Server 2008 R2 (10.50.2789), SQL Server 2012 (11.0.3000) e SQL Server 2014 (CTP2) (12.0.1524).

 

Não há diferenças entre as versões 2008 e 2008 R2. Os seguintes comandos existiam nestas versões e foram removidos para as próximas:

 

DBCC ACTIVECURSORS

DBCC DBRECOVER

DBCC ICECAPQUERY

DBCC LATCH

DBCC METADATA

DBCC MOVEPAGE

DBCC PRTIPAGE

DBCC SHOWDBAFFINITY

DBCC SHOWTABLEAFFINITY

No SQL Server 2012 surgiram 3 novos comandos:

 

DBCC CRASHDB

DBCC CSINDEX

DBCC READBYTES

E ainda no SQL Server 2014 surgiu mais um comando:

 

DBCC FLUSHQUERYSTORE

 

Todos os comandos removidos ou incluídos são não-documentados.

 

Apesar de existirem muito mais comandos não-documentados do que documentados, muitos comandos são frequentemente utilizados e “documentados” pela comunidade técnica, como por exemplo, DBCC PAGE, DBCC IND, DBCC EXTENTINFO, entre outros.

Referências: https://www.sqlskills.com/blogs/paul/category/dbcc/ http://technet.microsoft.com/pt-br/library/ms188796.aspx

Bons estudos!!! Erickson Ricci