P+R SQLEstouro de linha, backups diferenciais e muito mais

Paul S. Randal

P Atualizei recentemente um aplicativo para ser executado no SQL Server 2005. Um dos elementos que aproveitei foi a capacidade de ter linhas maiores do que 8.060 bytes. Com isso, posso permitir que os usuários criem campos de dados maiores sem obter um erro do SQL Server. Agora que o aplicativo já está em produção, temos experimentado problemas de desempenho com algumas consultas de verificação que costumavam funcionar bem antes da mudança de esquema. Verifiquei a fragmentação dos vários índices, e está tudo certo. Por que as consultas são executadas com lentidão no SQL Server 2005?

R O recurso de estouro de linha que você está usando é ideal para permitir que uma linha ultrapasse ocasionalmente os 8.060 bytes, mas não se destina a fazer com que a maioria das linhas exceda o tamanho normal. Isso pode gerar uma queda no desempenho das consultas, como a que você está experimentando.

O motivo é que, quando uma linha está prestes a ser superdimensionada, uma das suas colunas de tamanho variável é enviada para "fora da linha". Isso significa que a coluna é retirada da linha na página de índice de dados e movida para uma página de texto. O antigo valor da coluna é substituído por um ponteiro, que indica a nova localização do valor da coluna no arquivo de dados.

Esse é exatamente o mesmo mecanismo usado para armazenar colunas de LOB (objeto grande) regulares, como XML, texto, imagem ou varchar(max). Observe que, se o esquema da tabela contiver várias colunas de tamanho variável, não haverá garantia de que a mesma coluna será sempre enviada para fora da linha quando várias linhas forem superdimensionadas.

Esse mecanismo pode criar um problema de desempenho. Uma consulta para recuperar uma coluna de tamanho variável em uma única linha de tabela poderá precisar inesperadamente de uma E/S adicional, se a coluna tiver sido enviada para fora da linha (para fazer a leitura da página de texto contendo a localização do valor fora da linha). Se várias linhas forem superdimensionadas, uma consulta para recuperar a mesma coluna de tamanho variável em diversas linhas terá um desempenho imprevisível, dependendo da quantidade de valores enviados para fora da linha.

No seu caso, as consultas que realizam uma verificação de intervalo ou de tabela para uma lista de seleção incluindo uma coluna de tamanho variável apresentam desempenho insuficiente, devido ao estouro de linha e aos seus efeitos. Não importa que os índices estejam perfeitamente fragmentados — quando uma coluna de tamanho variável é enviada para fora da linha, a verificação que antes era eficiente passa a ser interrompida, pois uma E/S aleatória é necessária para ler a página de texto contendo o valor fora da linha.

O estouro de linha é muito útil para linhas superdimensionadas ocasionais. Mas, se o desempenho das consultas for fundamental, não deve ser um componente explorado com intensidade no seu design.

P Acabamos de introduzir o espelhamento de banco de dados entre dois clusters de failover, como forma de obter redundância geográfica por um custo menor do que o da replicação de SAN (rede de área de armazenamento). Os data centers se encontram na mesma cidade, então podemos usar o espelhamento síncrono. O problema é que, quando ocorre um failover no cluster local, o banco de dados espelhado faz failover para o cluster remoto, em um comportamento diferente do desejado. Como impedir que isso aconteça? Queremos que o failover ocorra somente se o cluster local estiver indisponível.

R Para proporcionar maior disponibilidade, o espelhamento é configurado com um servidor testemunha. Assim, os failovers ocorrem automaticamente se o servidor principal se torna indisponível. A ideia é que, se todo o cluster local ficar inativo, o espelhamento de banco de dados faça failover para o segundo cluster, e o aplicativo possa continuar funcionando.

O problema é quando acontece um failover de cluster. O failover leva mais tempo para ocorrer do que a configuração de tempo limite padrão do espelhamento de banco de dados. O servidor testemunha e o servidor espelho (ou seja, a instância ativa do SQL Server no segundo cluster) concordam que não conseguem ver o servidor principal, e então o servidor espelho inicia um failover de espelhamento para o segundo cluster.

