none
Reindexação de todas tabelas RRS feed

  • Pergunta

  • Pessoal, 
    Como faço a reindexação de todo o banco? Sem modificar o FillFactor dos indices, apenas recriar.
    Tentei esse comando que achei no google.
    DBCC  CHECKDB('BANCO',REPAIR_REBUILD) WITH NO_INFOMSGS; 
    go
    Sem sucesso, apesar da demora da execução, todos indices continuaram com a mesma fragmentação.
    To executando esse no mometo:
    EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
    GO
    Mas estou especificando o fillfactor, não queria fazer isso.
    att.

    Aprendiz
    • Editado Edivaldo Junior segunda-feira, 6 de junho de 2011 13:52
    • Movido Gustavo Maia Aguiar terça-feira, 7 de junho de 2011 22:13 (De:SQL Server - Desenvolvimento Geral)
    segunda-feira, 6 de junho de 2011 13:09

Respostas

  • Edivaldo,

     

    O primeiro comando postando, o que contem CHECKDB, voce esta fazendo um check de seu banco, e como voce esta especificando REAPIR_REBUILD, voce esta tentando consertar algo em seu banco, o checkdb é muito remorado pois mexe com estrutura logica e fisica do banco, e não tem nada a ver com indices, não sei se o lugar que voce pegou este codigo isso estava especificado, porem este comando não tem nada a ver com o que voce realmente quer.

     

    O comando reindex, voce devera especificar um fillfactor, mesmo por que, segue um exemplo:

    Se voce criou no começo do mes um indice com fillfactor 80, isso quer dizer que 80% das paginas de indices terão os dados do indice em si, mantendo 20% livre em cada pagina paea que caso seja inserido, alterado ou deletado qualquer dado em sua tabela, o indice possa ser alterado sem a necessidade de alocação de novas paginas, porem, esses 80% não são eternos, uma vez que as alterações são feitas, esses 80% tendem a 100%, por isso o comando de manutenção, e por isso se voce quiser manter um job de rebuild, deve-se especificar o fillfactor.

     

    O que voce pode fazer para reduzir a fragmentação de um indice é rodar um reorganize no mesmo:

    EXEC sp_MSforeachtable @command1="print 'Reorganizing indexes for ?' ALTER INDEX ALL ON ? REORGANIZE"

    Para que os efeitos do Reorganize possam ser visto por voce, após a execução do mesmo, rode um comando para atualizar as estatisticas:

    EXEC sp_updatestatistics 


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    • Marcado como Resposta Edivaldo Junior segunda-feira, 6 de junho de 2011 16:14
    segunda-feira, 6 de junho de 2011 13:22
    Moderador
  • Edivaldo,

     

    Bom, vamos la:

    O rebuild refaz todo o indice, é como se fosse dropado o indice e criado novamente, fazendo com que ele seja um indice novinho, e com algumas vantagens como especificar o fill factor por exemplo, se os indices estão com 90% de fragmentação, faça o rebuild, e depois mantenha-os diariamente com o reorganize.

    O reorganize apenas reorganiza o indice entre as paginas.

     

    Em meus ambiente normalmente eu deixo um rebuild para indices > 40% de fragmentação todos os dias, todo caso, se voce quiser, ja vi ambiente que de segunda a sexta é feito apenas reorganize, e de final de semana o rebuild de todos os indices.

     

    Em relação ao log de erro, verifique o recovery model de suas bases e tenha certeza de manter todos em simple e que os logs files possuem crescimento automatico e ilimitado (o limite passa a ser o tamanho disponivel fisicamente no disco), se mesmo assim não der, recomendo voce a adquirir mais discos.


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    • Marcado como Resposta Edivaldo Junior segunda-feira, 6 de junho de 2011 16:14
    segunda-feira, 6 de junho de 2011 15:17
    Moderador

