none
DBCC INDEXDEFRAG em todas as tabelas do Banco de Dados? RRS feed

Respostas

  • Clayton,

    Tem um exemplo no BOL do 2005 muito iteressante que calcula a necessidade de um rebuild ou apenas um defrag, mas nao tenho mais o bol do 2005 e nao cosegui localizar na net este script... mas de qq modo segue um script para defrag de todas as tabelas  (coletado do BOL 2008)

    /*Perform a 'USE <database name>' to select the database in which to run the script.*/
    -- Declare variables
    SET NOCOUNT ON;
    DECLARE @tablename varchar(255);
    DECLARE @execstr   varchar(400);
    DECLARE @objectid  int;
    DECLARE @indexid   int;
    DECLARE @frag      decimal;
    DECLARE @maxfrag   decimal;
    
    -- Decide on the maximum fragmentation to allow for.
    SELECT @maxfrag = 30.0;
    
    -- Declare a cursor.
    DECLARE tables CURSOR FOR
       SELECT TABLE_SCHEMA + '.' + TABLE_NAME
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE';
    
    -- Create the table.
    CREATE TABLE #fraglist (
       ObjectName char(255),
       ObjectId int,
       IndexName char(255),
       IndexId int,
       Lvl int,
       CountPages int,
       CountRows int,
       MinRecSize int,
       MaxRecSize int,
       AvgRecSize int,
       ForRecCount int,
       Extents int,
       ExtentSwitches int,
       AvgFreeBytes int,
       AvgPageDensity int,
       ScanDensity decimal,
       BestCount int,
       ActualCount int,
       LogicalFrag decimal,
       ExtentFrag decimal);
    
    -- Open the cursor.
    OPEN tables;
    
    -- Loop through all the tables in the database.
    FETCH NEXT
       FROM tables
       INTO @tablename;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN;
    -- Do the showcontig of all indexes of the table
       INSERT INTO #fraglist 
       EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
          WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
       FETCH NEXT
          FROM tables
          INTO @tablename;
    END;
    
    -- Close and deallocate the cursor.
    CLOSE tables;
    DEALLOCATE tables;
    
    -- Declare the cursor for the list of indexes to be defragged.
    DECLARE indexes CURSOR FOR
       SELECT ObjectName, ObjectId, IndexId, LogicalFrag
       FROM #fraglist
       WHERE LogicalFrag >= @maxfrag
          AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
    
    -- Open the cursor.
    OPEN indexes;
    
    -- Loop through the indexes.
    FETCH NEXT
       FROM indexes
       INTO @tablename, @objectid, @indexid, @frag;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN;
       PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
          ' + RTRIM(@indexid) + ') - fragmentation currently '
           + RTRIM(CONVERT(varchar(15),@frag)) + '%';
       SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
           ' + RTRIM(@indexid) + ')';
       EXEC (@execstr);
    
       FETCH NEXT
          FROM indexes
          INTO @tablename, @objectid, @indexid, @frag;
    END;
    
    -- Close and deallocate the cursor.
    CLOSE indexes;
    DEALLOCATE indexes;
    
    -- Delete the temporary table.
    DROP TABLE #fraglist;
    GO

    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    • Marcado como Resposta Clayton Santos terça-feira, 13 de abril de 2010 17:04
    terça-feira, 13 de abril de 2010 14:24
    Moderador
  • Veja este script

    --cria a proc
    
    create proc sp_executa_reorgs @banco varchar(60)
    as
    SET NOCOUNT ON;
    DECLARE @tablename varchar(255);
    DECLARE @execstr  varchar(400);
    DECLARE @objectid int;
    DECLARE @indexid  int;
    DECLARE @frag   decimal;
    DECLARE @maxfrag  decimal;
    DECLARE @strSQL  varchar(max);
    
    -- Decide on the maximum fragmentation to allow for.
    SELECT @maxfrag = 30.0;
    
    CREATE TABLE #TableList (ObjectName varchar(255))
    
    set @strSQL='SELECT TABLE_SCHEMA + ''.'' + TABLE_NAME
    FROM '+@banco+'.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = ''BASE TABLE'';'
    
    insert into #TableList
    exec (@strSQL)
    
    -- Declare a cursor.
    DECLARE tables CURSOR FOR
    
    select * from #TableList
    
    -- Create the table.
    CREATE TABLE #fraglist (
      ObjectName char(255),
      ObjectId int,
      IndexName char(255),
      IndexId int,
      Lvl int,
      CountPages int,
      CountRows int,
      MinRecSize int,
      MaxRecSize int,
      AvgRecSize int,
      ForRecCount int,
      Extents int,
      ExtentSwitches int,
      AvgFreeBytes int,
      AvgPageDensity int,
      ScanDensity decimal,
      BestCount int,
      ActualCount int,
      LogicalFrag decimal,
      ExtentFrag decimal);
      
    -- Open the cursor.
    OPEN tables;
    
    -- Loop through all the tables in the database.
    FETCH NEXT
      FROM tables
      INTO @tablename;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN;
    -- Do the showcontig of all indexes of the table
      INSERT INTO #fraglist 
      EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
       WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
      FETCH NEXT
       FROM tables
       INTO @tablename;
    END;
    
    -- Close and deallocate the cursor.
    CLOSE tables;
    DEALLOCATE tables;
    
    -- Declare the cursor for the list of indexes to be defragged.
    DECLARE indexes CURSOR FOR
      SELECT ObjectName, ObjectId, IndexId, LogicalFrag
      FROM #fraglist
      WHERE LogicalFrag >= @maxfrag
       AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
    
    -- Open the cursor.
    OPEN indexes;
    
    -- Loop through the indexes.
    FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN;
      PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
       ' + RTRIM(@indexid) + ') - fragmentation currently '
        + RTRIM(CONVERT(varchar(15),@frag)) + '%';
      SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
        ' + RTRIM(@indexid) + ')';
      EXEC (@execstr);
    
      FETCH NEXT
       FROM indexes
       INTO @tablename, @objectid, @indexid, @frag;
    END;
    
    -- Close and deallocate the cursor.
    CLOSE indexes;
    DEALLOCATE indexes;
    
    -- Delete the temporary table.
    DROP TABLE #fraglist;
    DROP TABLE #TableList;
    go
    
    
    --exemplo de chamada
    sp_msforeachdb 'use ?; exec sp_executa_reorgs "?"'
    

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    • Marcado como Resposta Clayton Santos quarta-feira, 2 de fevereiro de 2011 19:31
    quarta-feira, 2 de fevereiro de 2011 19:14
    Moderador

