VLDB, Auto Update Statistics e Virtual Log Files (VLF)

Na passada terça-feira fiz uma apresentação sobre Very Large Databases (VLDB) no grupo “SQL Port”. Os slides podem ser baixados aqui.

Aproveito para ponderar alguns pontos que foram discutidos durante a apresentação:

Auto Update Statistics
Ficou no ar a dúvida se a opção “Auto Update Statistics” estiver activa, as estatísticas são actualizadas antes ou depois da execução da query, efectivamente.

Confirmei os seguintes pontos:

O “query optimizer”, baseando-se nos predicados da query, verifica se existem estatísticas desactualizadas antes de compilar a query e antes de executar um plano que já esteja em cache.
 Com a opção “Auto Update Statistics” activa, a query sempre sera compilada e executada com estatísticas actualizadas.
Quando as estatísticas estão desactualizadas, o “query optimizer” espera pela actualização das mesmas para depois compilar e executar a query.

Se a opção “Auto Update Statistics Async“ estiver activada, este comportamento altera-se:

A query é executada mesmo se as estatísticas estiverem desactualizadas.
O “query optimizer” pode escolher um outro plano de execução, que não seria o melhor para dada query em condições normais, porém no panorama de estatísticas desactualizadas é mais eficiente.
Ao mesmo tempo que a query é executada, uma operação em background é lançada com o objective de actualizar as estatísticas, assim sendo, outras queries que sejam executadas nas mesmas condições irão tirar benefício de estatísticas actualizadas.

Em resumo:

Opção Activado Comportamento
Auto Update Stats (Sync) SIM ·       Actualização síncrona (antes da execução).
·       A primeira query após uma grande modificação pode ter a performance afectada.
Auto Update Stats Async NÃO
Auto Update Stats (Sync) SIM ·       Actualização assíncrona (em background).
·       A query não terá a performance afectada por actualização de estatísticas…
·       …porem não tira proveito das estatísticas actualizadas.
·       A estatística é actualizada em background.
Auto Update Stats Async SIM
Auto Update Stats (Sync) NÂO ·       Auto update stats estará desactivado.
Auto Update Stats Async SIM
Auto Update Stats (Sync) NÃO ·       Auto update stats estará desactivado.
Auto Update Stats Async NÃO

Virtual Log File (VLF)
Uma das recomendações que fiz na apresentação, foi controlar correctamente o crescimento do ficheiro de T-Log, o que ajuda a evitar problemas com a fragmentação des VLFs.

Eu tinha uma ideia errada do número possível de VLFs em um transaction log, e resolvi pesquisar.

De uma forma resumida, quando o SQL Server tem a necessidade de expandir o t-log, isso é feito baseado em virtual log files (VLF).
Um VLF pode ser encarado como um ficheiro muito pequeno dentro do ficheiro principal do t-log. Em um t-log existem N VLFs.

Existe uma regra para saber o número de VLFs alocados a cada growth:
·      Para uma expansão até 64Mb, 4 VLFs serão criados.
·      Para uma expansão de 65MB até 1GB temos 8 VLFs.
·      Para expansões superiores a 1GB, serao alocados 16 VLFs.

Um número incorrecto de VLFs (para mais ou para menos) pode causar problemas de performance, e até retardar o recovery de uma base de dados, assim como fazer com que também os backups sejam ineficientes.

Mas quantos VLFs devemos ter? Como sempre, isso depende! Mas, a ideia principal é que o tamanho dos VLFs seja bem definido. Sabendo a regra do número de VLFs que serão alocados Vs. o tamanho da expansão do log é fácil controlar o número de VLFs.

Com uma rápida pesquisa no Google podemos também encontrar scripts para verificação do estado do nosso t-log, e a partir daí tomar decisões.

Minha ideia aqui não é dedicar um post a este assunto, pelo menos por agora, porém temos boa informação nestes artigos:

Transaction Log VLFs – too many or too few?
Transaction Log Physical Architecture

Até a próxima!

Paralelismo – Parte 2: Cost Threshold for Parallelism

Boa tarde !

Em continuação ao primeiro post sobre paralelismo, irei analisar o parâmeto “Cost Threshold for Parallelism”.
Ao se alterar o valor do parâmetro “Max Degree of Parallelism”, de 0 (valor por defeito) para outro valor, o comportamento do SQL Server em relação ao número de threads paralelas a serem executadas em um processo é afectado. Porém, existo um outro parâmetro que interfere na decisão de “paralelizar ou não” um determinado processo, este parâmetro é o “Cost Threshold for Parallelism”.

