Monitoring and maintaining Planning Server database indexes
Atualizado em: 2009-04-30
Neste artigo:
Examine Measure Group table indexes
Checking index fragmentation
Reorganizing indexes
Rebuilding indexes
Como a maioria dos sistemas de bancos de dados que usam índices para aprimorar o desempenho do sistema, o sistema Planning Server usa índices para obter o desempenho ideal. Para qualquer índice que seja de cluster ou não, INSERT/UPDATE faz com que as páginas sejam divididas, causando fragmentação. Com o passar do tempo, a fragmentação do índice se torna mais grave e torna mais lento o desempenho do sistema. É importante que o administrador do banco de dados monitore o status da fragmentação do índice e saiba o que fazer quando os índices se tornarem muito fragmentados.
É necessário monitorar o status da fragmentação do índice de todas as tabelas no banco de dados do Planning Application, mas deve ser dada atenção especial às tabelas do Grupo de Medidas, cujo nome se inicia com o prefixo "MG_". Em bancos de dados de aplicativos, as tabelas do Grupo de Medidas são submetidas a intensas operações de inserção, atualização e exclusão. Os tipos de operações que executam essas alterações de dados incluem envios de atribuições e execução de regras de cálculos. Algumas tabelas do Grupo de Medidas têm mais atividade do que outras. Os índices nas tabelas "MG_*" se tornam bastante fragmentados com o passar do tempo e reduzem o desempenho do sistema consideravelmente, se os índices não forem recriados ou organizados regularmente.
Examinar índices de tabelas do Grupo de Medidas
Quando um modelo é criado, o Planning Server cria um índice de cluster na nova tabela “MG_*”. Esse índice de cluster inclui todas as chaves de dimensão dessa tabela de Grupo de Medidas. A ordem das chaves de dimensões desse índice de cluster é organizada arbitrariamente pelo Planning Server. Essa ordem pode não refletir da melhor forma o uso da tabela de Grupo de Medidas do cliente individual (por exemplo, uso para write back, para carregamento de dados e para cálculo baseado em regras). O administrador do banco de dados talvez precise verificar a ordem das colunas do índice de cluster e reorganizá-la.
Por exemplo, o índice de cluster a seguir é criado na tabela MG_Strategic_Plan_MeasureGroup_default_partition com a ordem de colunas pré-organizada a seguir. Você pode reorganizar a ordem das colunas nesse índice para se ajustar ao uso da tabela de Grupo de Medidas. Convém mover a coluna Entity_MemberID para a primeira coluna nesse índice, porque os usuários do Suplemento PerformancePoint para Excel freqüentemente executam operações de gravação com base em Entidade.
CREATE CLUSTERED INDEX [ClusteredIndex_default_partition] ON [dbo].[MG_Strategic_Plan_MeasureGroup_default_partition]
(
[Scenario_MemberId] ASC,
[Time_Month] ASC,
[Account_MemberId] ASC,
[BusinessProcess_MemberId] ASC,
[Entity_MemberId] ASC,
[TimeDataView_MemberId] ASC,
[Currency_MemberId] ASC,
[BusinessDriver_MemberId] ASC,
[Product_MemberId] ASC,
[Flow_MemberId] ASC,
[Intercompany_MemberId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Exemplo
O exemplo a seguir mostra como recriar um índice com opção online em uma tabela de Grupo de Medidas em um banco de dados do Aplicativo de Planejamento.
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);
O administrador do banco de dados deve criar um trabalho no computador que está executando o Microsoft SQL Server 2005 para desfragmentar o índice em cada tabela de Grupo de Medidas. Dependendo do grau de fragmentação, agende o trabalho para reorganizar o índice e recriá-lo em freqüências diferentes.
Você pode optar por reorganizar o índice uma vez por dia e recriá-lo uma vez por semana. Alguns índices podem exigir recriações mais freqüentes, como os índices de cluster na tabela do Grupo de Medidas. Agende os trabalhos de recriação de índices em horários de pouca atividade, como à meia-noite. Ambos os métodos podem ser executados online, exceto as tabelas, que têm tipos de dados de linha de negócios. Se optar por executar a recriação do índice offline em vez de executá-la online, você precisará colocar o aplicativo offline primeiro. Para obter informações sobre como colocar um aplicativo offline, consulte a Ajuda do Console de Administração do Planning.
Verificando a fragmentação do índice
Com o passar do tempo, as modificações no banco de dados podem fazer com que as informações no índice fiquem espalhadas ou fragmentadas no banco de dados. Índices extremamente fragmentados podem prejudicar o desempenho de consultas e fazer com que o aplicativo responda lentamente.
No SQL Server 2005, você pode corrigir a fragmentação do índice reorganizando-o ou recriando-o. É necessário analisar o índice para determinar o grau de fragmentação antes de decidir qual o método de desfragmentação a ser usado.
Exemplo
O exemplo a seguir mostra como monitorar a fragmentação do índice.
Quando você executar a seguinte instrução de código:
SELECT a.index_id, name, avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('MG_Strategic_Plan_MeasureGroup_default_partition'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
um conjunto de resultados semelhante ao seguinte será retornado.
| index_id | name | avg_fragmentation_in_percent |
|---|---|---|
1 |
MG_Strategic_Plan_MeasureGroup_default_partition_PK |
23.076923076923077 |
Para obter mais informações sobre fragmentação, consulte SQL Server Books Online . Neste exemplo, a resolução recomendada é reorganizar MG_Strategic_Plan_MeasureGroup_default_partition_PK, porque avg_fragmentation_in_percent é inferior a 30 por cento.
Se a fragmentação fosse superior a 30 por cento, a recomendação seria recriar o índice.
.gif "Observação") Observação: |
|---|
Os 30 por cento anteriores provêm da recomendação de SQL Server Books Online. Você pode alterar esse número para atender melhor às necessidades de sua organização. |
Reorganizando índices
É uma prática recomendada reorganizar um índice quando ele não está extremamente fragmentado. No entanto, se o índice estiver muito fragmentado, você obterá resultados melhores recriando-o. Para obter diretrizes sobre fragmentação, consulte Checking Index Fragmentation.
Exemplo
O exemplo a seguir mostra como reorganizar o índice de chave primária em uma tabela de Grupo de Medidas no Planning Server. Para reorganizar um ou mais índices, use a declaração ALTER INDEX com a cláusula REORGANIZE.
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REORGANIZE ;
A reorganização do índice não é um processo tão exaustivo quanto recriar o índice; ela apenas corrige a fragmentação no nível de folha. Ela não move o objeto para estender mais a densidade de verificação. O comando de reorganização de índice é sempre executado online. Nenhum bloqueio de execução longa é mantido durante a reorganização do índice. Para obter informações sobre reorganização de índices e os prós e contras de reorganizar e recriar um índice, consulte SQL Server Books Online.
Recriando índices
Um índice pode ser recriado se for eliminado e um novo índice for criado. Quando um novo índice é criado, a fragmentação é removida.
O exemplo a seguir mostra como recriar um índice com a opção online em uma tabela de Grupo de Medidas no Planning Server.
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);
Agende um trabalho do SQL Server para desfragmentar o índice em cada tabela de Grupo de Medidas. Após determinar o grau de fragmentação, agende o trabalho para executar a reorganização do índice e uma recriação do índice em freqüências diferentes. Você pode optar por reorganizar o índice uma vez por dia, agendá-lo para horários de pouca atividade, como à meia-noite, e executar uma recriação do índice apenas uma vez por semana. Ambos os métodos podem ser executados online, exceto as tabelas, que têm tipos de dados de linha de negócios.
A recriação de um índice geralmente fornece resultados melhores do que a reorganização do índice. Ela remove todos os níveis de fragmentação no nível de folha e árvore B, equilibrando novamente a árvore. Ela atualiza as estatísticas, agindo de forma semelhante à execução de uma "verificação completa", com estatísticas precisas.
Para obter mais informações sobre como recriar índices, consulte SQL Server Books Online.