Todas as Respostas

  • Edivaldo,

     

    O primeiro comando postando, o que contem CHECKDB, voce esta fazendo um check de seu banco, e como voce esta especificando REAPIR_REBUILD, voce esta tentando consertar algo em seu banco, o checkdb é muito remorado pois mexe com estrutura logica e fisica do banco, e não tem nada a ver com indices, não sei se o lugar que voce pegou este codigo isso estava especificado, porem este comando não tem nada a ver com o que voce realmente quer.

     

    O comando reindex, voce devera especificar um fillfactor, mesmo por que, segue um exemplo:

    Se voce criou no começo do mes um indice com fillfactor 80, isso quer dizer que 80% das paginas de indices terão os dados do indice em si, mantendo 20% livre em cada pagina paea que caso seja inserido, alterado ou deletado qualquer dado em sua tabela, o indice possa ser alterado sem a necessidade de alocação de novas paginas, porem, esses 80% não são eternos, uma vez que as alterações são feitas, esses 80% tendem a 100%, por isso o comando de manutenção, e por isso se voce quiser manter um job de rebuild, deve-se especificar o fillfactor.

     

    O que voce pode fazer para reduzir a fragmentação de um indice é rodar um reorganize no mesmo:

    EXEC sp_MSforeachtable @command1="print 'Reorganizing indexes for ?' ALTER INDEX ALL ON ? REORGANIZE"

    Para que os efeitos do Reorganize possam ser visto por voce, após a execução do mesmo, rode um comando para atualizar as estatisticas:

    EXEC sp_updatestatistics 


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    • Marcado como Resposta Edivaldo Junior segunda-feira, 6 de junho de 2011 16:14
    segunda-feira, 6 de junho de 2011 13:22
    Moderador
  • Imaginei mesmo que o REPAIR, não era o que eu procurava.

     

    Obrigado pela explicação do fillfactor.

     

    Só fiquei com uma dúvida, quando fazemos reorganize, o index fragmenta mais ainda.

    Porque pra reorganizar, vou ter que fragmentar os dados.

     

     

    att.


    Aprendiz
    segunda-feira, 6 de junho de 2011 13:26
  • Outra dúvida, quando faço a reindexação de todos indices, aumenta muito o log, como posso evitar isso?


    Aprendiz
    segunda-feira, 6 de junho de 2011 13:32
  • Edivaldo,

     

    Não entendi o que voce quis dizer, mas ao realizar um reorganize, ele ira literalmente reorganizar o indice em relação aos espaço vagos e desnecessarios, ocorrendo tambem a desfragmentação.

    Tanto que o antigo comando para o reorganize é o DBCC INDEXDEFRAG, ou seja, desfragmentation, desfragmentação. OBS: Não utilize este comando, e sim uma sintaxe de alter index, pois este comando tende a não existir mais em versões futuras.


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    segunda-feira, 6 de junho de 2011 13:35
    Moderador
  • Faça um teste simples, veja a fragmentação de uns 10 indices, depois faça o reorganize dos mesmos.

    Depois analisa a fragmentação novamente dos 10 indices. Eu digo fragmentação logica: avg_fragmentation_in_percent

     

     


    Aprendiz
    segunda-feira, 6 de junho de 2011 13:39
  • Edivaldo,

     

    Poste a query que voce esta utilizando para ver esses dados....


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    segunda-feira, 6 de junho de 2011 13:44
    Moderador
  • Fabrizzio,

    select * from sys.dm_db_index_physical_stats(DB_ID(N'banco'), OBJECT_ID(N'tabela'), NULL, NULL , 'DETAILED');

     

    Da uma confirmada ai.

    Sempre que li sobre o reorganize, dizia que aumentava a fragmentação e em testes realizados, realmente fragmentou mais.

     

     


    Aprendiz
    segunda-feira, 6 de junho de 2011 13:48
  • Edivaldo,

     

    Segue o teste, em comentario, esta o id do database (Nos meus testes fiz na master mesmo....), o id do objeto (Tabela), o id do indice, o nome do indice e sua fragmentação:

     

    CREATE TABLE ID(NOME VARCHAR(1000))

     

    INSERT INTO ID VALUES

    ('FABRIZZIO'),

    ('MARCELO'),

    ('PEDRO'),

    ('ROBERSON'),

    ('GUSTAVO')

    GO 1000

     

    CREATE INDEX IX_01 ON ID(NOME)

     

    SELECT * FROM ID

    --25 1 628197288 2 IX_01 0

    DELETE FROM ID WHERE NOME LIKE 'GUSTAVO'

    --25 1 628197288 2 IX_01 40

    ALTER INDEX IX_01 ON ID REORGANIZE

    --1 628197288 2 IX_01 0

     

     

    A query que utilizo para ver a fragmentação é esta aqui:

     

    SELECT ps.database_id, ps.OBJECT_ID,

    ps.index_id, b.name,

    ps.avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

    AND ps.index_id = b.index_id

    WHERE ps.database_id = DB_ID()

    ORDER BY ps.OBJECT_ID


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    segunda-feira, 6 de junho de 2011 13:53
    Moderador
  • Verdade, estava confundindo algumas coisas.

     

    Na verdade aumenta a fragmentação de extent. Isso você concorda?

     

     


    Aprendiz
    segunda-feira, 6 de junho de 2011 14:44
  • Edivaldo,

     

    De uma olhada neste link:

     

    http://msdn.microsoft.com/en-us/library/ms190969.aspx

     

    Preste atenção quando é dito sobre Mixed Extent ou uniform, pode ser que esta alteração esteja sendo feita, o que pode diminuir ou aumentar o do indice em si. Não creio que voce preciso se preocupar com isso, isso é interno ao SQL e gerenciado muito bem.


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    segunda-feira, 6 de junho de 2011 14:50
    Moderador
  • Interessante, então nao devo me preocupar muito com extent.

    Mas dai qual a vantagem do rebuild em cima do reorganize então? Pra mim essa era uma das maiores diferenças.

    Por isso que prefiro utilizar o rebuild.

    Li também em outras thread que é pra utilizar o reorganize quando a fragmentação for menor que 30% e rebuild quando foi maior que 30%.

     

    Nao tenho muita experiencia nisso, mas todas base de nossos clientes estão sem plano de manutenção, e fazendo um select aqui, tem varias tabelas com fragmentação de 90%+.

    A ideia era fazer um rebuild, em todos indices, mas quando fiz isso estourou o log e deu erro...como poderia evitar isso?

     

     


    Aprendiz
    segunda-feira, 6 de junho de 2011 14:56
  • Edivaldo,

     

    Bom, vamos la:

    O rebuild refaz todo o indice, é como se fosse dropado o indice e criado novamente, fazendo com que ele seja um indice novinho, e com algumas vantagens como especificar o fill factor por exemplo, se os indices estão com 90% de fragmentação, faça o rebuild, e depois mantenha-os diariamente com o reorganize.

    O reorganize apenas reorganiza o indice entre as paginas.

     

    Em meus ambiente normalmente eu deixo um rebuild para indices > 40% de fragmentação todos os dias, todo caso, se voce quiser, ja vi ambiente que de segunda a sexta é feito apenas reorganize, e de final de semana o rebuild de todos os indices.

     

    Em relação ao log de erro, verifique o recovery model de suas bases e tenha certeza de manter todos em simple e que os logs files possuem crescimento automatico e ilimitado (o limite passa a ser o tamanho disponivel fisicamente no disco), se mesmo assim não der, recomendo voce a adquirir mais discos.


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    • Marcado como Resposta Edivaldo Junior segunda-feira, 6 de junho de 2011 16:14
    segunda-feira, 6 de junho de 2011 15:17
    Moderador
  • Muito boa explicação, muito obrigado.

    Queria tirar outras dúvidas, mas não vou mudar o foco da pergunta inicial.

     

    Obrigado.

     


    Aprendiz
    segunda-feira, 6 de junho de 2011 16:14
  • Edivaldo,

     

    Perfeito, exato, nao mude o foco, se possui outras duvidas, ficarei feliz em lhe ajudar, mas abra outra thread.


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    segunda-feira, 6 de junho de 2011 16:15
    Moderador