Este valor pode ser definido, assim como o MaxDop, utilizando a interface gráfica do SSMS (nas propriedades da instância) ou utilizando o sp_configure. O valor por defeito é 5 segundos.

Basicamente, o SQL Server estima o custo de uma query e, dependendo deste custo, o paralelismo será aplicado ou não. Na prática, se o tempo de execução de uma query executada em série for superior ao definido no “Cost Threshold for Parallelism”, a abordagem poderá ser alterada para uma execução em paralelo, se o custo total final for compensatório.

Uma maneira de se testar na prática é executar uma query, que normalmente é paralelizada, utilizando a hint “MAXDOP <número de processadores>”. Desta forma, olhando para o plano de execução, é possível comparar os custos associados a mesma query, mas executada de formas diferentes, em paralelo ou em série.

Segue um exemplo da utilização desta hint:

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 1);

Tenha em atenção que este parâmetro é ignorado nos seguintes casos:

  • Se o servidor tem apenas um CPU (um core).
  • Se o SQL Server tem apenas um CPU (um core) disponível, por configuração da affinity-mask.
  • O MaxDop está definido para 1 (sem paralelismo).

Por hoje é isso, nos encontramos na próxima Terça-Feira!

Paralelismo – Parte 1: MAXDOP

Muito se fala do paralelismo no SQL Server. Geralmente a discussão vem a tona quando uma quantidade anormal de processos com wait types do tipo CXPACKET começam a aparecer e a ficarem “pendurados” durante um tempo incomum.
Neste artigo, irei apenas fazer uma abordagem inicial acerca do MAXDOP, que será desenvolvido e aprofundado durante as próximas terça-feiras. Ah… a propósito, meu nome é Murilo Miranda, DBA de profissão, e mais um colaborador deste Blogue Lusitano – uma grande iniciativa dos criadores.
Voltando ao tema, o “Max Degree of Parallelism” (a.k.a MAXDOP) é uma configuração a nível da instância que controla o número de processadores que serão utilizados, paralelamente, para execução de uma query. O que não significa limitar o número de processadores utilizados pela instância – o que pode ser definido ao afinar o “Affinity Mask”.
O MAXDOP é uma opção avançada, com efeitos praticamente imediatos no comportamento dos processos, pois influencia os recursos que serão utilizados ao calcular o plano de execução.
Dependendo da característica do servidor, em relação a processadores, a abordagem deve ser diferente. Ou seja, a configuração em servidores SMP, NUMA ou com hyperthreading activado variam de caso para caso.
O MAXDOP vem configurado com o valor 0, por defeito. O que significa que o SQL Server irá gerar os planos de execução tendo disponíveis até um máximo de 64 CPUs para paralelizar processos.
Para anular o paralelismo, basta alterar o valor do MAXDOP para 1, o que na pratica significa que apenas 1 CPU estará disponível para paralelismo. Isto pode ser uma boa prática em determinados casos e não significa que será sempre o mesmo CPU a ser utilizado.
Tenha em conta que:
  • Se o MAXDOP for alterado para um valor superior ao número de CPUs disponíveis no servidor, o SQL Server irá utilizar todos os CPUs.
  •  Se o servidor tem apenas um CPU lógico, obviamente, não haverá paralelismo.

  
Existem duas maneiras de se alterar o valor do MAXDOP. Bem… na verdade existem mais, porem as duas mais obvias (e utilizadas) são :

  • sp_configure
    Sp_configure ‘show advanced options’, 1; 
    go 
    RECONFIGURE WITH override; 
    go 

    Sp_configure ‘max degree of parallelism’, <novo valor para maxdop>; 
    go 
    RECONFIGURE WITH override; 
    go 
  • Alterando as propriedades Instância

Há muito o que falar sobre o MAXDOP, por isso este artigo será dividido em algumas partes. Nesta primeira parte o objectivo era passar o básico, mas ainda iremos “falar“ sobre CXPAKET, “Cost Threshold for Parallelism”, abordagens para configuração do MAXDOP, casos em que esta propriedade pode ser sobreposta, boas práticas… Não percam os próximos capítulos, a mesma hora, no mesmo dia, neste mesmo canal!