O Caso dos Schedulers Off-line

Alta utilização de CPU nunca é legal… E como um DBA preguiçoso proativo, é interessante sempre manter esse contador sob controle. No entanto, vira e mexe e aparece um novo procedimento, um cubo ou um deploy da morte que te obriga a realizar uma análise do que está errado. Hoje eu não falarei exatamente sobre a causa deste problema, mas sobre o que o troubleshooting revelou.

O ambiente analisado é um servidor de relatórios que fora migrado recentemente do 2008 R2 para o 2014. Após algumas (poucas) batalhas com o novo Cardinality Estimator, os problemas de desempenho haviam sido sanados e nada mais foi relatado. Isso até o próximo deploy, claro.

Quando o comportamento não usual foi identificado, a consulta culpada foi isolada e mandada de volta para o desenvolvimento. No entanto, algo me deixou curioso sobre esse comportamento: Sempre que o procedimento era disparado, a CPU oscilava, mas nunca ultrapassava a linha de 75~77%. Nesse momento, abri o task manager e vi o seguinte:

CPU Usage

Ué… Só 20 das 26 CPUs estavam trabalhando. Isso geralmente é problema. Então, fui direto na DMV sys.dm_os_schedulers pra investigar e me deparei com isso:

schedulers

What the f…? Seis dos schedulers estão off-line! Como eu sei exatamente o que significa que o scheduler está visível e off-line, fui ao BOL e lá você encontra o seguinte:

OFFLINE schedulers map to processors that are offline in the affinity mask and are, therefore, not being used to process any requests. ONLINE schedulers map to processors that are online in the affinity mask and are available to process threads.

Cara… Alguém mexeu no affinity mask [1] desta instância?! Que mente diabólica arquitetaria plano tão maligno?! Fui confirmar a configuração do mal e…

affinity

Não tou entendendo mais nada… Nesta hora você recorre ao único cara que nunca te esconde nada, o errorlog. E, usando seu novo melhor amigo “Get-SqlErrorLog”:

errorlog

Ok. Este servidor nunca teve problema de licenciamento… Por que ela está sendo limitado a 20 processadores agora? Aí, você sabe que terá de consultar o oráculo. Meio minuto se passa e você descobre através de vários blog posts [2][3] que, desde o SQL Server 2012, o licenciamento mudou e o tipo de licença CAL até permite que seja realizado o upgrade do seu SQL Server Enterprise, mas o limita a 20 processadores lógicos.

Mas é claro que você já sabia disso. Obviamente, você leu isso durante o processo de planejamento da migração de versão, né…?! Não. Eu não li. Se você leu, deixa um recado na sessão de comentários. Quero estudar você ser seu amigo bater um papo contigo.

Enfim, este novo licenciamento parece ter pegado muita gente de surpresa e pode até causar problemas de desempenho por desbalanceamento dos schedulers entre os NUMA nodes [4]!

Então, depois desse post, espero que você entenda a importância do licenciamento e leia sobre todas as suas nuances para cada nova versão do SQL Server.

E, por favor, depois me conte.

[]’s

Links:

[1] affinity mask Server Configuration Option

[2] A cautionary tale about grandfathering CAL licenses in SQL Server 2012 Enterprise

[3] SQL SERVER – Using 20 Logical Processors Based on SQL Server Licensing

[4] Performance Problems with SQL Server 2012 Enterprise Edition Under CAL Licensing

Anúncios

Tabelas Temporárias X Variáveis de Tabela

