segunda-feira, 15 de dezembro de 2014

RE-Escrevendo SpRebuildAllIndex para o SQL AZURE

Minha procedure habitual de IndexRebuild não funcionava no SQL Azure, por duas razões:


  • Ausência de sys.partitions (substituída por sys.dm_db_partition_stats)
  • O SQL Azure não tomar SELECT INTO (eu estava usando isso na minha habitual Reconstruir Proc)


Contornando estas limitações, criei uma nova proc:

CREATE PROC [dbo].[SpRebuildAllIndex] 
AS 
  BEGIN 
      CREATE TABLE #tmpfrag 
        ( 
           tbname VARCHAR(200) 
        ) 

      INSERT INTO #tmpfrag 
      SELECT TOP 20 Schema_name(o.schema_id) + '.' 
                    + Object_name(ps.object_id) AS TableName 
      FROM   sys.dm_db_partition_stats ps 
             INNER JOIN sys.indexes i 
                     ON ps.object_id = i.object_id 
                        AND ps.index_id = i.index_id 
             INNER JOIN sys.objects o 
                     ON ( i.object_id = o.object_id ) 
             CROSS apply sys.Dm_db_index_physical_stats(Db_id(), ps.object_id, 
                         ps.index_id, 
                         NULL, 
                         'LIMITED') ips 
      GROUP  BY Schema_name(o.schema_id) + '.' 
                + Object_name(ps.object_id) 
      ORDER  BY Avg(ips.avg_fragmentation_in_percent) DESC 

      DECLARE @TableName VARCHAR(255) 
      DECLARE tablecursor CURSOR FOR 
        (SELECT * 
         FROM   #tmpfrag) 

      OPEN tablecursor 

      FETCH next FROM tablecursor INTO @TableName 

      WHILE @@FETCH_STATUS = 0 
        BEGIN 
            PRINT( 'Rebuilding Indexes on ' + @TableName ) 

            BEGIN try 
                EXEC('ALTER INDEX ALL ON ' + @TableName + 
                ' REBUILD with (ONLINE=ON)') 
            --PRINT 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)' 
            END try 

            BEGIN catch 
                PRINT( 'Rebuild with Online=On ' + @TableName 
                       + ' UnSuccessful, removing ONLINE' ) 

                EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD') 
            END catch 

            FETCH next FROM tablecursor INTO @TableName 
        END 

      CLOSE tablecursor 

      DEALLOCATE tablecursor 

      DROP TABLE #tmpfrag 

      INSERT INTO logrebuild 
                  (desclogrebuild) 
      VALUES      ('Sucesso') 

      SELECT Cast(1 AS INT) AS [Resultado] 
  END 

go 

Observem que estou agrupando as tabelas e considerando a fragmentação total/média de todos os índices, pois neste momento não vou fazer o REBUILD índice por índice, mas de todos os índices da tabela.

Check out my new book on MariaDB http://www.amazon.com/dp/B00MQC06HC

Nenhum comentário:

Postar um comentário

Leave your comment here!