none
Reindex - Problema espaço em disco RRS feed

  • Pergunta

  • Pessoal,

    fiz essa mesma pergunta em um tópico já marcado como respondido, por isso estou repetindo aqui:

    "Estou enfrentando o problema de espaço em disco para realizar a manutenção nos índices, arquivo de log fica gigante após o processo, e esse processo é executado 3 vezes por semana.

    As tabelas desses bancos sofrem muitas alterações durante o dia, em algumas delas são inseridos mais de 400.000 registros por dia.

    Segue o script que foi desenvolvido por outra pessoa e é utilizado para realizar o reindex, gostaria de saber como garantir que após o fim do processo o disco do servidor não fique cheio.

     

    1. cursor para pegar os databases ativos;

    2. altera o recovery model para BULK_LOGGED;

    3. cursor para pegar os indices;

    4. abaixo a condição para o REBUILD ou REORGANIZE;

     

    IF (@V_AVG_FRAGMENTATION_IN_PERCENT >= 30)
        EXEC('USE ['+@V_DE_DATABASE+']; DBCC DBREINDEX (['+@V_NM_TABLE+']) WITH NO_INFOMSGS');
    ELSE IF (@V_AVG_FRAGMENTATION_IN_PERCENT >= 5)
        EXEC('USE ['+@V_DE_DATABASE+']; DBCC INDEXDEFRAG (['+@V_DE_DATABASE+'], ['+@V_NM_TABLE+']) WITH NO_INFOMSGS');
    
    5. retorna o recovery model para FULL;

     

    Outras dúvidas:

    - Melhor utilizar DBCC ou ALTER INDEX?

    - Da forma que está o FILL FACTOR fica em 100% por default, certo?

    - Como fazer o reindex off-line?

    - Teria problema em alterar o recovery model para SIMPLE para não registrar essas transações no log?

     

    Grato.

    • Movido Gustavo Maia Aguiar domingo, 18 de dezembro de 2011 07:01 (De:SQL Server - Infra Geral)
    sexta-feira, 16 de dezembro de 2011 17:34

