Plano de Manutenção do Sql Server - Melhores Práticas em Tarefas e Agendamento

José,

Esta é uma tarefa muito comum para todos os DBAs e a resposta certa NÃO é a mesma para todos e para cada servidor. Como muitas outras coisas, depende do que você precisa.

Definitivamente, você não deseja executar o "Shrink Database" como já sugerido. É MAL para o desempenho e a referência abaixo mostrará o porquê. Isso causa fragmentação do disco e do índice e isso pode levar a problemas de desempenho. É melhor pré-alocar um tamanho grande para os dados e arquivos de log para que o crescimento automático NÃO seja ativado.

Não entendi seu nº 2. backup completo das tabelas selecionadas. Você pode elaborar mais sobre isso?

Chegando à reorganização do índice, atualização de estatísticas e recompilações de índice, você precisa ter cuidado em como fazer isso, caso contrário, acabará usando mais recursos e também terá problemas de desempenho.

Quando você recria índices, as estatísticas dos índices são atualizadas com fullscan, mas se você atualizar as estatísticas depois disso, elas serão atualizadas novamente com uma amostra padrão (que depende de vários fatores, geralmente 5% da tabela quando o tamanho da tabela > 8 MB) o que pode levar a problemas de desempenho. Dependendo da edição que você possui, você poderá fazer recompilações de índice online. A maneira correta de fazer essa atividade é verificar a quantidade de fragmentação e, dependendo disso, reconstruir o índice ou reorganizar o índice + atualizar as estatísticas. E também você pode querer identificar quais tabelas precisam atualizar as estatísticas com mais frequência e tentar atualizar as estatísticas com mais frequência.

Os planos de manutenção são bons, mas é difícil tirar o melhor proveito deles fazendo essas personalizações, a menos que você possa fazer login no SSIS e ajustar os MPs. é por isso que prefiro NÃO usá-los e usar os scripts gratuitos de Ola Hallengren que são mais robustos que os MP's. Além disso, eu recomendaria acompanhar o artigo referenciado por Paul Randal sobre este tópico.

Ref: http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

Esta NÃO é uma resposta abrangente à sua pergunta, mas um bom ponto de partida. HTH e deixe-nos saber se você tiver quaisquer perguntas/comentários adicionais.

Vou compartilhar minha experiência, mesmo que você já tenha aceitado uma resposta. Talvez seja útil :-).

1. Backup diário completo (diário) - ótimo, mas não se esqueça de verificar o espaço e remover arquivos antigos após algum tempo predefinido.
2. Faça backup de tabelas selecionadas (por hora) - não entendo por que você precisa disso, é melhor usar backups diferenciais. Como você faz backup apenas de determinadas tabelas: SSIS, scripts, bcp? Em relação aos backups de diferenças, não agende com muita frequência, pois você roubará a função de backups de log.
3. Backups de log de transações (a cada 15 minutos) - ótimo, você tem certeza de que precisa para todos os bancos de dados? Todos os bancos de dados usam o modelo de recuperação completa ou não?
4. Verifique a integridade do banco de dados - sim, mas você precisa ter certeza de não matar seu ambiente. As instruções de verificação DBCC são bastante egoístas em recursos e verificam bancos de dados completos, portanto, elas precisam ser agendadas fora do horário comercial.
5. Reorganize o índice (diariamente) - não force, faça apenas se necessário. Verifique os DMV's do índice quanto à fragmentação e reorganize apenas com base nas necessidades. Eu moveria todas as operações de índice e estatísticas em uma única tarefa semanal.
6. Atualizar estatísticas (diariamente) - Por favor, veja minha resposta a uma pergunta anterior. Em vez de apenas forçar a atualização de todas as estatísticas, todos os dias, é melhor verificar quando as estatísticas foram atualizadas pela última vez e apenas em alguns casos atualizá-las.
7. Encolher banco de dados (semanal) - Ah, não. Por favor, leia o artigo de Paul Randal sobre redução de arquivos.
8. Índice de reconstrução (semanal) - ver 5.

9. Limpeza de manutenção (diária) - ok com isso.

10. É melhor você também adicionar uma tarefa para verificar seus backups - há uma versão do comando RESTORE (verifyOnly .. se bem me lembro) - digamos semanalmente, embora eu prefira diariamente.

Você menciona que deseja ser blindado em caso de perda de dados, então eu diria que você precisa adicionar os bancos de dados do sistema neste procedimento de manutenção. E também tome o cuidado de fazer backup dos arquivos em uma máquina diferente do próprio servidor. Mantenha em algum lugar um arquivo com o que fazer caso precise reconstruir o master db, msdb..etc. Boa sorte com sua tarefa!