O método mais simples para impedir isso é remover o servidor testemunha, de forma que o espelhamento de banco de dados não faça failover automaticamente se o cluster local ficar inativo. Logicamente, isso reduz a disponibilidade, pois passa a ser necessária interação humana para iniciar um failover.

A segunda opção consiste em alterar a configuração do tempo limite padrão do espelhamento de banco de dados. Ou seja, o número de "pings" enviados uma vez por segundo que o servidor principal precisa deixar de responder para ser declarado indisponível. Essa configuração é chamada de tempo limite de parceiro e tem valor padrão 10. É possível encontrar o valor do tempo limite atual para o banco de dados usando este código:

SELECT [mirroring_connection_timeout]
  FROM master.sys.database_mirroring 
  WHERE [database_id] = DB_ID ('mydbname');
GO

O valor do tempo limite pode ser alterado usando este código:

ALTER DATABASE mydbname 
  SET PARTNER TIMEOUT <timeoutvalue>;
GO

Nesse cenário, o tempo limite de parceiro precisa ser definido como superior ao tempo normalmente necessário para que um failover de cluster ocorra no cluster local. Isso pode ser um pouco difícil de determinar, devido às variações no tempo necessário para executar a recuperação no banco de dados espelhado quando ocorre o failover de cluster. Mas provavelmente você conseguirá definir o limite superior. O problema desse método é que talvez o valor do tempo limite precise ser de minutos, o que poderá ser inaceitável na ocorrência de um verdadeiro desastre.

P Minha estratégia de backup envolve backups completos e de log, mas ouvi falar que deveria acrescentar backups diferenciais para diminuir o tempo de restauração. Faço um backup completo uma vez por semana, e backups de log a cada hora. Tentei acrescentar backups diferenciais diários, mas percebi algo estranho: os backups diferenciais do final da semana têm praticamente o mesmo tamanho do backup completo semanal. Eu tinha a impressão de que eram incrementais como os backups de log. Será que não entendi alguma coisa?

R Há um mal-entendido quanto à natureza dos backups diferenciais. Eles não são incrementais como os backups de log. Um backup diferencial contém todas as extensões de arquivos de dados alteradas desde o backup completo anterior (e isso se aplica aos backups em nível de banco de dados, grupo de arquivos e arquivo).

Quando uma extensão (grupo lógico de oito páginas contíguas de arquivos de dados) sofre qualquer tipo de alteração, ela é marcada em uma página de bitmap especial chamada de mapa diferencial (mais conhecida como "diff map"). Há um mapa diferencial para cada bloco de 4 GB de cada arquivo de dados. Quando é feito um backup diferencial, o subsistema de backup examina todos os mapas diferenciais e copia todas as extensões alteradas, mas os mapas não são reiniciados. Isso significa que, se mais extensões forem alteradas entre backups diferenciais sucessivos, os backups posteriores serão maiores. Os mapas diferenciais só são reiniciados quando um backup completo é realizado.

Se a carga de trabalho do aplicativo for tanta que o conteúdo do banco de dados seja extensivamente alterado em um período curto (digamos, em uma semana), um backup completo semanal terá quase o mesmo tamanho de um backup diferencial realizado pouco antes do próximo backup completo. Isso explica o comportamento que você observou.

Está correta a sua ideia de que os backups diferenciais ajudam a reduzir o tempo de restauração em casos de recuperação de desastre. Se a estratégia de backup consiste em realizar backups completos semanais e backups de log a cada hora, uma restauração até o minuto atual exige o seguinte:

• Fazer um backup do final do log (todos os logs gerados desde o backup de log mais recente).
• Restaurar o backup completo de banco de dados mais recente.
• Restaurar todos os backups de log, em sequência, desde o backup completo de banco de dados mais recente.
• Restaurar o backup do final do log.

Isso poderia exigir a restauração de muitos backups de log, especialmente se o desastre ocorresse pouco antes do próximo backup completo (no pior cenário possível, isso significaria 24 + 24 + 24 + 24 + 24 + 24 + 23 backups de log a serem restaurados!). O acréscimo de backups diferenciais diários a essa estratégia muda a sequência de restauração para esta:

• Fazer um backup do final do log (todos os logs gerados desde o backup de log mais recente).
• Restaurar o backup completo de banco de dados mais recente.
• Restaurar o backup diferencial mais recente.
• Restaurar todos os backups de log, em sequência, desde o backup diferencial mais recente.
• Restaurar o backup do final do log.

Isso eliminaria a necessidade de restaurar muitos backups de log, pois a restauração de um backup diferencial equivale, basicamente, a restaurar todos os backups de log no período coberto pelo backup diferencial.

O pior caso possível em um cenário no qual se realiza um backup diferencial diário seria de 23 backups de log, até mesmo no último dia da semana. A única desvantagem dos backups diferenciais não serem incrementais é que podem ocupar mais espaço, mas quase sempre eles oferecem uma compensação válida, reduzindo o tempo de restauração.

P Tenho um cluster de failover com dois nós. Cada nó executa uma única instância do SQL Server 2005. Segui o conselho de configurar cada instância para usar somente 50% da memória disponível. Agora, estou com problemas, porque a carga de trabalho nas duas instâncias precisa de mais memória para manter os mesmos níveis de desempenho. Se eu remover a limitação de memória ou torná-la maior, acredito que terei problemas quando uma das instâncias fizer failover, e as duas estiverem sendo executadas no mesmo nó. Qual a sua recomendação?

R Responderei à pergunta sobre o caso de dois nós e duas instâncias, mas o mesmo se aplica a outras configurações com várias instâncias (N-1 clusters de failover, com N nós e N-1 instâncias do SQL Server).

Muitos profissionais lidam com uma carga de trabalho elevada (consumindo mais de 50% da memória do servidor) nas duas instâncias, e não levam em conta o efeito sobre as cargas de trabalho quando as duas instâncias precisam ser executadas no mesmo nó, após a ocorrência de um failover. Sem nenhuma configuração especial, a distribuição de memória entre as instâncias pode se tornar desproporcional, fazendo com que uma carga de trabalho seja executada sem problemas, enquanto a outra fica muito lenta.

Com o SQL Server 2000, recomenda-se limitar cada instância a, no máximo, 50% da memória do nó de cluster. Afinal, o gerenciador de memória do SQL Server 2000 não responde à pressão de memória — se o SQL Server tirar, digamos, 80% da memória do nó, ele não fará a reposição. Em uma situação de failover, isso significa que, se outra instância for iniciada, terá apenas 20% da memória disponível. Limitando as duas instâncias a usar, no máximo, 50% da memória de um nó, uma instância de failover terá a garantia de dispor de 50% da memória. O problema dessa abordagem é que a carga de trabalho em cada instância também se limita a 50% da memória.

Com o SQL Server 2005 (e o SQL Server 2008), o gerenciador de memória é capaz de responder à pressão de memória. Então, o máximo de 50% não é mais adequado. Mas, sem algum tipo de limitação, se duas instâncias forem executadas no mesmo nó de cluster, poderão pressionar-se mutuamente até alcançar uma distribuição de memória desproporcional.

A solução consiste em definir cada instância com uma quantidade mínima de memória, de forma que não possam ser pressionadas a liberar memória demais. Uma configuração comum para um cenário de dois nós e duas instâncias seria cada instância com um mínimo de 40% da memória. Isso significa que, quando cada instância for executada em um nó separado, elas poderão consumir a memória de que precisarem. Quando ocorrer um failover, cada instância terá uma certa quantidade de memória garantida, a fim de preservar um nível definido de desempenho da carga de trabalho, com uma pequena sobra para ser compartilhada entre as duas. Embora isso represente uma possível queda de desempenho das duas cargas de trabalho em uma situação de failover (conforme esperado), elas não ficarão limitadas na maior parte do tempo, quando cada instância estiver sendo executada em um nó de cluster separado.

Paul S. Randal é diretor administrativo da SQLskills.com e MVP do SQL Server. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server da Microsoft de 1999 a 2007. Paul escreveu o DBCC CHECKDB/repair para o SQL Server 2005 e foi responsável pelo mecanismo de armazenamento principal durante o desenvolvimento do SQL Server 2008. É especialista em recuperação de desastres, alta disponibilidade e manutenção de banco de dados, e participa regularmente de conferências em todo o mundo. Ele mantém um blog em SQLskills.com/blogs/paul.