Todas as Respostas

  • Boa Noite,

    O fato do RECOVERY MODEL estar marcado como Bulk Logged irá resultar no maior tamanho possível de backup de log na sua situação (maior inclusive que o RECOVERY MODEL FULL). Quando a operação de REINDEX roda com o RECOVERY MODEL em Bulk Logged, a quantidade de entradas no log é significativamente menor, pois, apenas as extensões alteradas são registradas. Entretanto, para manter a consistência, no momento em que o backup for feito, as extensões irão junto com o backup de log tornando-o gigantesco. Não recomendo essa estratégia. Há mais detalhes em:

    Using Bulk Logged recovery model for Bulk Operations will reduce the size of Transaction log backups – Myths and Truths
    http://blogs.msdn.com/b/sqlserverfaq/archive/2011/01/07/using-bulk-logged-recovery-model-for-bulk-operations-will-reduce-the-size-of-transaction-log-backups-myths-and-truths.aspx

    Se você está tendo problemas de espaço em disco durante a reindexação, eu sugiro aumentar o tamanho do disco ou colocar a execução de backups de log dentro da rotina de reindexação de forma ou gerar um alerta para que quando o log atingir um determinado tamanho um backup de log seja executado.

    Vamos às dúvidas:

    Melhor utilizar DBCC ou ALTER INDEX?
    Em princípio ambos os comandos efetuam a reindexação da tabela, mas o ALTER INDEX é capaz de fazê-lo ONLINE enquanto que o REINDEX não possui essa capacidade. A reindexação ONLINE é possível desde que a tabela não possua BLOBs (TEXT, IMAGE, VARBINARY(MAX), VARCHAR(MAX) e XML) e que você esteja na edição Enteprise (2005 e superiores). Como o DBCC DBREINDEX está em deprecated é possível que ele seja retirado posteriomente então sugiro o ALTER INDEX.
     
    Da forma que está o FILL FACTOR fica em 100% por default, certo?
    A reindexação irá manter o FILL FACTOR no momento de criação do índice. Se você o criou com 100% (DEFAULT), a reindexação irá respeitar esse valor. Se você criou com outro valor, a reindexação irá utilizar o outro valor
     
    Como fazer o reindex off-line?
    Todo reindex é por padrão Offline. É possível reindexar de forma online conforme condições descritas anteriormente
     
    Teria problema em alterar o recovery model para SIMPLE para não registrar essas transações no log?
    O Recovery Model Simple não irá deixar de registrar nenhuma transação. O que acontecerá é que no primeiro CHECKPOINT as transações serão descartadas enquanto que no FULL e no BULK LOGGED apenas um backup de log as expurga do arquivo de log. Não haverá nenhum efeito prático em mudar do FULL para SIMPLE na reindexação e você ainda perderá a capacidade de Restore Point In Time. Não recomendo fazer essa alteração (só tem desvantagens)

    Notei que você está usando o DBREINDEX e o INDEXDEFRAG. Sugiro utilizar o ALTER INDEX com REBUILD e REORGANIZE respectivamente. Também vi que com mais de 5% você já está reindexando. 5% de fragmentação é uma taxa muito baixa, aceitável e muitas vezes não é possível reduzir abaixo disso (é impossível chegar a 0% e algum percentual de fragmentação é aceitável). Eu sugiro aumentar esse patamar para digamos 10%, pois, em 5% você vai estar reindexando toda hora (mesmo que sem necessidade).

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos: http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível

    • Sugerido como Resposta Gustavo Maia Aguiar domingo, 18 de dezembro de 2011 14:27
    • Editado Gustavo Maia Aguiar domingo, 18 de dezembro de 2011 14:28
    • Marcado como Resposta diegobarr quarta-feira, 21 de dezembro de 2011 16:57
    • Não Marcado como Resposta diegobarr sexta-feira, 23 de dezembro de 2011 13:24
    domingo, 18 de dezembro de 2011 06:58
  • Obrigado Gustavo, me ajudou bastante.

     

    Gostaria de fazer mais umas perguntas:

    - como funciona o FILL_FACTOR configurado = 0 ?

    - estava lendo esse link http://technet.microsoft.com/en-us/library/ms189858.aspx e tem um item comentando sobre desabilitar os índices NONCLUSTERED para reduzir o consumo de espaço durante operações de reindex, seria apenas executar um ALTER INDEX ... DISABLE em uma outra sessão antes de executar o ALTER INDEX ... REBUILD?

     

    Fiz uma rotina para fazer o reorganize ou reindex de acordo com a fragmentação do índice, voce poderia dar uma olhada?

    CREATE PROCEDURE [dbo].[SPR_JOB_MTN_REINDEX_V2]
    AS
    BEGIN
    
    	DECLARE @V_DATABASE VARCHAR(255);
    	DECLARE @V_VL_MIN_FRAGMENTATION FLOAT;
    	DECLARE @V_OBJECT_ID INT;
    	DECLARE @V_OBJECT_NAME SYSNAME;
    	DECLARE @V_SCHEMA_NAME SYSNAME;
    	DECLARE @V_INDEX_NAME SYSNAME;
    	DECLARE @V_INDEX_ID INT;
    	DECLARE @V_FRAG_PERCENT FLOAT;
    	DECLARE @V_INDEX_TYPE VARCHAR(50);
    	DECLARE @V_ALLOC_UNIT_TYPE VARCHAR(50);
    	DECLARE @V_AUX INT;
    	DECLARE @V_SQL NVARCHAR(4000);
    	DECLARE @V_PARAM NVARCHAR(4000);
    	DECLARE @V_SQL_DISABLE_INDEX VARCHAR(4000);
    	DECLARE @V_SQL_ALTER_INDEX VARCHAR(4000);
    	DECLARE @V_DE_DEFRAG_TYPE VARCHAR(50);
    
    
    	--VALOR MININO PARA DESFRAGMENTAR
    	SET @V_VL_MIN_FRAGMENTATION = 10.0;
    
    	--LOOP EM TODOS OS DATABASES CONFIGURADOS PARA BKP
    	DECLARE CUR_DATABASE LOCAL CURSOR FAST_FORWARD FOR
    		
    		SELECT 
    			DE_DATABASE
    		FROM DATABASE_TO_BKP
    		WHERE IS_ACTIVE = 1;
    		
    	OPEN CUR_DATABASE;
    	FETCH NEXT FROM CUR_DATABASE INTO @V_DATABASE;
    	
    
    	WHILE ( @@FETCH_STATUS = 0 )
    	BEGIN
    
    		SELECT
    			IDENTITY(INT,1,1) AS "ID_INDEX_STATS",
    			OBJECT_ID, 
    			INDEX_ID, 
    			AVG_FRAGMENTATION_IN_PERCENT AS "FRAG_PERCENT",
    			INDEX_TYPE_DESC AS "INDEX_TYPE",
    			ALLOC_UNIT_TYPE_DESC AS "ALLOC_UNIT_TYPE"
    		INTO #TMP_INDEX_STATS
    		FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(''+@V_DATABASE+''), NULL, NULL, NULL, NULL)
    		WHERE AVG_FRAGMENTATION_IN_PERCENT >= @V_VL_MIN_FRAGMENTATION
    		AND INDEX_ID > 0 
    		AND INDEX_LEVEL = 0
    		ORDER BY
    			OBJECT_ID;
    
    		/** INICIO - LOOP PARA FAZER O REINDEX **/
    		SELECT 
    			@V_AUX = MIN(ID_INDEX_STATS) 
    		FROM #TMP_INDEX_STATS;
    
    		SELECT
    			@V_OBJECT_ID = TMP.OBJECT_ID, 
    			@V_INDEX_ID = TMP.INDEX_ID, 
    			@V_FRAG_PERCENT = TMP.FRAG_PERCENT,
    			@V_INDEX_TYPE = TMP.INDEX_TYPE,
    			@V_ALLOC_UNIT_TYPE = TMP.ALLOC_UNIT_TYPE
    		FROM #TMP_INDEX_STATS TMP
    		WHERE TMP.ID_INDEX_STATS = @V_AUX;
    
    		WHILE ( @@ROWCOUNT <> 0 )
    		BEGIN
    
    			SET @V_SQL = '';
    			SET @V_PARAM = '';
    			SET @V_SQL_ALTER_INDEX = '';
    			SET @V_SQL_DISABLE_INDEX = '';
    			SET @V_DE_DEFRAG_TYPE = '';
    
    			-- RECUPERA AS INFORMAÇÕES DO ÍNDICE (TABELA, SCHEMA E NOME DO INDICE)
    			SET @V_SQL = '	SELECT 
    								@V_OBJECT_NAME = O.[NAME],
    								@V_SCHEMA_NAME = S.[NAME],
    								@V_INDEX_NAME = SI.[NAME]
    							FROM '+ QUOTENAME(@V_DATABASE) +'.SYS.OBJECTS O
    							JOIN '+ QUOTENAME(@V_DATABASE) +'.SYS.SCHEMAS S ON ( S.SCHEMA_ID = O.SCHEMA_ID )
    							JOIN '+ QUOTENAME(@V_DATABASE) +'.SYS.INDEXES SI ON ( O.[OBJECT_ID] = SI.[OBJECT_ID] ) 
    							WHERE O.[OBJECT_ID] = @V_OBJECT_ID
    							AND SI.INDEX_ID = @V_INDEX_ID';
    
    			SET @V_PARAM = N'	@V_OBJECT_ID INT, @V_INDEX_ID INT, 
    								@V_OBJECT_NAME SYSNAME OUTPUT, @V_SCHEMA_NAME SYSNAME OUTPUT, @V_INDEX_NAME SYSNAME OUTPUT';
    
    			EXEC sp_executesql 
    				@V_SQL, 
    				@V_PARAM, 
    				@V_OBJECT_ID = @V_OBJECT_ID, 
    				@V_INDEX_ID = @V_INDEX_ID,
    				@V_OBJECT_NAME = @V_OBJECT_NAME OUTPUT, 
    				@V_SCHEMA_NAME = @V_SCHEMA_NAME OUTPUT,
    				@V_INDEX_NAME = @V_INDEX_NAME OUTPUT;
    
    
    			/** INICIO - MONTAR O COMANDO ALTER INDEX **/
    			
    			-- FAZ REBUIL OU REORGANIZE DE ACORDO COM O PERCENTUAL DE FRAGMENTAÇÃO
    			-- SE FOR INFERIOR A 30 % -> REORGANIZE
    			IF ( @V_FRAG_PERCENT < 30.0 )
    				SET @V_DE_DEFRAG_TYPE = 'REORGANIZE';	
    				
    			--SE FOR SUPERIOR A 30 % -> REBUILD
    			ELSE
    				SET @V_DE_DEFRAG_TYPE = 'REBUILD';
    				
    			
    			SET @V_SQL_ALTER_INDEX = 'ALTER INDEX '+ QUOTENAME(@V_INDEX_NAME);
    			SET @V_SQL_ALTER_INDEX = @V_SQL_ALTER_INDEX +' ON '+ QUOTENAME(@V_DATABASE) +'.'+ QUOTENAME(@V_SCHEMA_NAME) + '.' + QUOTENAME(@V_OBJECT_NAME);
    			SET @V_SQL_ALTER_INDEX = @V_SQL_ALTER_INDEX +' '+@V_DE_DEFRAG_TYPE;
    			
    			-- SE O INDICE FOR NONCLUSTERED DESABILITA O INDICE ANTES DE FAZER O REBUILD
    			-- PARA NÃO OCUPAR ESPAÇO COM O INDICE ANTIGO
    			IF ( @V_DE_DEFRAG_TYPE = 'REBUILD' AND @V_INDEX_TYPE = 'NONCLUSTERED INDEX' )
    			BEGIN
    			
    				SET @V_SQL_DISABLE_INDEX = 'ALTER INDEX '+ QUOTENAME(@V_INDEX_NAME);
    				SET @V_SQL_DISABLE_INDEX = @V_SQL_DISABLE_INDEX +' ON '+ QUOTENAME(@V_DATABASE) +'.'+ QUOTENAME(@V_SCHEMA_NAME) + '.' + QUOTENAME(@V_OBJECT_NAME) 
    				SET @V_SQL_DISABLE_INDEX = @V_SQL_DISABLE_INDEX +' DISABLE;';
    				
    			END;
    
    			/** FIM - MONTAR O COMANDO ALTER INDEX **/
    			
    			/** INICIO - EXECUTAR ALTER INDEX **/
    			--PRINT ( @V_SQL_DISABLE_INDEX );
    			--PRINT ( @V_SQL_ALTER_INDEX );
    			EXEC ( @V_SQL_DISABLE_INDEX );
    			EXEC ( @V_SQL_ALTER_INDEX );
    					
    			/** FIM - EXECUTAR ALTER INDEX **/
    		    
    			--PEGA O PROXIMO INDICE
    			SELECT 
    				@V_AUX = MIN(TMP.ID_INDEX_STATS) 
    			FROM #TMP_INDEX_STATS TMP
    			WHERE ID_INDEX_STATS > @V_AUX;
    
    			SELECT 
    				@V_OBJECT_ID = TMP.OBJECT_ID, 
    				@V_INDEX_ID = TMP.INDEX_ID, 
    				@V_FRAG_PERCENT = TMP.FRAG_PERCENT,
    				@V_INDEX_TYPE = TMP.INDEX_TYPE,
    				@V_ALLOC_UNIT_TYPE = TMP.ALLOC_UNIT_TYPE
    			FROM #TMP_INDEX_STATS TMP
    			WHERE TMP.ID_INDEX_STATS = @V_AUX;
    			
    		/** FIM - LOOP PARA FAZER O REINDEX **/
    		END;
    		
    		DROP TABLE #TMP_INDEX_STATS;
    		
    		FETCH NEXT FROM CUR_DATABASE INTO @V_DATABASE;
    
    	END;
    
    	CLOSE CUR_DATABASE;
    	DEALLOCATE CUR_DATABASE;
    	
    END;
    

    Mais uma vez obrigado.

    quarta-feira, 21 de dezembro de 2011 17:41