none
Dropando indices não utilizados RRS feed

  • Pergunta

  • Pessoal, usando o comando abaixo identifiquei diversos índices não utilizados pelo meu banco de dados, porém percebi que muitos são adventos de PK das tabelas. Entendo que não é coreto deixa PKs sem índices, no qual por padrão cria índices clusterizados.

    Quais seriam as dicas de como proceder nesta situação.

    USE Base
    
    ;WITH IndicesNaoUtilizados As (
    SELECT
        DB_NAME(database_id) As Banco, OBJECT_NAME(I.object_id) As Tabela, I.Name As Indice,
        U.User_Seeks As Pesquisas, U.User_Scans As Varreduras, U.User_Lookups As LookUps,
        U.Last_User_Seek As UltimaPesquisa, U.Last_User_Scan As UltimaVarredura,
        U.Last_User_LookUp As UltimoLookUp, U.Last_User_Update As UltimaAtualizacao
    FROM
        sys.indexes As I
        LEFT OUTER JOIN sys.dm_db_index_usage_stats As U
        ON I.object_id = U.object_id AND I.index_id = U.index_id
    WHERE database_id = DB_ID())
    
    SELECT
        Banco, Tabela, Indice, Pesquisas, Varreduras, LookUps,
        UltimaPesquisa, UltimaVarredura, UltimoLookUp
    FROM IndicesNaoUtilizados
    WHERE
        (Pesquisas + Varreduras + LookUps) = 0 
        order by Tabela
    Agradeço desde já!

    quinta-feira, 24 de abril de 2014 16:03

Todas as Respostas

  • Deleted
    quinta-feira, 24 de abril de 2014 20:34
  • Jerfeson,

    Concordo com o Jose Diz, mas veja se este exemplo poderá te ajudar:

    -- List unused indexes
    SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
            i.name
    FROM    sys.indexes AS i
            INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
    WHERE   i.index_id NOT IN ( SELECT  s.index_id
                                FROM    sys.dm_db_index_usage_stats AS s
                                WHERE   s.[object_id] = i.[object_id]
                                        AND i.index_id = s.index_id
                                        AND database_id = DB_ID() )
            AND o.[type] = 'U'
    ORDER BY OBJECT_NAME(i.[object_id]) ASC ;


    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]

    segunda-feira, 28 de abril de 2014 15:13
    Moderador
  • Galvão, esse script trás muitos índices originados de chaves primárias de tabelas que não pode ser removidos, mesmo não sendo usados.

    Como muito dessas tabelas tem pouquíssimos dados e outras nem dados tem, a decisão seria de aumentar fill factor desses índices pra mais próximo de 100%? Atualmente estão com 80%.
    domingo, 25 de maio de 2014 12:58
  • Jefferson, veja bem... 

    o FillFactor tem que ser observado bemmmmm de perto para cada caso. O FillFactor é o fator de preenchimento, se a tabela a qual este índice esta vinculado tiver um grande volume de atualização, chegando próximo a 100% você pode aumentar a fragmentação do seu índice. Caso ela tenha um pequeno volume de atualização, chegando próximo a 100% você terá uma economia de recursos de IO.

    Quanto a índices não utilizados, principalmente no tocante a Chaves Primárias (PK) e Restrições de Unicidade (Unique Constraints UQ), há de se avaliar outros aspectos. caso você tenha uma tabela de tipos (Uma tabela de sexos onde vc tem coluna Id, Sigla e Descricao) com uma chave primária associada ao ID, mas não tenha essa tabela como chave estrangeira (FK) em outra tabela a sua PK servirá somente para garantir a não duplicidade de ID, e se você não insere registros nessa tabela com frequência, a utilização do índice será baixa, senão nula.

    Em resumo, muito cuidado com o que se chama de "Índices não utilizados", sem deixar de considerar do comentário do José.Diz quanto a estatísticas.


    quarta-feira, 28 de maio de 2014 14:38
  • Jefferson,

    Acredito que neste caso o melhor é você identificar os indices que estão apresentando estatísticas desatualizadas, fazer posteriormente o processo de atualização destas estatísticas e depois identificar quais indices não estão realmente em uso.

    SELECT name AS stats_name, 
        STATS_DATE(object_id, stats_id) AS statistics_update_date
    FROM sys.stats 
    order by statistics_update_date Desc

    Através da Função State_Date você vai conseguir identificar os indices com estatísticas desatualizadas, através da system table sys.stats.

    Este outro exemplo, também pode te ajudar:

    --Create a table for the outdated statistics
    CREATE TABLE Outdated_statistics
    ([Table name] sysname,
    [Index name] sysname,
    [Last updated] datetime NULL,
    [Rows modified] int NULL)
    GO
    
    --Get the list of outdated statistics
    INSERT INTO Outdated_statistics
    SELECT OBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr FROM sys.sysindexes
    WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE()) 
    AND rowmodctr>0 
    AND id IN (SELECT object_id FROM sys.tables)
    GO
    
    Select * from Outdated_statistics
    
    --Set the thresholds when to consider the statistics outdated
    DECLARE @hours int
    DECLARE @modified_rows int
    DECLARE @update_statement nvarchar(300);
    
    SET @hours=24
    SET @modified_rows=10
    
    --Update all the outdated statistics
    DECLARE statistics_cursor CURSOR FOR
    SELECT 'UPDATE STATISTICS '+OBJECT_NAME(id)+' '+name
    FROM sys.sysindexes
    WHERE STATS_DATE(id, indid)<=DATEADD(HOUR,-@hours,GETDATE()) 
    AND rowmodctr>=@modified_rows 
    AND id IN (SELECT object_id FROM sys.tables)
     
    OPEN statistics_cursor;
    FETCH NEXT FROM statistics_cursor INTO @update_statement;
     
     WHILE (@@FETCH_STATUS <> -1)
     BEGIN
      EXECUTE (@update_statement);
      PRINT @update_statement;
     
     FETCH NEXT FROM statistics_cursor INTO @update_statement;
     END;
     
     PRINT 'The outdated statistics have been updated.';
    CLOSE statistics_cursor;
    DEALLOCATE statistics_cursor;
    GO
    

    Utilize também a System Stored Procedures SP_UpdateStats, para atualizar as estatísticas internas do SQL Server.

    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]

    • Sugerido como Resposta Durval Ramos sexta-feira, 20 de junho de 2014 14:35
    quarta-feira, 28 de maio de 2014 17:12
    Moderador
  • Deleted
    • Sugerido como Resposta Durval Ramos sexta-feira, 20 de junho de 2014 14:35
    segunda-feira, 2 de junho de 2014 20:49