Inquiridor
Fragmentação de indices

Pergunta
-
Salve Srs.
estou com uma dúvida quanto a fragmentação de indices. Possuo algumas tabelas (pequenas, com poucas páginas) que "apresentam" alta fragmentação e que mesmo organizando, fazendo o rebuild ou dropando e criando novamente os mesmo continuam altos. Tentei ajustes no fillfactor mas alguns ainda sim apresentaram elevado indice (tem um pentelho que o melhor q consigo é 50%).
Li em um doc da Microsoft e até em foruns que isto é normal (pelo menos foi o q eu entendi) para tabelas com menos de 1000 páginas.
Todas as Respostas
-
Alves,
Falar de fragmentação e índices não é fácil!!!
O Mecanismo de Banco de Dados do SQL Server mantém os índices automaticamente sempre que são realizadas operações de entrada, atualização ou exclusão nos dados subjacentes. No decorrer do tempo, essas modificações podem fazer com que as informações do índice sejam dispersadas pelo banco de dados (fragmentadas). A fragmentação ocorre quando os índices têm páginas nas quais a ordem lógica, com base no valor de chave, não corresponde à ordem física do arquivo de dados. Índices com fragmentação pesada podem degradar o desempenho da consulta e causar lentidão de resposta do aplicativo.
O Mecanismo de Banco de Dados do SQL Server mantém os índices automaticamente sempre que são realizadas operações de entrada, atualização ou exclusão nos dados subjacentes. No decorrer do tempo, essas modificações podem fazer com que as informações do índice sejam dispersadas pelo banco de dados (fragmentadas). A fragmentação ocorre quando os índices têm páginas nas quais a ordem lógica, com base no valor de chave, não corresponde à ordem física do arquivo de dados. Índices com fragmentação pesada podem degradar o desempenho da consulta e causar lentidão de resposta do aplicativo.
Você pode solucionar a fragmentação de índice reorganizando ou recriando um índice. Para índices particionados criados em um esquema de partição, é possível usar qualquer um desses métodos em um índice completo ou em uma única partição de índice.
Detectando a fragmentação
A primeira etapa para optar pelo método de fragmentação a ser usado é analisar o índice para determinar o grau de fragmentação. Usando a função de sistema sys.dm_db_index_physical_stats, você pode detectar a fragmentação em um índice específico, em todos os índices de uma tabela ou exibição indexada, em todos os índices de um banco de dados ou em todos os índices de todos os bancos de dados. Para índices particionados, sys.dm_db_index_physical_stats também fornece informações de fragmentação por partição.
O conjunto de resultados retornado pela função sys.dm_db_index_physical_stats inclui as colunas a seguir.
Coluna Descrição avg_fragmentation_in_percent
Porcentagem de fragmentação lógica (páginas fora de ordem no índice).
fragment_count
Número de fragmentos (páginas folha fisicamente consecutivas) do índice.
avg_fragment_size_in_pages
Número médio de páginas em um fragmento de índice.
Depois que o grau de fragmentação for conhecido, use a tabela a seguir para determinar o melhor método para corrigir a fragmentação.
Valor avg_fragmentation_in_percent Instrução corretiva > 5% e < = 30%
ALTER INDEX REORGANIZE
> 30%
ALTER INDEX REBUILD WITH (ONLINE = ON)*
* A recriação de um índice pode ser executada online ou offline. A reorganização de um índice sempre é executada online.
Para atingir disponibilidade semelhante à opção de reorganização, recrie índices online. Esses valores fornecem uma diretriz elementar para a determinação do ponto em que se deve alternar entre ALTER INDEX REORGANIZE e ALTER INDEX REBUILD. Contudo, os valores reais podem variar de acordo com o caso. É importante que você experimente para poder determinar o melhor limite para um ambiente.
Níveis muito baixos de fragmentação (menos de 5 por cento) não deveriam ser resolvidos por nenhum desses comandos, uma vez que o benefício de remover uma quantidade pequena de fragmentação é quase sempre amplamente excedido pelo custo de reorganizar ou recriar o índice.
-
Caro Júnior,
justamente! venho adotando essa "regra de negocios" (excetuando-se o caso dos 5% que nunca levei em consideração). Fiz uma procedure pra realizar esta tarefa mas alguns indices não obtem "queda" da percertagem de fragmentação. O que pode justificar isto? Ouvi inclusive comentários que a desfragmentação de arquivos no disco tb pode influir (???), mas sinceramente achei bem subjetivo. Isso de certa forma é normal?
[]´s -
-
Em geral, a fragmentação em índices pequenos não é freqüentemente controlável. As páginas de índices pequenos não são armazenadas em extensões mistas. As extensões mistas são compartilhadas por até oito objetos, portanto, a fragmentação em um índice pequeno pode não ser reduzida após a reorganização ou recriação do índice. Para obter mais informações sobre eventos mistos, consulte Compreendendo páginas e extensões. Se for uma Heap Table a fragmentação também não irá reduzir ai você terá que criar um indice cluster defragmentar e exclui-lo.
Abraços,
-
-
-
-
-
Bom dia pessoal,
Me desculpem pela intromissao nesta thread, mas eu gostaria de saber qual seria o camando/procedimento especifico no sql server 2005 para que eu possa fazer esta fragmentação de indices, pois os que estão acima não deram certo, ou eu fiz alguma coisa errada.
-
Olá pessoal,
eu costumo utilizar o código abaixo para fazer as verificações:
use [database] SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; OPEN partitions; WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END; CLOSE partitions; DEALLOCATE partitions; DROP TABLE #work_to_do; GO ___________
ricardo leka -
Ricardo Leka disse:
Olá pessoal,
eu costumo utilizar o código abaixo para fazer as verificações:
use [database] SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; OPEN partitions; WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END; CLOSE partitions; DEALLOCATE partitions; DROP TABLE #work_to_do; GO ___________
ricardo leka
olá Ricardo,
sua query tá dando erro:
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.
Grato
Bruno H.