Todas as Respostas

  • Clayton,

    Tem um exemplo no BOL do 2005 muito iteressante que calcula a necessidade de um rebuild ou apenas um defrag, mas nao tenho mais o bol do 2005 e nao cosegui localizar na net este script... mas de qq modo segue um script para defrag de todas as tabelas  (coletado do BOL 2008)

    /*Perform a 'USE <database name>' to select the database in which to run the script.*/
    -- Declare variables
    SET NOCOUNT ON;
    DECLARE @tablename varchar(255);
    DECLARE @execstr   varchar(400);
    DECLARE @objectid  int;
    DECLARE @indexid   int;
    DECLARE @frag      decimal;
    DECLARE @maxfrag   decimal;
    
    -- Decide on the maximum fragmentation to allow for.
    SELECT @maxfrag = 30.0;
    
    -- Declare a cursor.
    DECLARE tables CURSOR FOR
       SELECT TABLE_SCHEMA + '.' + TABLE_NAME
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE';
    
    -- Create the table.
    CREATE TABLE #fraglist (
       ObjectName char(255),
       ObjectId int,
       IndexName char(255),
       IndexId int,
       Lvl int,
       CountPages int,
       CountRows int,
       MinRecSize int,
       MaxRecSize int,
       AvgRecSize int,
       ForRecCount int,
       Extents int,
       ExtentSwitches int,
       AvgFreeBytes int,
       AvgPageDensity int,
       ScanDensity decimal,
       BestCount int,
       ActualCount int,
       LogicalFrag decimal,
       ExtentFrag decimal);
    
    -- Open the cursor.
    OPEN tables;
    
    -- Loop through all the tables in the database.
    FETCH NEXT
       FROM tables
       INTO @tablename;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN;
    -- Do the showcontig of all indexes of the table
       INSERT INTO #fraglist 
       EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
          WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
       FETCH NEXT
          FROM tables
          INTO @tablename;
    END;
    
    -- Close and deallocate the cursor.
    CLOSE tables;
    DEALLOCATE tables;
    
    -- Declare the cursor for the list of indexes to be defragged.
    DECLARE indexes CURSOR FOR
       SELECT ObjectName, ObjectId, IndexId, LogicalFrag
       FROM #fraglist
       WHERE LogicalFrag >= @maxfrag
          AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
    
    -- Open the cursor.
    OPEN indexes;
    
    -- Loop through the indexes.
    FETCH NEXT
       FROM indexes
       INTO @tablename, @objectid, @indexid, @frag;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN;
       PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
          ' + RTRIM(@indexid) + ') - fragmentation currently '
           + RTRIM(CONVERT(varchar(15),@frag)) + '%';
       SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
           ' + RTRIM(@indexid) + ')';
       EXEC (@execstr);
    
       FETCH NEXT
          FROM indexes
          INTO @tablename, @objectid, @indexid, @frag;
    END;
    
    -- Close and deallocate the cursor.
    CLOSE indexes;
    DEALLOCATE indexes;
    
    -- Delete the temporary table.
    DROP TABLE #fraglist;
    GO

    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    • Marcado como Resposta Clayton Santos terça-feira, 13 de abril de 2010 17:04
    terça-feira, 13 de abril de 2010 14:24
    Moderador
  • Olá Marcelo.

    Te agradeço pela ajuda! Mas esse irá servir sim.

    Um forte abraço


    Clayton Santos
    clayton1745@gmail.com
    http://clayton1745.spaces.live.com
    http://www.blogdoclayton.blog.com.es
    terça-feira, 13 de abril de 2010 17:04
  • Eu havia esquecido que: Outra forma prática de realizar um reorg de todos os indices é o plano de manutenção do próprio SQL Server.

    Abraços


    Clayton Santos
    clayton1745@gmail.com
    http://clayton1745.spaces.live.com
    http://www.blogdoclayton.blog.com.es
    sexta-feira, 16 de abril de 2010 14:45
  • Olá Marcelo, preciso de uma ajuda sobre esse script.

    Como faço para criar uma procedure desse script e nele colocar um laço para que faça em todas as bases que tenho no servidor. Exemplo:

    Tenho as bases

    Base 1
    Base 2
    Base 3
    Base 4

    Criar procudure 'sp_executa_reorgs' com o script que me enviou acima mas esse script tem que ter um while para usar as minhas bases uma por uma para fazer o reorg dos indices.

    Se tiver como, por favor da uma ajuda.

    Abraço

    Clayton Santos


    Clayton Santos
    clayton1745@gmail.com
    http://clayton1745.spaces.live.com
    http://www.blogdoclayton.blog.com.es
    segunda-feira, 26 de abril de 2010 18:12
  • crie a proc e use o sp_msforeachdb 'sp_executa_reorgs'

    Agende esta execucao... depende do tamanho de suas bases pode demorar um pouco...

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    segunda-feira, 26 de abril de 2010 20:10
    Moderador
  • Olá Marcelo. Desculpe não ter respondido antes.

    Na época acabei fazendo manualmente mesmo por que não eram muitos databases. Porém agora tenho dezenas e precisava de uma forma há para colocar em um job.

    Tentei fazer conforme informou utilizando essa procedure  sp_msforeachdb e não consegui. Você consegue montar um exemplo pra mim?

    Um forte abraço


    Clayton Santos
    clayton1745@gmail.com
    http://clayton1745.spaces.live.com

    ITIL|MCTS
    quarta-feira, 2 de fevereiro de 2011 18:13
  • Eae Clayton...

    Segue alguns exemplos...

    --executa o sp_helpfile para cada base
    sp_msforeachdb 'sp_helpFile'
    
    --print com nome de cada base
    sp_msforeachdb 'print "?"'
    
    --exemplo com sua proc, enviando o nome do banco como parametro
    sp_msforeachdb 'sp_executa_reorgs "?"'
    
    

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    quarta-feira, 2 de fevereiro de 2011 18:18
    Moderador
  • Olá Marcelo... blz.

    Entendi suas dicas e legal essa procedure sp_msforeachdb ajuda e muito... o problema pra mim ta sendo na verdade desenvolver a procedure sp_executa_reorgs por que nao to conseguindo fazer uma forma que ele entenda que tem que passar por todas as bases de dados utilizando o script que me passou acima.

    abraço


    Clayton Santos
    clayton1745@gmail.com
    http://clayton1745.spaces.live.com

    ITIL|MCTS
    quarta-feira, 2 de fevereiro de 2011 18:35
  • Veja este script

    --cria a proc
    
    create proc sp_executa_reorgs @banco varchar(60)
    as
    SET NOCOUNT ON;
    DECLARE @tablename varchar(255);
    DECLARE @execstr  varchar(400);
    DECLARE @objectid int;
    DECLARE @indexid  int;
    DECLARE @frag   decimal;
    DECLARE @maxfrag  decimal;
    DECLARE @strSQL  varchar(max);
    
    -- Decide on the maximum fragmentation to allow for.
    SELECT @maxfrag = 30.0;
    
    CREATE TABLE #TableList (ObjectName varchar(255))
    
    set @strSQL='SELECT TABLE_SCHEMA + ''.'' + TABLE_NAME
    FROM '+@banco+'.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = ''BASE TABLE'';'
    
    insert into #TableList
    exec (@strSQL)
    
    -- Declare a cursor.
    DECLARE tables CURSOR FOR
    
    select * from #TableList
    
    -- Create the table.
    CREATE TABLE #fraglist (
      ObjectName char(255),
      ObjectId int,
      IndexName char(255),
      IndexId int,
      Lvl int,
      CountPages int,
      CountRows int,
      MinRecSize int,
      MaxRecSize int,
      AvgRecSize int,
      ForRecCount int,
      Extents int,
      ExtentSwitches int,
      AvgFreeBytes int,
      AvgPageDensity int,
      ScanDensity decimal,
      BestCount int,
      ActualCount int,
      LogicalFrag decimal,
      ExtentFrag decimal);
      
    -- Open the cursor.
    OPEN tables;
    
    -- Loop through all the tables in the database.
    FETCH NEXT
      FROM tables
      INTO @tablename;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN;
    -- Do the showcontig of all indexes of the table
      INSERT INTO #fraglist 
      EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
       WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
      FETCH NEXT
       FROM tables
       INTO @tablename;
    END;
    
    -- Close and deallocate the cursor.
    CLOSE tables;
    DEALLOCATE tables;
    
    -- Declare the cursor for the list of indexes to be defragged.
    DECLARE indexes CURSOR FOR
      SELECT ObjectName, ObjectId, IndexId, LogicalFrag
      FROM #fraglist
      WHERE LogicalFrag >= @maxfrag
       AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
    
    -- Open the cursor.
    OPEN indexes;
    
    -- Loop through the indexes.
    FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN;
      PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
       ' + RTRIM(@indexid) + ') - fragmentation currently '
        + RTRIM(CONVERT(varchar(15),@frag)) + '%';
      SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
        ' + RTRIM(@indexid) + ')';
      EXEC (@execstr);
    
      FETCH NEXT
       FROM indexes
       INTO @tablename, @objectid, @indexid, @frag;
    END;
    
    -- Close and deallocate the cursor.
    CLOSE indexes;
    DEALLOCATE indexes;
    
    -- Delete the temporary table.
    DROP TABLE #fraglist;
    DROP TABLE #TableList;
    go
    
    
    --exemplo de chamada
    sp_msforeachdb 'use ?; exec sp_executa_reorgs "?"'
    

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    • Marcado como Resposta Clayton Santos quarta-feira, 2 de fevereiro de 2011 19:31
    quarta-feira, 2 de fevereiro de 2011 19:14
    Moderador
  • Marcelão, eu preciso criar essa procedure em cada banco de dados, certo?

    Por que ele me informa que não econtra nos outros bancos.

    Abraço

    Obrigado pela ajuda/paciencia


    Clayton Santos
    clayton1745@gmail.com
    http://clayton1745.spaces.live.com

    ITIL|MCTS
    quarta-feira, 2 de fevereiro de 2011 19:21
  • não bastar criar na master ou eum banco de administração que vc tenha ai em seu ambiente e executar o comando deste banco...

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    quarta-feira, 2 de fevereiro de 2011 19:28
    Moderador
  • o que pode ocorrer é mensagem de erro para a base do TempDb! para outras bases ele deve achar as tabelas... fiz um teste aqui....

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    quarta-feira, 2 de fevereiro de 2011 19:29
    Moderador
  • Olá Marcelo... é verdade. Eu havia criado em outro banco de usuário e tinha dado errado. Criei ele no master e executei...

     

    Agora deu certinho.

     

    Muito obrigado pela sua ajuda!!!

     

    Abraço


    Clayton Santos
    clayton1745@gmail.com
    http://clayton1745.spaces.live.com

    ITIL|MCTS
    quarta-feira, 2 de fevereiro de 2011 19:31
  • Clayton,

    existe essa rotina que utiliza varios recursos de criação de indices, como por exemplo:

    • ordenar na tempdb
    •  se tiver mais de uma partição, executar o reindex por particionamento.
    •  executa reindex online
    • Colocar o tempo maximo para execução do job de reindex.
    • etc...

     

    http://sqlfool.com/2010/04/index-defrag-script-v4-0/

     

    []'s!


    http://www.diaadiasql.com.br
    • Sugerido como Resposta Renato J. Alves quinta-feira, 3 de fevereiro de 2011 00:49
    quinta-feira, 3 de fevereiro de 2011 00:49
  • Olá marcelo, como vai?

    Estou encontrando alguns problemas sobre a execução do reorg dos indices e gostaria muito de sua ajuda em colocar uma condição para não pegar as bases de sistemas, ou seja, queria colocar uma condição no sp_msforeachdb dizendo para ele pegar todas as bases de dados exceto as de sistemas. Tem como?

    Um forte abraço


    Clayton Santos
    clayton1745@gmail.com
    ITIL|MCTS
    sexta-feira, 15 de julho de 2011 13:36
  • tenta chamar assim:

    sp_msforeachdb 'use ?; if DB_ID("?")>4 exec sp_executa_reorgs "?"'
    


    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    sexta-feira, 15 de julho de 2011 13:49
    Moderador
  • Ola Marcelo

    Quando executo (lembrando que estou fanzendo nas duas versões SQL Server 2000 e 2005) ele não encontra os databases.

    Msg 207, Level 16, State 3, Line 1

    Invalid column name 'baseTeste'.

    Msg 207, Level 16, State 3, Line 1

    Invalid column name 'baseTeste1'.

    Msg 207, Level 16, State 3, Line 1

    Invalid column name 'baseTeste2'.

    Um forte abraço


    Clayton Santos
    clayton1745@gmail.com
    ITIL|MCTS
    sexta-feira, 15 de julho de 2011 14:11