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

Como configurar a quantidade de arquivos do errorlog

Olá,

Por padrão o SQL Server mantém apenas 6 arquivos históricos do errorlog. Se o SQL Server for reiniciado será gerado um arquivo de errorlog novo e o último arquivo (mais antigo) será descartado.

Há uma maneira simples de configurar esta quantidade de arquivos. Através do Management Studio, vá até a pasta Management, SQL Server Logs, e clique com o botão direito na pasta. Acesse o menu Configure. (Veja imagem abaixo)

image1A tela a seguir será apresentada:

image2Nesta tela você poderá alterar a quantidade de arquivos de errorlog que o SQL Server irá manter.

Lembre-se que, se você quiser, você pode “reciclar” o atual arquivo do errorlog executando manualmente a procedure sp_cycle_errorlog.

 

Bons Estudos!
Até mais.

Erickson Ricci

Entenda o erro 18056 – The client was enable to reuse a session with SPID ##

Olá,

Todo DBA já deve ter visto alguma vez na vida o erro 18056. E se viu, provavelmente teve dificuldades para entendê-lo e tratá-lo. Por conta de todas as dúvidas e mal-entendimentos sobre este erro, alguns engenheiros de escalação da Microsoft fizeram alguns posts para esclarecer todas estas dúvidas.

Deixarei aqui a referência de todos os artigos relacionados a este erro.

How It Works: Error 18056 – The client was unable to reuse a session with SPID ##, which had been reset for connection pooling

How It Works: Error 18056 – The client was unable to reuse a session – Part 2

Error 18056 can be unwanted noise in certain scenarios

Breaking Down 18065

Todos estes artigos ( e todos os outros do blog ) são altamente recomendados. Leitura essencial para um bom DBA.

 

Bons estudos! Enjoy!!!
Erickson Ricci

Session levels – Como medir o nível técnico de palestras e treinamentos

Olá,

Hora de retomar alguns artigos por aqui … 

Vocês já pararam para pensar no significado dos níveis das palestras e treinamentos que normalmente participamos? Palestra XPTO de SQL Server 2012 (Nível 300). Mas o que exatamente significa “Nível 300″ ?!

Por um bom tempo eu procurei por uma explicação decente para estes níveis mas até esses dias eu não havia encontrado nada que me explicasse estes níveis direito… Até que encontrei um artigo que explicasse de maneira simples e direta. (Você pode ler o artigo original AQUI ). Vou descrever o meu entendimento de cada nível abaixo:

  • Nível 100 (Básico) – Considera que você já possui algum conhecimento, não necessariamente experiência prática. Sugere que você tenha menos de um ano com experiência com o produto… Em outras palavras, você conhece o SQL Server e até se arrisca a rodar algumas queries, mas se perguntarem onde você inicia o serviço do SQL você já pode ter dúvidas… =)
  • Nível 200 (Intermediário) – Agora você já conhece o produto e tem experiência com ele (1 a 3 anos). É hora de conhecer novas features. Já podemos falar de tuning de índices, configurações do SQL, monitoramento, etc, etc, etc…
  • Nível 300 (Avançado) – Aqui já se tem um profundo conhecimento do produto e sua utilização. Já podemos falar um pouco de internals, alta disponibilidade, soluções mais complexas de códigos/arquitetura, etc. Você provavelmente já seria um DBA pleno/senior neste nível. Considera-se um tempo de experiência de 4 a 6 anos.
  • Nível 400 (Expert ou SET GOING_CRAZY_MODEL ON) – Quem sabe, sabe. Aqui se fala do funcionamento detalhado de funções e componentes internos do produto, podemos falar de scheduling, troubleshooting avançado, disaster recovery descrevendo detalhadamente o que acontece internamente, etc… Diz-se apropriado para quem possui mais de 6 anos de experiência com o produto.
  • Nível 500 (tem mais?! …. Não-Oficial ou SET INSANE_MODE ON) – Se você achou o nível 400 muito Deep Dive, este nível é Deep Deep Deep Deep Dive… rs. Em resumo, é tenso! Pessoas como Bob Ward, Paul Randal, Itzik Ben-Gan, Paul White e outros se encontram por aqui para trocar idéias… 

Um ponto de atenção, não pense que porque você vai a um evento (PASS Summit, por exemplo) e tem uma palestra de nível 200 e que você não vai aprender nada de interessante pois você já “se considera” como nível 300 ou 400… não se engane. Há muitas palestras de nível 200/300 por ai que são fantásticas.

Muitas vezes as palestras variam o nível também. A média da palestra pode ser 200 mas o palestrante pode te dar umas dicas ou demonstrar exemplos de nível 300/400 no meio da palestra… fique atento!

Espero que a informação seja útil.

 

Enjoy!