none
Recriação de índices RRS feed

  • Pergunta

  • Boa tarde

    Tenho uma procedure onde faz a recriação e reordenação dos indices da base toda.

    SET NOCOUNT ON;
    	DECLARE @objectid int;
    	DECLARE @indexid int;
    	DECLARE @partitioncount bigint;
    	DECLARE @schemaname nvarchar(130); 
    	DECLARE @objectname nvarchar(130); 
    	DECLARE @indexname nvarchar(130); 
    	DECLARE @partitionnum bigint;
    	DECLARE @partitions bigint;
    	DECLARE @frag float;
    	DECLARE @command nvarchar(4000); 
    	DECLARE @nome_base VARCHAR(40)
    	
    	SET @nome_base = 'SGL_HOMOLOGACAO'
    	-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
    	-- and convert object and index IDs to names.
    	SELECT
    		object_id AS objectid,
    		index_id AS indexid,
    		partition_number AS partitionnum,
    		avg_fragmentation_in_percent AS frag
    	INTO #work_to_do
    	FROM sys.dm_db_index_physical_stats (DB_ID('N''' + @nome_base), NULL, NULL , NULL, 'LIMITED')
    	WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
    
    	-- Declare the cursor for the list of partitions to be processed.
    	DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
    
    	-- Open the cursor.
    	OPEN partitions;
    
    	-- Loop through the partitions.
    	WHILE (1=1)
    		BEGIN;
    			FETCH NEXT
    			   FROM partitions
    			   INTO @objectid, @indexid, @partitionnum, @frag;
    			IF @@FETCH_STATUS < 0 BREAK;
    			SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
    			FROM sys.objects AS o
    			JOIN sys.schemas as s ON s.schema_id = o.schema_id
    			WHERE o.object_id = @objectid;
    			SELECT @indexname = QUOTENAME(name)
    			FROM sys.indexes
    			WHERE  object_id = @objectid AND index_id = @indexid;
    			SELECT @partitioncount = count (*)
    			FROM sys.partitions
    			WHERE object_id = @objectid AND index_id = @indexid;
    
    	-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    			IF @frag < 30.0
    				SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
    			IF @frag >= 30.0
    				SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
    			IF @partitioncount > 1
    				SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
    			EXEC (@command);
    			PRINT N'Executed: ' + @command;
    		END;
    
    	-- Close and deallocate the cursor.
    	CLOSE partitions;
    	DEALLOCATE partitions;
    
    	-- Drop the temporary table.
    	DROP TABLE #work_to_do;

    Porém as vezes ocorre o seguinte erro:

    Não é possível criar um índice no objeto 'dbo.PK__GN_CLASSISFICACO__0697FACD' porque o objeto não é uma tabela ou exibição do usuário.

    Se eu for ate a tabela, excluir o indice do PK e criar novamente e rodar a procedure funciona, porém, essa procedure seria para rodar a noite, ou seja, ocorre esse problema e nao sei como melhorar a procedure para atender ou resolver isso.

    Se alguem puder me ajudar!

    Obrigado

    segunda-feira, 27 de janeiro de 2014 16:19

Respostas

  • Josivan,

    Insira na condição da sua consulta "AND o.type = 'U'". Veja a alteração proposta em sua consulta:

    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    JOIN sys.schemas as s ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid AND o.type = 'U';
    
    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE  object_id = @objectid AND index_id = @indexid;
    
    SELECT @partitioncount = count(*)
    FROM sys.partitions
    WHERE object_id = @objectid AND index_id = @indexid;

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Marcado como Resposta Josivan Laskoski segunda-feira, 27 de janeiro de 2014 18:33
    segunda-feira, 27 de janeiro de 2014 16:32
    Moderador

Todas as Respostas

  • Josivan,

    Insira na condição da sua consulta "AND o.type = 'U'". Veja a alteração proposta em sua consulta:

    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    JOIN sys.schemas as s ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid AND o.type = 'U';
    
    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE  object_id = @objectid AND index_id = @indexid;
    
    SELECT @partitioncount = count(*)
    FROM sys.partitions
    WHERE object_id = @objectid AND index_id = @indexid;

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Marcado como Resposta Josivan Laskoski segunda-feira, 27 de janeiro de 2014 18:33
    segunda-feira, 27 de janeiro de 2014 16:32
    Moderador
  • Durval, muito obrigado pela resposta!

    Aparentemente quando estava analisando o result e estava aparecendo os REBUILD e os REORGANIZE mas ai apareceu o seguinte erro:

    Não é possível encontrar o índice 'PK__CO_TIPOSIMPOSTOS__6A50C1DA'
    Obrigado!

    segunda-feira, 27 de janeiro de 2014 18:00
  • Durval, muito obrigado pela resposta!

    Aparentemente quando estava analisando o result e estava aparecendo os REBUILD e os REORGANIZE mas ai apareceu o seguinte erro:

    Não é possível encontrar o índice 'PK__CO_TIPOSIMPOSTOS__6A50C1DA'
    Obrigado!

    Josivan,

    Comente o EXEC do seu script para identificar exatamente em qual tabela de seu script esta PK pertence.

    Com o nome da tabela, execute sp_help e confirme se o schema desta tabela é realmente "dbo".

    Caso contrário você deverá tratar seu script para aceitar outros schemas além do padrão (dbo).

    Não esqueça de marcar todos os posts que ajudaram na solução como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    segunda-feira, 27 de janeiro de 2014 18:31
    Moderador
  • Executei o sp_help para ter 100% do que eu sabia e sim, todos pertencem ao schema dbo

    segunda-feira, 27 de janeiro de 2014 18:44