Compartilhar via

Considerações sobre as configurações de crescimento automático e de preenchimento automático no SQL Server

  • Artigo

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

Resumo

As configurações padrão de crescimento automático e de preenchimento automático 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 de preenchimento automático. Este artigo fornece algumas informações em segundo plano para guiá-lo quando selecionar essas configurações para seu ambiente.

Aqui estão algumas coisas a serem consideradas se você decidir ajustar seus parâmetros de crescimento automático e de preenchimento automático.

Como fazer configurar as configurações

  1. Você pode configurar ou modificar as configurações de crescimento automático e de preenchimento automático usando um dos seguintes procedimentos:

    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. Tenha em mente que as configurações de crescimento automático são por arquivo. Portanto, você precisa 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 dados e/ou arquivos 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 sobre 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 para reduzir e reduzir os dados ou arquivos de log.

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

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

  • Depois AUTO_SHRINK de 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 ocupar 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é-ampliá-los. Deixe o espaço não utilizado nos arquivos de banco de dados se você achar que os padrões de uso do aplicativo precisarão deles novamente. Isso pode impedir a redução frequente e o crescimento 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 está disponível e acionou a opção de crescimento automático para o log de transações desse banco de dados, então 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 leve muito tempo, a consulta na qual você abre a transação pode 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 banco de dados.

  • Se você executar uma transação grande que exige que o log cresça, 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 virtual (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 de preenchimento automático, poderá criar sobrecarga desnecessária. Verifique se os limites que disparam as operações de crescimento e redução não causarão 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 100 MB no momento em que ele se confirma. Algum tempo depois disso, o autoshrink inicia e reduz o log de transações em 100 MB. Em seguida, você executa a mesma transação e isso 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, qualquer um dos quais pode afetar negativamente o desempenho.

Se você aumentar seu banco de dados em pequenos incrementos ou se o crescer e reduzi-lo, poderá acabar com a fragmentação de 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 em seu sistema.

Em SQL Server, você pode habilitar a inicialização instantânea do arquivo. A inicialização de arquivo instantâneo acelera as alocações de arquivos apenas para arquivos de dados. A inicialização de arquivo instantâneo não se aplica aos arquivos de log. Para obter mais informações, consulte Inicialização de Arquivo Instantâneo do Banco de Dados.

Práticas recomendadas para o crescimento automático e o preenchimento automático

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

  • Você pode usar alertas ou programas de monitoramento para monitorar tamanhos de arquivo e aumentar arquivos proativamente. Isso ajuda você a evitar a fragmentação e permite que você altere essas atividades de manutenção para horários não de pico.

  • O preenchimento automático e o crescimento automático devem ser avaliados cuidadosamente por um DBA (Administrador de Banco de Dados) 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 na configuração e a escolha entre um crescimento percentual e um crescimento de tamanho de MB específico depende de muitos fatores em seu ambiente. Uma regra geral que você pode usar para teste é definir a configuração de crescimento automático como cerca de um a oito do tamanho do arquivo.

  • Ative a \<MAXSIZE> configuração de cada arquivo para impedir que qualquer arquivo cresça até um ponto em que ele usa todo o espaço em disco disponível.

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

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ê depender da funcionalidade de crescimento automático para dimensionar seus bancos de dados, ainda deverá marcar seu espaço em disco rígido disponível de forma independente. 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 Monitor de Desempenho SQL Server: Objeto Databases: Tamanho de arquivo 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 gerar espaço que outros aplicativos esperam estar disponíveis e podem 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 de 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.

  • SQL Server não testa constantemente bancos de dados que atingiram o limite configurado para o preenchimento automático. Em vez disso, ele examina os bancos de dados disponíveis e localiza o primeiro configurado para o preenchimento automático. 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 que está configurado para o autoshrink. Em outras palavras, SQL Server não marcar todos os bancos de dados ao mesmo tempo e reduz todos eles de uma só vez. Ele trabalhará através dos bancos de dados de forma round robin para escalonar a carga durante um período de tempo. Portanto, dependendo de quantos bancos de dados você configurou para pensar automaticamente em uma instância SQL Server específica, pode levar várias horas a partir do momento em que o banco de dados atinge o limite até que ele realmente diminua.

Referências