Venho compartilhar com vocês uma situação que geralmente é motivo de dúvidas. O que devo usar para materializar temporariamente meus dados: uma variável de tabela (i.e. @tabela) ou uma tabela temporária (i.e. #tabela)? Hoje mostrarei um caso onde a melhor escolha foi uma tabela temporária, explicitando as razões que me fizeram optar por ela ao invés de uma variável de tabela. Antes de começarmos, vamos falar um pouco sobre nossas duas opções.

TABELAS TEMPORÁRIAS

Tabelas temporárias tem o símbolo “#” como prefixo. As mais comumente utilizadas são as tabelas temporárias locais que possuem apenas um “#” (i.e. #tabela). No entanto, também é possível cria tabelas temporárias globais, utilizando dois “#” como prefixo (i.e. ##tabela). A diferença entre as duas está no escopo e na visibilidade. Enquanto a tabela local só pode ser acessada pelo criador e apenas dentro da sessão onde foi criada, a tabela temporária global pode ser acessada por qualquer sessão, ou seja, todos podem acessar e alterar seu conteúdo e até remover a tabela.

Tabelas temporárias são criadas e possuem uma representação física no tempdb, apesar de que quando são pequenas – e o SQL Server tem memória sobrando –, suas páginas permanecem na memória. Quando a memória livre é pouca, o SQL Server persiste a tabela em disco.

Em minha opinião, o fato mais importante para escolher entre tabelas temporárias e variáveis de tabela é o SQL Server criar e manter estatísticas de distribuição de dados para as tabelas temporárias da mesma forma como faz para tabelas permanentes. Isso é especialmente importante quando se indexa uma tabela temporária, pois a informação de distribuição permite que se obtenham planos de execução mais eficientes. Também como consequência deste fato, consultas nas tabelas temporárias serão recompiladas caso as estatísticas sejam atualizadas, atinjam o limiar de recompilação (recompilation treshold), etc. Isto não ocorre para variáveis de tabela – a não ser com a opção RECOMPILE –, pois nenhuma estatística é criada ou mantida.

VARIÁVEIS DE TABELA

Variáveis de tabelas geralmente causam confusões por serem pouco compreendidas. Um erro comum é acreditar que essas tabelas residirão apenas em memória, sem representação física. Outro erro comum é achar que se deve sempre optar por variáveis de tabela ao invés de tabelas temporárias.

Assim como tabelas temporárias, variáveis de tabela possuem representação física no tempdb e, quando pequenas – e o SQL Server tendo memória de sobra –, essas tabelas residirão em memória. Já o escopo e a visibilidade dessas tabelas são bem mais limitados. As variáveis de tabelas são apenas acessíveis dentro de um único lote (batch), como as demais variáveis.

Como mencionei antes, o SQL Server não cria ou mantém estatísticas para variáveis de tabela como faz para as tabelas temporárias. Por um lado, este fato pode ser especialmente problemático para tabelas grandes, onde o plano de execução pode se tornar impreciso. Por outro lado, isso também acarreta em menos – ou nenhuma – recompilações.

ENTÃO, QUAL OPÇÃO DEVO ESCOLHER?

Depois de entendermos melhor nossas opções, devemos levantar algumas questões:

• O SQL Server precisará de estatísticas de distribuição ou estimativas precisas de cardinalidade para gerar planos de execução eficientes? Se precisar, qual é o custo de utilizar planos ineficientes quando não tivermos estatísticas disponíveis?

• Qual é o custo das recompilações se utilizarmos tabelas temporárias?

No caso de estarmos trabalhando com tabelas grandes, o custo de utilizarmos planos ineficientes pode ser muito elevado. Já para tabelas pequenas, este custo geralmente é aceitável ou até mesmo desprezível. Como foi citado [1] no Books Online da Microsoft:

“Do not use table variables to store large amounts of data (more than 100 rows). Plan choices may not be optimal or stable when a table variable contains a large amount of data”.

Em outras palavras, não utilize variáveis de tabela para armazenar grandes quantidades de dados (mais de 100 linhas), pois os planos de execução podem não ser eficientes ou estáveis.

Recompilações são caras, então se tivermos uma tabela criada dentro de uma stored procedure, quanto mais vezes esta SP for executada, maior o seu custo. É claro que em algumas situações precisaremos que nossos dados sejam persistidos num determinado escopo em que apenas uma opção atenderá.

Vemos, então, que é necessário avaliar bem nossos requisitos e a massa de dados com a qual iremos trabalhar. Com isso trago o tuning realizado numa SP que utilizava uma variável de tabela e, quando o número de linhas aumentava muito, seu tempo de execução subia drasticamente.

ESTUDO DE CASO

Após a alteração de uma dada SP, observou-se que esta variava muito em tempo de execução. Este variava de poucos segundos até pouco mais de 2 horas e 15 minutos. Começamos a observá-la capturando os tempos de execução e o número de linhas ao longo de alguns dias. Verificamos que mais de 95% das execuções envolviam menos de 1.000 linhas e que dos 5% restantes o número de linhas variava de 1.000 até 100 mil linhas. O comportamento desta SP seguia o esperado até um determinado número de linhas – por volta de 40 mil – e após este ponto, seu tempo de execução aumentava seguindo uma tendência exponencial. Logo abaixo segue um gráfico de dispersão com as medições realizadas relacionando o tempo de execução da SP (duração) com o número de linhas.

Hmm... nice curve!

Hmm… nice curve!

Ao analisarmos o código, identificamos um JOIN utilizando uma variável de tabela que estava causando o tempo de execução inesperado quando o número de linhas era grande. Isso se dá por não existir estatísticas na variável de tabela, desta forma o SQL Server sempre estimava que esta tabela teria apenas um linha. Logo, utilizando nosso primeiro questionamento, ficou evidente que o custo de utilizar planos de execução ineficientes era astronômico. Isto já seria suficiente para optar por uma tabela temporária, mas também analisamos se o custo das recompilações impactaria nesta escolha. Esta SP era executada menos de 90 vezes por dia, um número pequeno que pouco seria influenciado pelas recompilações. Desta forma, alteramos a SP e substituímos a variável de tabela por uma tabela temporária.

No interesse de garantirmos o mesmo desempenho para os 95% dos casos (com poucas linhas), examinamos o antes e depois da alteração. Abaixo estão dois gráficos mostrando as medições realizadas para cada uma das soluções.

imagem02
imagem03
Com estas medições, comprovamos que para a maioria dos casos (95%), o desempenho permaneceu praticamente o mesmo, ou seja, menos de 40 segundos. Já para os casos atípicos (5%) com muitas linhas, o desempenho fui muito superior, como mostra o gráfico com as medições após as alterações.

imagem04
Aqui fica clara a diferença no comportamento da SP em relação ao número de linhas. Enquanto na versão anterior (utilizando variável de tabela) a SP tinha uma duração de aproximadamente 2 horas e 15 minutos para 75 mil linhas, após a alteração (utilizando tabela temporária) a SP possui um tempo de execução de aproximadamente 5 minutos para mais de 100 mil linhas!

CONCLUSÃO

Neste artigo aprendemos um pouco mais sobre variáveis de tabela e tabelas temporárias e também a escolher mais precisamente qual é a melhor opção para cada caso. Vimos, através de um caso real, o impacto que esta decisão pode ter caso seja mal tomada. Seu usuário agradece!

REFERÊNCIAS

[1] Books Online – table (Transact-SQL)
[2] Itzik Ben-gan – Inside Microsoft SQL Server 2008: Programming. Chapter 7: Temporary Tablesand Table Variables.

Quanto os VLFs podem afetar o backup de log?

No post de hoje, vou falar brevemente sobre o impacto que um grande número de VLFs (Virtual Log Files) pode ter sobre os backups de log através de um caso real. Não pretendo explicar detalhadamente o que são os VLFs, como funcionam ou como dimensioná-los, mas certamente não irei deixar você sem fontes para sanar quaisquer dúvidas.

A grosso modo, os VLFs são como o arquivo de log do seu banco de dados é subdividido. Desta forma, o SQL Server gerencia quais partes do log estão sendo utilizadas no momento, quais já foram escritas em disco e podem ser reutilizadas, etc. Se quiser ler mais sobre como o SQL Server registra as informações no arquivo de log, veja esse ótimo artigo [1] do Paul Randal publicado na TechNet Magazine. Recomendo também este vídeo [2] da Jes Schultz Borland da família Brent Ozar que explica de uma forma simples o assunto.

Enfim, no momento em que o arquivo de log cresce – supondo que ele tenha espaço pra isso –, o SQL Server utiliza o incremento definido na opção Autogrowth do arquivo de log para fornecer espaço. Cada vez que isso ocorre, um número de VLFs é criado dependendo do incremento utilizado como é pontuado abaixo.

• Se o incremento for de até 64MB, são criados mais 4 VLFs;
• Se o incremento for maior que 64MB e de até 1GB, são criados 8 VLFs;
• Se o incremento for maior que 1GB, são criados 16 VLFs;

Por padrão, a opção Autogrowth para o arquivo de log é definida como 10% do tamanho inicial que, por sua vez, se não for informado, é definido como 1MB. Para uma base OLTP movimentada, utilizar os valores padrão não é uma boa idéia. Imagine quantas vezes seria necessário que o arquivo de log crescesse para ter 1GB, 2GB, 10GB… Além disso, milhares de VLFs seriam criados, podendo causar a fragmentação do arquivo de log. Sobre este assunto, você encontra um material bem didático neste excelente post [3] do Leandro Ribeiro.

“Ok. Mas qual é o problema em ter vários VLFs?” Bom, para começar, aumento do tempo de recuperação em caso de desastre como mostra esse artigo [4] do Graham Kent da Microsoft. Também pode afetar a performance dos INSERTs, UPDATEs e DELETEs como demonstra Linchi Shea em seu post [5]. Dentre outras implicações de ter VLFs demais, temos o impacto no backup de log como irei mostrar.

“Calma aí! Quantos são VLFs demais?” Aí vou recorrer à máxima do meio SQL Server: Depende. Não vou lhe dizer qual é o número ideal de VLFs pois esse número varia de acordo com o ambiente, mas você pode ler as recomendações que a Kimberly Tripp do SQL Skills descreve neste post [6]. No post, ela recomenda VLFs de 500MB para arquivos de logs com 8GB ou mais. Isso dá 16 VLFs se o arquivo fosse dimensionado no momento da criação, ou pouco mais que 16 caso tivesse que reajustar depois que estiver em uso.

“Certo… Mas como eu conto os VLFs?” Para isso você pode utilizar o comando não documentado DBCC LOGINFO. O resultado será uma linha para cada VLF dentro do banco de dados.

Hm... apenas 6 VLFs? Que bom!

Hm… apenas 6 VLFs? Que bom!

Na figura, executei o comando para o msdb e verifiquei que tenho 6 VLFs para este banco. Não vou detalhar o significado de todas as colunas aqui, mas vale ressaltar a coluna FileSize que retorna o tamanho do VLF em Bytes e a coluna Status que indica que o VLF está sendo usado (= 2) ou não (= 0).

No entanto, é bem provável que você queira verificar isso para todas as suas bases de uma vez e pra isso recomendo esse script no post [7] do David Levy. Neste post, ele também dá dicas – e um script – de como reduzir e controlar o número de VLFs do banco.

Agora que estabelecemos o ponto de partida, vamos ao caso. Na instância haviam por volta de 20 bases bem distintas, variando o tamanho dos arquivos de log de poucos MBs até pouco mais de 90GBs. Os backups de log variavam de base para base de acordo com a necessidade, indo de 5 em 5 minutos para as bases mais ativas até de hora em hora para as bases mais tranquilas. O número de backups de log de todas as bases somadas num dia era de pouco mais de 500 e a duração média destes backups beirava os 40 segundos. Isso pode não parecer muito, mas no final do dia todos esses backups somavam quase 6 horas. Em outras palavras, durante um quarto do dia a instância estava realizando backups de log.

Analisando melhor o arquivo de log, foi constatado que o número de VLFs por base variava de poucas centenas até pouco menos de 50 MIL! Será que isso atrapalhava? Na primeira janela de manutenção, reduzimos ao máximo os arquivos de log e aumentamos de volta para o tamanho original de 8000MB em 8000MB (leia o post da Kimberly Tripp). Desta forma, reduzimos os VLFs para poucas dezenas e com isso os backups de log que antes duravam em média 40 segundos, agora duram em média menos de 1 segundo. Aproveitando os resultados, aumentamos a frequência dos backups de log e agora são realizados por volta de mil backups por dia, quase o dobro de antes. O melhor? O tempo gasto com backups de log que era de 6 horas para 500 backups/dia passou para menos de 15 minutos para 1000 backups/dia.

Moral da história: um número excessivo de VLFs pode afetar seus backups de log e muito! Sempre os mantenha sob controle e avalie bem as ocorrências de crescimento do arquivo de log. E aí, qual foi a última vez que você verificou os VLFs?

REFERÊNCIAS

[1] Paul S. Randal – Understanding Logging and Recovery in SQL Server

[2] Jes S. Borland – How SQL Server Works: Log File (Video)

[3] Leandro Ribeiro – Fragmentação do Transaction Log – Parte I

[4] Graham Kent – Slow recovery times and slow performance due to large numbers of Virtual Log Files

[5] Linchi Shea – Performance impact: a large number of virtual log files – Part I

[6] Kimberly L. Tripp – Transaction Log VLFs – too many or too few?

[7] David Levy – A Busy/Accidental DBA’s Guide to Managing VLFs