Uma das variáveis mais críticas para o desempenho da base de dados é a Fragmentação do Índice SQL. Podemos enfrentar bloqueios, deadlocks, problemas de IO, problemas de vazamento de disco se o nível de fragmentação não for gerenciado adequadamente. Também pode levar a um plano de execução inadequado.
DMVs
No SQL Server, podemos usar o DMV sys.dm_db_index_physical_stats para obter as informações sobre o nível de fragmentação do índice, contagem de páginas e tipos de índice usando isto.
SSMS
Também podemos usar os relatórios embutidos do SQL Server ‘Index Physical Statistics’ em SSMS
Obtém-se o Index Physical Statistics para o banco de dados no seguinte formato.
Estamos limitados a executar este relatório em nível de base de dados, e se tivermos múltiplas bases de dados na instância e muitas instâncias para gerir, torna-se tarefa consumidora de tempo e recursos recolher os detalhes, analisar o estado com base no nosso estado limite e depois decidir sobre a reconstrução ou reorganização do índice SQL.
Cripts personalizados
Também podemos usar scripts personalizados para detectar e corrigir os problemas de fragmentação, mas também é um processo complicado para estabelecer e analisar os resultados manualmente. Podemos querer toda essa informação em um lugar para centralizar e depois decidir a política de manutenção do índice. Podemos usar os comandos do Alter Index para remover o nível de fragmentação. Para bancos de dados pequenos, nós não nos importamos muito com a contagem de páginas enquanto reconstruímos o índice, no entanto, para bancos de dados grandes precisamos considerar estes também.
3rd party tools
Nós podemos facilmente monitorar, analisar e remover a fragmentação do índice SQL existente usando o ApexSQL Defrag. Podemos gerenciar múltiplas instâncias e bancos de dados em uma ferramenta gráfica. Nós obtemos os seguintes benefícios com esta ferramenta.
- Fornece uma interface gráfica de usuário para monitorar e analisar a fragmentação do índice
- Podemos fazer manutenção de índice para todas as instâncias do SQL Server e bancos de dados
- Fornece a forma de configurar os relatórios personalizados com base em nossos requisitos
- Podemos configurar políticas para verificar e executar tarefas de manutenção de índice conforme as métricas personalizadas
- Podemos analisar (rápida ou profunda) fragmentação de índice, set fill factor, online index maintenance
- Podemos preparar os relatórios nos formatos desejados (CSV ou HTML)
Uma vez instalada a ferramenta ApexSQL Defrag e configurada uma instância para monitorar, ela mostra os detalhes do estado de fragmentação do índice SQL rapidamente.
Devemos evitar a reconstrução do índice SQL ou reorganizar as operações se não for necessário. Se tivermos índices muito grandes, então pode levar muito tempo e mais recursos do sistema para analisar. O ApexSQL Defrag dá a opção de verificar a fragmentação.
- Limitada: Neste modo de verificação, o SQL Server coleta informações limitadas sobre a fragmentação. Isto não causa qualquer problema de performance ao sistema. É o modo padrão no ApexSQL Defrag
- Sampled: Neste modo, ele pega uma porção de amostra do índice SQL e coleta as informações sobre a fragmentação
- Detalhado: Nós obtemos o conhecimento detalhado sobre todas as páginas do índice; isso pode causar alguma sobrecarga nos recursos do sistema, no entanto, podemos usá-lo se observarmos a alta fragmentação com os modos de varredura acima
Da mesma forma, podemos definir o nível de fragmentação na categoria Alta, Média e Baixa.
Você também pode definir seu limiar de fragmentação ganho para analisar os índices.
Uma vez analisado o conjunto de resultados, clique com o botão direito do mouse no índice SQL particular e você pode ter múltiplas opções para escolher.
- Analisar (rápido)
- Analisar (profundo)
- Reorganizar\Rebuild\Rebuild online
- Definir fator de preenchimento
- Podemos pausar, parar ou cancelar se algum trabalho estiver sendo executado para esse índice. É útil se virmos algum problema de performance ou bloqueio devido a um determinado índice, podemos cancelar esse trabalho em si
ApexSQL Defrag permite criar as políticas a partir dos templates pré-definidos ou pelos parâmetros personalizados. O template também é adequado para a maioria dos requisitos de desfragmentação de índices SQL.
Usando estes templates, podemos decidir desfragmentar todos os índices ou índices com base no nível de limiar no modo offline ou online. Por exemplo, eu quero criar a política para Reconstruir online 50% dos índices mais fragmentados acima de 30% ou reorganizar se fragmentados acima de 10%.
Podemos configurar o limite de recursos em relação à CPU, carga de memória, uso do log de transações, transações ativas também. Nós não obtemos este nível de controle na manutenção do índice usando os trabalhos do assistente de manutenção ou scripts personalizados.
Se o recurso estiver ocupado, podemos definir o tempo de reentrada também. Eu tenho visto os requisitos em alguns usuários que não querem executar a manutenção após o período de utilização do banco de dados lean ter terminado. Se estamos fazendo a manutenção, precisamos parar o trabalho manualmente, ou precisamos criar outro trabalho de agente SQL para parar o trabalho de manutenção do índice em um determinado momento.
ApexSQL Defrag fornece overhead para configurar e monitorar múltiplos trabalhos. Podemos personalizar para cancelar o trabalho de manutenção do índice após ‘N’ de horas.
Na captura de tela acima, você pode ver que podemos personalizar ainda mais a operação de manutenção do índice SQL pelo nível de fragmentação, tamanho do índice e a contagem da página também. Você pode definir o atraso do objeto bloqueado por um determinado tempo. Às vezes queremos excluir o índice onde temos contagens de páginas de índice menores ou maiores que as especificadas.
Você pode fazer as alterações e configurá-las como um modelo para reutilização. Uma vez realizada a manutenção do índice, na ficha Atividades, você obtém uma excelente visão geral das estatísticas de fragmentação do índice antes e depois da manutenção. É essencial saber se o nível de fragmentação do índice está ou não sob controle após a atualização. Normalmente precisamos capturar o nível de fragmentação novamente, mas isso nos poupa as despesas gerais com essas informações.
Na seção Relatórios, podemos ver os relatórios do nível do servidor, relatório geral.
Também podemos obter uma boa visão dos 10 principais índices agrupados e não agrupados.
Também estamos interessados em conhecer o comportamento da CPU e da Memória durante a manutenção do índice. Podemos obter esta informação útil e manter um olho nos recursos do sistema também.
Podemos exportar todos os relatórios e configurar a opção de exportação. Podemos querer que informações limitadas sejam extraídas enquanto a exploramos. O ApexSQL Defrag dá-lhe a opção de escolher que informação mostrar como gráficos, estatísticas, gráficos estatísticos de índice SQL que queremos incluir no relatório.
Conclusion:
A ferramenta ApexSQL Defrag facilita aos DBA’s a tarefa administrativa com melhor monitorização e manutenção do índice SQL. Você obtém todas as informações é uma ferramenta gráfica com visuais personalizados, políticas junto com vários relatórios informativos.
