none
Duvidas Alter Index Rebuild e Alter Index Reorganize RRS feed

  • Pergunta

  • Boa Noite a Todos!

    Hoje desenvolvi uma SP onde identifico quais indices necessitam de Rebuild (maior que 30.0) e Reorganize (entre 5 e 30), mas existem alguns casos onde mesmo executando a rotina existem indices que não sofreram nenhuma modificação conforme esse exemplo (85.7143 - Executado: ALTER INDEX [IDX_codigo] ON [IMAC_ATERS_ADM].[dbo].[tbCidade] REBUILD).

    O que percebi que as Pk's tambem não sofreram nenhum ação com o comando. Caso alguem queira dar uma olhada nessa SP me avise onde postar.


    Segue abaixo o codigo da SP:

    DECLARE

                      @vobjectid int,

                      @vindexid int,

                      @vpartitioncount bigint,

                      @vschemaname varchar(130),

                      @vobjectname varchar(130),

                      @vindexname varchar(130),

                      @vpartitionnum bigint,

                      @vpartitions bigint,

                      @vfrag float,

                      @vcommand varchar(2000),

                     

                      @vSql1 nvarchar(2000),

                      @vParam nvarchar(2000),

                     

                      @vSql2 nvarchar(2000),

                      @vParam2 nvarchar(2000),

                     

                      @vSql3 nvarchar(2000),

                      @vParam3 nvarchar(2000)

     

          -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

          -- and convert object and index IDs to names.

     

     

     

                begin

                     

                     

                      SELECT

                                 object_id AS objectid,

                                 index_id AS indexid,

                                 partition_number AS partitionnum,

                                 avg_fragmentation_in_percent AS frag

                                 INTO #Temp

                      FROM

                                 sys.dm_db_index_physical_stats (DB_ID(@pDb), NULL, NULL , NULL, 'LIMITED')                        

                      WHERE

                                 avg_fragmentation_in_percent > 10.0

                      AND         index_id > 0;

     

     

                      print @pDb

                      -- Declare the cursor for the list of partitions to be processed.

                      DECLARE partitions CURSOR FOR SELECT * FROM #Temp;

     

                      -- Open the cursor.

                      OPEN partitions;

     

                      -- Loop through the partitions.

                      WHILE (1=1)

                            BEGIN

                                 FETCH NEXT FROM partitions INTO @vobjectid, @vindexid, @vpartitionnum, @vfrag;

                                                                   

                                         

                                 IF (@@FETCH_STATUS < 0)

                                       begin                                   

                                             break

                                       end

     

                                

                                 Set @vSql1 = N' SELECT @vobjectnameOut = QUOTENAME(o.name), @vschemanameOut = QUOTENAME(s.name)

                                 FROM

                                             '+@pDb+'.sys.objects AS o

                                             JOIN '+@pDb+'.sys.schemas as s ON (s.schema_id = o.schema_id)

                                 WHERE

                                             o.object_id = @vobjid'

                                            

                                

                                 Set @vParam = N'@vobjectnameOut varchar(130) OUTPUT, @vschemanameOut varchar(130) OUTPUT, @vobjid int'

                                

                                 execute sp_executesql   @vSql1,

                                                                     @vParam,

                                                                     @vobjid = @vobjectid,                                                             

                                                                     @vobjectnameOut = @vobjectname OUTPUT,

                                                                     @vschemanameOut = @vschemaname OUTPUT;

     

                                                                                                                        

                                                        

                                 Set @vSql2 = N'SELECT   @vindexnameOut = QUOTENAME(name)

                                                     FROM

                                                                     '+@pDb+'.sys.indexes

                                                     WHERE 

                                                                     object_id = @vobjid

                                                     AND       index_id = @vindid '

                                

                                 Set   @vParam2 = N'@vindexnameOut varchar(130) OUTPUT, @vobjid int, @vindid int '

                                

                                 execute sp_executesql   @vSql2,

                                                                     @vParam2,

                                                                     @vobjid = @vobjectid,

                                                                     @vindid = @vindexid,

                                                                     @vindexnameOut = @vindexname OUTPUT;

                                

                                                                    

                                

                                      

                                      

                                 Set @vSql3 = N'SELECT   @vpartitioncountOut = count (*)

                                                         FROM

                                                                     '+@pDb+'.sys.partitions

                                                         WHERE

                                                                     object_id = @vobjid

                                                         AND         index_id = @vidxid '

                                

                                 Set @vParam3 = N'@vpartitioncountOut bigint OUTPUT, @vobjid int, @vidxid int '

                                

                                 execute sp_executesql @vSql3,

                                                                 @vParam3,

                                                                 @vobjid = @vobjectid,

                                                                 @vidxid = @vindexid,

                                                                 @vpartitioncountOut = @vpartitioncount OUTPUT;

                                                                

     

                                 print @vfrag

                                            

                                 -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

                                 IF (@vfrag between 5.0 and 30.0)

                                       begin

                                             SET @vcommand = 'ALTER INDEX ' + @vindexname + ' ON ['+@pDb+'].' + @vschemaname + '.' + @vobjectname + ' REORGANIZE';                

                                       end

                                            

                                 IF (@vfrag >= 30.0)

                                       begin

                                             SET @vcommand = 'ALTER INDEX ' + @vindexname + ' ON ['+@pDb+'].' + @vschemaname + '.' + @vobjectname + ' REBUILD';

                                       end

                                            

                                 IF (@vpartitioncount > 1)

                                       begin                  

                                             SET @vcommand = @vcommand + ' PARTITION=' + CAST(@vpartitionnum AS nvarchar(10));

                                       end

                                            

                                 EXEC (@vcommand);

                                            

                                 PRINT 'Executado: ' + @vcommand;

                                        

                                            

                            end

                           

                end

    END        

     

    CLOSE partitions;

    DEALLOCATE partitions;

     

    DROP TABLE #Temp;

     

    GO

     

     

     

    Um grande abraço



    Anderson

    sexta-feira, 3 de junho de 2011 01:12

