Desfragmentando índices para bancos de dados do Project Server 2007

  • Artigo

Atualizado: setembro de 2008

 

Tópico modificado em: 2015-02-27

As tarefas de manutenção de banco de dados podem ser executadas por meio de comandos Transact-SQL ou do Assistente para Manutenção de Banco de Dados. Este artigo fornece detalhes sobre ambas as abordagens.

As tarefas de manutenção de banco de dados recomendadas para bancos de dados do Microsoft Office Project Server 2007 incluem os seguintes tópicos:

  • Verificando a integridade do banco de dados

  • Desfragmentando índices através de reorganização ou recriação

  • Definindo o fator de preenchimento de um servidor

  • Monitorando o tamanho do banco de dados para aumentar previamente ou reduzir bancos de dados

  • Limpando o histórico

  • Atualizando estatísticas

Desfragmentando índices por meio de reorganização ou recriação

A fragmentação ocorre quando a alocação de armazenamento lógico e físico de um banco de dados contém muitas áreas de armazenamento espalhadas insuficientes, não fisicamente contíguas ou muito fragmentadas para serem usadas com eficiência. A fragmentação pode ser o resultado de muitas inserções, atualizações ou exclusões de uma tabela. Quando uma tabela fica fragmentada, os índices definidos na tabela também ficam.

O Office Project Server 2007 usa tipos de GUID como chaves de cluster, o que evita inserções simultâneas competindo pelas mesmas páginas de dados (pontos de acesso de inserção), mas que pode causar fragmentação de tabela e de índice. A fragmentação pode ocorrer porque novos registros podem ser inseridos em qualquer lugar da árvore b, e não no final, o que gera maior tendência de divisão de página (índice e dados) e, portanto, fragmentação. Isso é reduzido pelo agrupamento em cluster nas chaves de composição que usam a UID do projeto para garantir que páginas de dados contenham dados correlatos, mas a desfragmentação regular das tabelas maiores melhora o desempenho, especialmente em grandes implantações do Office Project Server 2007.

Com o passar do tempo, a fragmentação do banco de dados pode resultar em degradação do desempenho (atividade desnecessária do disco) e utilização de espaço ineficiente. Para reduzir a fragmentação e reduzir sua taxa de ocorrência, defina manualmente os bancos de dados de conteúdo com o maior tamanho possível, conforme seus requisitos de negócios e a arquitetura do banco de dados. Por exemplo: existindo o requisito de limitar os bancos de dados de conteúdo a 100 gigabytes (GB), depois de criar os bancos de dados de conteúdo defina seu tamanho para 100 GB no SQL Server Management Studio.

Embora seja possível desfragmentar tabelas, os índices de desfragmentação são mais úteis para o desempenho do banco de dados e muito mais rápidos. Este artigo apenas descreve como desfragmentar índices.

Antes de implementar um plano de manutenção de fragmentação do banco de dados, determine quais tabelas e índices estão mais fragmentados e crie um plano de manutenção para recriar ou reorganizar os índices.

A medição da fragmentação pode ser feita da seguinte maneira:

  • No SQL Server 2005, use o modo de exibição de gerenciamento dinâmico sys.dm_db_index_physical_stats

  • No SQL Server 2000, use DBCC SHOWCONTIG

Observe que o algoritmo para calcular a fragmentação é mais preciso em sys.dm_db_index_physical_stats do que em DBCC SHOWCONTIG. Como resultado, os valores de fragmentação calculados por sys.dm_db_index_physical_stats parecem mais altos.

Medindo a fragmentação com sys.dm_db_index_physical_stats (SQL Server 2005)

No SQL Server 2005, use o modo de gerenciamento dinâmico sys.dm_db_index_physical_stats para determinar a fragmentação dos índices de uma tabela ou modo de exibição especificado.

Para medir a fragmentação, recomendamos monitorar a coluna avg_fragmentation_in_percent. O valor de avg_fragmentation_in_percent deve ser o mais próximo possível de zero para produzir desempenho máximo. No entanto, valores de 0% a 10% são aceitáveis.