Resposta tardia, mas pode ser útil para outros leitores.

Por favor, tenha em mente que existem muitas tarefas de manutenção ou relatórios que você pode criar, que acarretam riscos invisíveis associados a elas.

O que aconteceria quando uma unidade ficasse cheia durante os backups diferenciais realizados diariamente? E se um trabalho de reconstrução de índice for muito longo? E se um processo de carregamento de dados causar uma extensa contenção de recursos, deixando as operações normais de joelhos? Todos esses são eventos planejados, mas podem causar uma interrupção considerável nos próprios processos que estamos tentando proteger.

Sempre considere como os diferentes trabalhos interagem e programe-os de forma que não haja sobreposição em tarefas sensíveis ou com uso intensivo de recursos.

Sugiro ler este artigo primeiro: http://www.sqlshack.com/removing-the-risk-from-important-maintenance-tasks-in-sql-server/

Ele descreve como realizar importantes tarefas de manutenção no SQL Server – sem riscos. Por exemplo, você pode criar verificações simples em seus trabalhos de manutenção que verificam os recursos disponíveis, bem como o que uma operação exigirá antes da execução. Isso permite garantir que seu ambiente possa lidar com o que você está prestes a fazer e abortar com um erro significativo se os recursos forem inadequados.

1. Parece bom

2. Você pode se beneficiar de fazer backups diferenciais aqui. Olhe para eles com certeza

3. Parece bom

4. Parece bom

5. Conforme declarado anteriormente, as reduções de banco de dados são ruins porque criam fragmentação física de seus dados e arquivos de log, causando mais leituras de E/S aleatórias.

5, 6 e 8: Veja a seguir.

Eles realmente andam de mãos dadas, pois os índices dependem de estatísticas atualizadas e a ordem dessas operações é bastante importante. O método de linha de base que eu emprego, que reconhecidamente pode não funcionar para todos, é que eu executo duas rodadas de manutenção do índice. Primeiro, faço os índices clusterizados e, em seguida, os índices não clusterizados. O método que emprego para ambos é o seguinte. Se um índice for grande e fragmentado o suficiente (sys.dm_db_index_physical_stats), reconstruirei o índice (que inclui uma atualização de estatísticas com verificação completa). Se um índice for muito pequeno para uma reconstrução ou não estiver fragmentado o suficiente para uma reconstrução (menos de 100 páginas e entre 5% e 15% de fragmentação), primeiro realizarei uma reorganização do índice. Em seguida, executarei uma atualização de estatísticas com verificação completa.

Agora, isso abrange as estatísticas de índice, mas não faz nada com as estatísticas da coluna. Semanalmente, atualizarei as estatísticas da coluna.

Espero que isso tenha ajudado de alguma forma!

Eu inclinei no seu comentário "a perda de dados pode ter ramificações legais aqui".

Então, você definitivamente deseja obter uma ferramenta poderosa de terceiros (como o DPM) que possa lidar com backups (e recuperar de eventos catastróficos em um flash e com o mínimo de confusão) muito mais rápido e muito melhor do que qualquer script que você pode retirar da Internet.

Ter backups é uma coisa. Saber como usá-los em caso de emergência é outra.

Não se esqueça de que, se você estiver a ponto de restaurar um backup após uma grande falha, provavelmente já está sob uma carga de estresse e pressão. Você não precisa do fardo adicional de desenterrar e escrever perfeitamente a instrução RESTORE DATABASE com 12 arquivos de log de transações... E rezando para que não falhe...

No meu local de trabalho, posso recuperar/restaurar um banco de dados de 350 Gb em qualquer ponto em um período de 5 minutos na última semana usando o DPM. Com uma interface GUI. Vale a pena, no meu livro.

Para o resto, definitivamente examine o script de índice de Ola Hallengren e ajuste os parâmetros de acordo com suas necessidades. Pessoalmente, juntei-o a uma tarefa agendada que faz com que ele seja executado por uma hora todas as noites sem nova varredura, para que ele lide com os piores índices todas as vezes e force uma nova varredura completa da fragmentação todos os sábados, ou quando todos os índices da lista foram desfragmentados.

Por fim, adiciono minha voz ao grupo "não reduza seus arquivos automaticamente, nunca". File Shrink é apenas uma ferramenta para usar esporadicamente quando ocorre um evento não regular que supera seus logs ou arquivos de banco de dados (loop infinito ou algo parecido). Não é suposto ser uma ferramenta de manutenção. Se você tiver pressão de espaço em disco, a redução só atrasará o inevitável de qualquer maneira.