none
Lista do indexes com fragmentação acima de 50% de um banco de dados RRS feed

  • Pergunta

  • Prezados,

               Tem como gerar uma lista de todos os indexes de um banco, informando o seu percentual de fragmentação de preferencia a lista sendo da seguinte forma ( NOME TABELA | NOME INDEXES | PERCENTUAL FRAGMENTAÇÃO ) ?


    Grato


    Bruno Avanso

    terça-feira, 4 de junho de 2013 23:26

Respostas

  • Tente rodar este script na base:

    SELECT ps.database_id, ps.OBJECT_ID,
    ps.index_id, b.name,
    ps.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
    AND ps.index_id = b.index_id
    WHERE ps.database_id = DB_ID()
    ORDER BY ps.OBJECT_ID
    GO
    Fonte: http://blog.sqlauthority.com/2008/03/27/sql-server-2005-find-index-fragmentation-details-slow-index-performance/


    André CR / Helped? If the answer is yes mark! If the answer is no, wait a little bit because i'll back! Visit my blog! sqlmagu.blogspot.com.br

    • Marcado como Resposta Bruno Avanso domingo, 28 de julho de 2013 14:46
    quarta-feira, 5 de junho de 2013 02:30
  • Bruno,

    Veja este exemplo:

    ALTER PROCEDURE VerificarIndices80CentsMaiorQuetabela
     
    AS
     
    --DECLARE´S
     
    DECLARE @Tables TABLE(Nome VARCHAR(100), Stat VARCHAR(2))
     
    DECLARE @Tabela VARCHAR(100)
     
    DECLARE @Final TABLE(Name VARCHAR(100), Rows INT, Reserved VARCHAR(100), Data VARCHAR(100), Index_Size VARCHAR(100), Unused VARCHAR(100))
     
    --CARGA DE DADOS
     
    INSERT INTO @Tables
     
    SELECT NAME, NULL FROM SYS.TABLES
     
    --LOOPING
     
    WHILE(EXISTS(SELECT TOP 1 1 FROM @Tables WHERE Stat IS NULL))
     
    BEGIN
     
    SET @Tabela = (SELECT TOP 1 Nome FROM @Tables WHERE Stat IS NULL)
     
    INSERT INTO @Final EXEC sp_spaceused @Tabela
     
    UPDATE @Tables SET Stat = 'ok' WHERE Nome LIKE @Tabela
     
    END
     
    --SELECT Name, Data FROM @Final
     
    SELECT a.TableName, a.IndexName, a.IndexSizeKB, b.Data
     
    FROM
     
    (SELECT
     
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
     
    i.name AS IndexName,
     
    i.index_id AS IndexID,
     
    8 * SUM(a.used_pages) AS 'IndexsizeKB'
     
    FROM sys.indexes AS i
     
    JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
     
    JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
     
    WHERE i.name IS NOT NULL
     
    AND OBJECT_NAME(i.OBJECT_ID) NOT LIKE '%sys%'
     
    GROUP BY i.OBJECT_ID,i.index_id,i.name) a
     
    INNER JOIN @Final b ON (a.TableName = b.Name)
     
    WHERE a.IndexSizeKB > cast(RTRIM(LTRIM(REPLACE(b.Data,'KB',''))) as Int)*0.5
    


    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]

    • Marcado como Resposta Bruno Avanso domingo, 28 de julho de 2013 14:46
    quarta-feira, 5 de junho de 2013 14:22
    Moderador

Todas as Respostas

  • Tente rodar este script na base:

    SELECT ps.database_id, ps.OBJECT_ID,
    ps.index_id, b.name,
    ps.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
    AND ps.index_id = b.index_id
    WHERE ps.database_id = DB_ID()
    ORDER BY ps.OBJECT_ID
    GO
    Fonte: http://blog.sqlauthority.com/2008/03/27/sql-server-2005-find-index-fragmentation-details-slow-index-performance/


    André CR / Helped? If the answer is yes mark! If the answer is no, wait a little bit because i'll back! Visit my blog! sqlmagu.blogspot.com.br

    • Marcado como Resposta Bruno Avanso domingo, 28 de julho de 2013 14:46
    quarta-feira, 5 de junho de 2013 02:30
  • Bruno,

    Veja este exemplo:

    ALTER PROCEDURE VerificarIndices80CentsMaiorQuetabela
     
    AS
     
    --DECLARE´S
     
    DECLARE @Tables TABLE(Nome VARCHAR(100), Stat VARCHAR(2))
     
    DECLARE @Tabela VARCHAR(100)
     
    DECLARE @Final TABLE(Name VARCHAR(100), Rows INT, Reserved VARCHAR(100), Data VARCHAR(100), Index_Size VARCHAR(100), Unused VARCHAR(100))
     
    --CARGA DE DADOS
     
    INSERT INTO @Tables
     
    SELECT NAME, NULL FROM SYS.TABLES
     
    --LOOPING
     
    WHILE(EXISTS(SELECT TOP 1 1 FROM @Tables WHERE Stat IS NULL))
     
    BEGIN
     
    SET @Tabela = (SELECT TOP 1 Nome FROM @Tables WHERE Stat IS NULL)
     
    INSERT INTO @Final EXEC sp_spaceused @Tabela
     
    UPDATE @Tables SET Stat = 'ok' WHERE Nome LIKE @Tabela
     
    END
     
    --SELECT Name, Data FROM @Final
     
    SELECT a.TableName, a.IndexName, a.IndexSizeKB, b.Data
     
    FROM
     
    (SELECT
     
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
     
    i.name AS IndexName,
     
    i.index_id AS IndexID,
     
    8 * SUM(a.used_pages) AS 'IndexsizeKB'
     
    FROM sys.indexes AS i
     
    JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
     
    JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
     
    WHERE i.name IS NOT NULL
     
    AND OBJECT_NAME(i.OBJECT_ID) NOT LIKE '%sys%'
     
    GROUP BY i.OBJECT_ID,i.index_id,i.name) a
     
    INNER JOIN @Final b ON (a.TableName = b.Name)
     
    WHERE a.IndexSizeKB > cast(RTRIM(LTRIM(REPLACE(b.Data,'KB',''))) as Int)*0.5
    


    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]

    • Marcado como Resposta Bruno Avanso domingo, 28 de julho de 2013 14:46
    quarta-feira, 5 de junho de 2013 14:22
    Moderador