Partilhar via

Considerações para as configurações de crescimento automático e redução automática no SQL Server

  • Artigo

Versão original do produto: SQL Server
Número original do KB: 315512

Resumo

As configurações padrão de crescimento automático e redução automática são apropriadas em muitos sistemas SQL Server. No entanto, há ambientes em que talvez seja necessário ajustar os parâmetros de crescimento automático e redução automática. Este artigo fornece algumas informações em segundo plano para orientar você, quando selecionar essas configurações para seu ambiente.

Aqui estão algumas coisas a considerar se você decidir ajustar seus parâmetros de crescimento automático e redução automática.

Como faço para definir as configurações

  1. Você pode definir ou modificar as configurações de crescimento automático e redução automática usando uma das seguintes opções:

    Observação

    Para obter mais informações sobre como definir essas configurações no nível do arquivo de banco de dados, consulte Adicionar dados ou arquivos de log a um banco de dados.

    Você também pode configurar a opção de crescimento automático ao criar um banco de dados.

    Para exibir as configurações atuais, execute o seguinte comando Transact-SQL:

    sp_helpdb [ [ @dbname= ] 'name' ]

  2. Lembre-se de que as configurações de crescimento automático são por arquivo. Portanto, você deve defini-los em pelo menos dois locais para cada banco de dados (um para o arquivo de dados primário e outro para o arquivo de log primário). Se você tiver vários arquivos de dados e/ou de log, deverá definir as opções em cada arquivo. Dependendo do seu ambiente, você pode terminar com configurações diferentes para cada arquivo de banco de dados.

Considerações para AUTO_SHRINK

AUTO_SHRINK é uma opção de banco de dados no SQL Server. Quando você habilita essa opção para um banco de dados, esse banco de dados se torna qualificado para redução por uma tarefa em segundo plano. Essa tarefa em segundo plano avalia todos os bancos de dados que atendem aos critérios de redução e redução dos dados ou arquivos de log.

Você precisa avaliar cuidadosamente a configuração dessa opção para os bancos de dados em uma instância do SQL Server. Operações frequentes de crescimento e redução podem levar a vários problemas de desempenho.

  • Se vários bancos de dados passarem por operações frequentes de redução e crescimento, isso levará facilmente à fragmentação no nível do sistema de arquivos. Isso pode ter um impacto severo no desempenho. Isso é verdade se você usar as configurações automáticas ou se aumentar e reduzir manualmente os arquivos com frequência.

  • Depois de AUTO_SHRINK reduzir com êxito os dados ou o arquivo de log, uma operação DML ou DDL subsequente pode diminuir significativamente se o espaço for necessário e os arquivos precisarem crescer.

  • A AUTO_SHRINK tarefa em segundo plano pode consumir recursos quando há muitos bancos de dados que precisam ser reduzidos.

  • A AUTO_SHRINK tarefa em segundo plano precisará adquirir bloqueios e outras sincronizações que possam entrar em conflito com outras atividades regulares do aplicativo.

Considere definir bancos de dados para um tamanho necessário e pré-aumentá-los. Deixe o espaço não utilizado nos arquivos de banco de dados se achar que os padrões de uso do aplicativo precisarão deles novamente. Isso pode evitar a redução e o crescimento frequentes dos arquivos de banco de dados.

Considerações sobre AUTOGROW

  • Se você executar uma transação que requer mais espaço de log do que o disponível e tiver ativado a opção de crescimento automático para o log de transações desse banco de dados, o tempo que a transação leva para ser concluída incluirá o tempo que o log de transações leva para crescer pelo valor configurado. Se o incremento de crescimento for grande ou houver algum outro fator que faça com que ele demore muito, a consulta na qual você abre a transação poderá falhar devido a um erro de tempo limite. O mesmo tipo de problema pode resultar de um crescimento automático da parte de dados do seu banco de dados.

  • Se você executar uma transação grande que exija que o log aumente, outras transações que exigem uma gravação no log de transações também terão que aguardar até que a operação de crescimento seja concluída.

  • Se você tiver muitos crescimentos de arquivos em seus arquivos de log, poderá ter um número excessivamente grande de arquivos de log virtuais (VLF). Isso pode levar a problemas de desempenho com operações de inicialização/online de banco de dados, replicação, espelhamento e CDC (captura de dados de alteração). Além disso, isso às vezes pode causar problemas de desempenho com modificações de dados.

Observação