Respostas

  • Anderson,

    Faça o seguinte, antes de rodar seu stored procedure execute o comando DBCC UPDATEUSAGE para que o SQL Server atualize as informações sobre as tabelas de sistemas responsáveis em armazenar informações sobre estatísticas.

    Execute a stored procedure e posteriormente execute o comando sp_updatestats.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    sexta-feira, 3 de junho de 2011 18:41
    Moderador

Todas as Respostas

  • sexta-feira, 3 de junho de 2011 11:21
    Moderador
  • Anderson,

    Faça o seguinte, antes de rodar seu stored procedure execute o comando DBCC UPDATEUSAGE para que o SQL Server atualize as informações sobre as tabelas de sistemas responsáveis em armazenar informações sobre estatísticas.

    Execute a stored procedure e posteriormente execute o comando sp_updatestats.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    sexta-feira, 3 de junho de 2011 18:41
    Moderador
  • Fabrizzio e Junior,

    Gostaria de agradecer a voces pelas dicas, segui todos os passos e blz. Ultimamente tenho como responsabilidade 3 ambientes e ganhei mais 2 totalizando 2 Tb de dados abrangendo versão de 2000 Std a 2008 R2. Já estou montando um plano para deixar o ambiente homogenio (2008 R2) e tenho seguido muitas dicas do pessoal do technet, principalmente voces.

    Estou tirando 2 horas por dia para estudar a parte administration e tunnig, pois essa será minha vida de agora em diante, agradeço por mais dicas e documentos.

    Marquei para Agosto minha certificaçao.

     

    Um grande abraço

     

    Anderson

    sábado, 4 de junho de 2011 16:14
  • Anderson,

     

    Sem problemas, e bom, parabens pelas novas responsabilidades, e continue acompanhando o forum pois sempre ajuda, todos nós, e sei que digo isso pelo Junior tambem, sempre aprendemos muito aqui e todos os dias!

     

    E se possivel, para que possa ajudar outros usuarios que venham a ter a mesma duvida, marque como respostas as threads que te ajudaram. =)


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    segunda-feira, 6 de junho de 2011 02:09
    Moderador
  • Anderson,

    Cara isso mesmo, sempre estamos aprendendo e muito na nossa vida, eu mesmo procuro também estudar todos os dias alguma coisa nova sobre o SQL Server, independente do recurso, desde de aqueles básicos até os mais avançados, sofisticados e atuais.

    Obrigado pelo retorno, estamos aqui para ajudar e trocar conhecimentos.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    quarta-feira, 8 de junho de 2011 17:46
    Moderador