Para obter informações sobre como usar sys.dm_db_index_physical_stats, consulte sys.dm_db_index_physical_stats (em inglês) (https://go.microsoft.com/fwlink/?linkid=128479\&clcid=0x416) (em inglês).

Medindo a fragmentação com DBCC SHOWCONTIG (SQL Server 2000)

Para verificar a fragmentação de tabelas de banco de dados, o administrador pode usar a função DBCC SHOWCONTIG para relatar a fragmentação de verificação lógica e de extensão. Para obter uma explicação completa dos resultados de DBCC SHOWCONTIG, consulte DBCC SHOWCONTIG (em inglês) (https://go.microsoft.com/fwlink/?linkid=110841\&clcid=0x416) (em inglês).

Para medir a fragmentação, recomendamos monitorar o valor da densidade de verificação retornado por DBCC SHOWCONTIG. Em tabelas em que tudo é contíguo, a densidade de verificação é 100.

Reduzindo a fragmentação de um banco de dados

Para reduzir o nível de fragmentação de índice, execute o procedimento indicado em Como desfragmentar bancos de dados do Windows SharePoint Services 3.0 e SharePoint Server 2007 (https://go.microsoft.com/fwlink/?linkid=110843\&clcid=0x416) na Base de Dados de Conhecimento da Microsoft.

Após determinar o nível de fragmentação dos bancos de dados, você pode agendar o procedimento armazenado para ser executado diária, semanal ou mensalmente, dependendo de suas necessidades e da taxa geral de alteração em seu ambiente. Em geral, é recomendável estabelecer uma agenda semanal de desfragmentação, no mínimo. Também recomendamos agendar operações de desfragmentação após a execução de operações DBCC CHECKDB REPAIR.

Este procedimento armazenado altera os índices do banco de dados de conteúdo. Não há suporte para qualquer modificação do procedimento armazenado. Para obter informações adicionais sobre as alterações com suporte para bancos de dados de conteúdo de Produtos e Tecnologias do SharePoint, consulte Suporte para alterações em bancos de dados usados por produtos do servidor do Office e pelo Windows SharePoint Services (https://go.microsoft.com/fwlink/?linkid=110844\&clcid=0x416) na Base de Dados de Conhecimento da Microsoft.

Reduzindo a fragmentação de uma tabela específica e seus índices

Se você deseja desfragmentar o índice associado a uma tabela específica, em vez de um banco de dados inteiro, você poderá reorganizar ou recriar o índice. Para obter mais informações, consulte Estruturas de índice em cluster (em inglês) (https://go.microsoft.com/fwlink/?linkid=128480\&clcid=0x416) (em inglês).

Reorganizar um índice significa que o nível de folha do índice será reorganizado. A reorganização de índice desfragmenta e compacta índices agrupados e não agrupados em cluster em tabelas e modos de exibição e pode melhorar significativamente o desempenho de verificação de índice. A reorganização será sempre executada on-line, para que a tabela de base esteja disponível para os usuários. A reorganização equivale à instrução DBCC INDEXDEFRAG do SQL Server 2000.

Recriar um índice significa que o índice será recriado usando as mesmas colunas, tipo de índice, atributo de exclusividade e ordem de classificação. A reconstrução melhora o desempenho de buscas e verificações de índice. Você pode recriar o índice com uma tabela on-line ou off-line. Recriar equivale à instrução DBCC DBREINDEX do SQL Server 2000.

O nível de fragmentação de um índice determina o método que deve ser usado para desfragmentá-lo, e se ele pode permanecer online ou se deve ser colocado offline.

Nível de fragmentação Método de desfragmentação

Até 10%

Reorganizar (online)

10% a 75%

Recriar (online)

75% ou mais

Recriar (offline)

Observe que o uso dos comandos DROP INDEX e CREATE INDEX não é permitido em bancos de dados de Produtos e Tecnologias do SharePoint.

Você pode reorganizar e recriar índices usando a instrução ALTER INDEX do SQL Server 2005, o Assistente de Manutenção do SQL Server 2005, as instruções DBCC INDEXDEFRAG e DBCC DBREINDEX do SQL Server 2000 ou o Assistente de Manutenção do SQL Server 2000. Este tópico apresenta somente as opções do SQL Server 2005 em detalhes. Para obter mais informações sobre as opções do SQL Server 2000, consulte os seguintes recursos:

Usando o ALTER INDEX

O ALTER INDEX permite que o administrador de banco de dados execute operações de manutenção em uma tabela ou índice de modo de exibição existente. Ele pode ser usado para desabilitar, recriar e reorganizar índices ou, opcionalmente, configurar opções no índice. O ALTER INDEX substitui as instruções DBCC DBREINDEX e DBCC INDEXDEFRAG.

Na maioria dos casos você pode reconstruir índices enquanto o banco de dados está online, porque não há ganho significativo em uma reconstrução dos índices off-line. No entanto, é importante observar que quando um índice está sendo reconstruído, um bloqueio de tabela compartilhada é colocado na tabela, impedindo que todas as operações, com exceção de operações SELECT, sejam executadas. Os bancos de dados de Produtos e Tecnologias do SharePoint usam especificamente índices agrupados em cluster. Quando um índice de cluster está sendo reconstruído, um bloqueio de tabela exclusivo é colocado na tabela, impedindo o acesso de usuários finais a qualquer tabela.

Você pode personalizar o seguinte exemplo de script para reconstruir todos os índices de uma tabela.

USE Contoso_Content_1
GO
ALTER INDEX ALL ON [database_name. [ schema_name ] . | schema_name. ]table_or_view_name
REBUILD WITH (FILLFACTOR = 70, SORT_IN_TEMPDB = ON, ONLINE = ON,
STATISTICS_NORECOMPUTE = ON)
GO

Consideração especial sobre o Banco de Dados de Relatório

Como esperamos que os clientes implementem relatórios personalizados baseados em campos personalizados e dados disponíveis no banco de dados de relatório, as seguintes práticas são recomendadas para escrever T-SQL e criar índice, para garantir uma solução de relatório escalonável e de desempenho. O Office Project Server 2007 não indexa essas tabelas (geradas dinamicamente) fora da chave primária. O Atualização de Infraestrutura para os Microsoft Office Servers fornece funcionalidade adicional. Para obter mais informações, consulte a seção "Otimizações do RDS para campos personalizados" no artigo sobre a versão de atualização da infraestrutura do Project 2007 para servidor e cliente (https://go.microsoft.com/fwlink/?linkid=121912\&clcid=0x416).

Ao trabalhar com o Suporte e Atendimento ao Cliente Microsoft, o engenheiro de suporte pode solicitar que você remova todos os índices adicionais criados ou todas as colunas adicionais adicionadas aos índices existentes. Isso ocorre porque índices adicionais podem alterar os caminhos de acesso a dados e em algumas situações pode levar a desempenho inesperado e problemas de bloqueio/travamento.

Definindo o fator de preenchimento de um servidor

O fator de preenchimento pode ser usado para melhorar ainda mais o desempenho e o armazenamento de dados do índice. Quando os índices são criados ou reconstruídos, o valor do fator de preenchimento (1 a 100) determina a porcentagem de espaço em cada página de nível folha que pode ser preenchida com dados. O espaço restante é reservado para crescimento futuro. Em muitas situações, um nível de fator de preenchimento padrão do servidor inteiro 0 é ideal; no entanto, para o Microsoft Office SharePoint Server 2007, uma configuração de servidor de 70 é ideal para dar suporte ao crescimento e reduzir a fragmentação.

Embora seja possível, não recomendamos definir o fator de preenchimento para tabelas ou índices individuais.

Para exibir o valor do fator de preenchimento de um ou mais índices, consulte o modo de exibição de catálogo sys.indexes. Para obter mais informações sobre o modo de exibição, consulte sys.indexes (Transact-SQL) (em inglês) (https://go.microsoft.com/fwlink/?linkid=128510\&clcid=0x416) (em inglês).

Para configurar o valor do fator de preenchimento do servidor, use o procedimento armazenado do sistema sp_configure. Para obter mais informações, consulte spconfigure (Transact-SQL) (em inglês) (https://go.microsoft.com/fwlink/?linkid=128512\&clcid=0x416) (em inglês).