Se você combinar as opções de crescimento automático e redução automática, poderá criar uma sobrecarga desnecessária. Certifique-se de que os limites que disparam as operações de aumento e redução não causem alterações frequentes de tamanho para cima e para baixo. Por exemplo, você pode executar uma transação que faz com que o log de transações cresça em 100 MB no momento em que ele é confirmado. Algum tempo depois disso, a redução automática inicia e reduz o log de transações em 100 MB. Em seguida, você executa a mesma transação e faz com que o log de transações cresça 100 MB novamente. Nesse exemplo, você está criando sobrecarga desnecessária e potencialmente criando fragmentação do arquivo de log, o que pode afetar negativamente o desempenho.

Se você aumentar seu banco de dados em pequenos incrementos, ou se você aumentá-lo e depois reduzi-lo, poderá acabar com a fragmentação do disco. A fragmentação de disco pode causar problemas de desempenho em algumas circunstâncias. Um cenário de pequenos incrementos de crescimento também pode reduzir o desempenho do sistema.

No SQL Server, você pode habilitar a inicialização instantânea de arquivos. A inicialização instantânea de arquivos acelera as alocações de arquivos apenas para arquivos de dados. A inicialização instantânea de arquivos não se aplica a arquivos de log. Para obter mais informações, consulte Inicialização instantânea de arquivos do banco de dados.

Práticas recomendadas para crescimento automático e redução automática

  • Para um sistema de produção gerenciado, você deve considerar o crescimento automático apenas como uma contingência para um crescimento inesperado. Não gerencie diariamente o crescimento de seus dados e do log com o crescimento automático.

  • Você pode usar alertas ou programas de monitoramento para monitorar tamanhos de arquivos e aumentar arquivos de forma proativa. Isso ajuda a evitar a fragmentação e permite que você mude essas atividades de manutenção para horários fora do horário de pico.

  • Autoshrink e autogrow devem ser cuidadosamente avaliados por um administrador de banco de dados (DBA) treinado; Eles não devem ser deixados sem gerenciamento.

  • Seu incremento de crescimento automático deve ser grande o suficiente para evitar as penalidades de desempenho listadas na seção anterior. O valor exato a ser usado em sua definição de configuração e a escolha entre um crescimento percentual e um crescimento específico do tamanho de MB depende de muitos fatores em seu ambiente. Uma regra geral que você pode usar para testar é definir sua configuração de crescimento automático para cerca de um oitavo do tamanho do arquivo.

  • Ative a \<MAXSIZE> configuração de cada arquivo para evitar que qualquer arquivo cresça a ponto de usar todo o espaço em disco disponível.

  • Mantenha o tamanho de suas transações o menor possível para evitar o crescimento não planejado de arquivos.

Por que tenho que me preocupar com o espaço em disco se as configurações de tamanho são controladas automaticamente

  • A configuração de crescimento automático não pode aumentar o tamanho do banco de dados além dos limites do espaço em disco disponível nas unidades para as quais os arquivos são definidos. Portanto, se você confiar na funcionalidade de crescimento automático para dimensionar seus bancos de dados, ainda deverá verificar independentemente o espaço disponível no disco rígido. A configuração de crescimento automático também é limitada pelo MAXSIZE parâmetro selecionado para cada arquivo. Para reduzir a possibilidade de ficar sem espaço, você pode monitorar o contador do Monitor de Desempenho SQL Server: Bancos de Dados Objeto: Tamanho dos Arquivos de Dados (KB) e configurar um alerta quando o banco de dados atingir um determinado tamanho.

  • O crescimento não planejado de dados ou arquivos de log pode ocupar espaço que outros aplicativos esperam estar disponível e pode causar problemas a esses outros aplicativos.

  • O incremento de crescimento do log de transações deve ser grande o suficiente para ficar à frente das necessidades de suas unidades de transação. Mesmo com o crescimento automático ativado, você pode receber uma mensagem informando que o log de transações está cheio, se ele não puder crescer rápido o suficiente para atender às necessidades de sua consulta.

  • O SQL Server não testa constantemente os bancos de dados que atingiram o limite configurado para redução automática. Em vez disso, ele examina os bancos de dados disponíveis e encontra o primeiro configurado para redução automática. Ele verifica esse banco de dados e reduz esse banco de dados, se necessário. Em seguida, ele aguarda vários minutos antes de verificar o próximo banco de dados configurado para redução automática. Em outras palavras, o SQL Server não verifica todos os bancos de dados de uma só vez e os reduz de uma só vez. Ele trabalhará nos bancos de dados de forma round robin para escalonar a carga ao longo de um período de tempo. Portanto, dependendo de quantos bancos de dados você configurou para redução automática em uma instância específica do SQL Server, pode levar várias horas desde o momento em que o banco de dados atinge o limite até que ele realmente seja reduzido.

Referências