none
Rebuild de Indice no Sql Server 2014 Standard RRS feed

  • Pergunta

  • Olá pessoal bom dia,

    1-Tenho um índice com 94,57 % de fragmentação

    2-Quando executo o comando abaixo e após verificação o índice ainda com 94,57

    ALTER INDEX SRZ0101 ON SRZ010 REBUILD PARTITION = ALL WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)   

    3-Quando altero o parâmetro ONLINE = OFF para ONLINE = ON é retornado o aviso abaixo:

    >[Error] Script lines: 1-1 --------------------------
     As operações de índice online só podem ser realizadas na edição Enterprise do SQL Server.

    4-Presumo que só conseguirei recriar o índice quando não houver ninguém conectado na base pelo fato de ser a versão standard 2014, isso confirma ? Caso a resposta for negativa, qual o procedimento devo adotar ?

    Obrigado!

    quarta-feira, 20 de janeiro de 2016 12:46

Respostas

  • No geral, é recomendado que não nos preocupemos com índices abaixo de 1000 páginas. Essas são questões bem mais complexas do que valores definidos, pois cada DBA e seus Banco de Dados possuem suas características específicas.

    Outro ponto que eu recomendaria são para os seguintes Tresholds:

    Menos de 10% de fragmentação: Não fazer nada.
    Entre 10% e 30% de fragmentação: Executar um REORGANIZE.
    Mais de 30% de fragmentação: Executar REBUILD.

    Qualquer dúvida, permaneço à disposição.

    Att,

    Marcos Lanzarini
    Administrador de Banco de Dados
    Microsoft Certified Professional

    Se a resposta foi útil, por favor, vote-a como útil. Se isso resolveu seu caso, marque-a como resposta.

    • Marcado como Resposta lcristiano quarta-feira, 20 de janeiro de 2016 16:46
    quarta-feira, 20 de janeiro de 2016 15:30

Todas as Respostas

  • Se é um índice é pequeno, poderá ocorrer isso mesmo.

    O recomendado é fazer REBUILD para índices com, no mínimo, 1000 páginas. Pode verificar isso na coluna "Page_Count", da DMV "SYS.DM_DB_INDEX_PHYSICAL_STATS", onde mostra diversas outras informações sobre índices.

    REBUILD online somente na versão Enterprise do SQL Server.

    Qualquer dúvida, permaneço à disposição.

    Att,

    Marcos Lanzarini
    Administrador de Banco de Dados
    Microsoft Certified Professional

    Se a resposta foi útil, por favor, vote-a como útil. Se isso resolveu seu caso, marque-a como resposta.


    • Editado MarcosLanzarini quarta-feira, 20 de janeiro de 2016 12:59 Adicionar comentário.
    • Sugerido como Resposta FLauffer quarta-feira, 20 de janeiro de 2016 13:14
    quarta-feira, 20 de janeiro de 2016 12:58
  • Se é um índice é pequeno, poderá ocorrer isso mesmo.

    O recomendado é fazer REBUILD para índices com, no mínimo, 1000 páginas. Pode verificar isso na coluna "Page_Count", da DMV "SYS.DM_DB_INDEX_PHYSICAL_STATS", onde mostra diversas outras informações sobre índices.

    REBUILD online somente na versão Enterprise do SQL Server.

    Qualquer dúvida, permaneço à disposição.

    Att,

    Marcos Lanzarini
    Administrador de Banco de Dados
    Microsoft Certified Professional

    Se a resposta foi útil, por favor, vote-a como útil. Se isso resolveu seu caso, marque-a como resposta.


    Icristiano,

    Apenas complementando a resposta do MarcosLanzarini:

    Respondendo a sua pergunta 4:

    Sim. Se você for recriar o índice no modo OFFLINE é recomendado realizar esta operação apenas quando ninguém estiver utilizando a tabela em questão. Você pode optar por realizar a operação REORGANIZE neste índice, que é uma operação ONLINE e que não depende da edição do SQL Server.

    Seguem alguns links para leitura:

    https://msdn.microsoft.com/pt-br/library/ms189858%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396

    http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/


    Felipe Lauffer MCSA: SQL Server | MCP

    quarta-feira, 20 de janeiro de 2016 13:32
  • Marcos, verifiquei o page_count e tem apenas 129. Neste cenário seria melhor o reorganizar o índice?

    Obrigado!

    quarta-feira, 20 de janeiro de 2016 15:10
  • Felipe, eu até executei usando a opção OFFLINE = OFF  porém não surte efeito inclusive aparece a mensagem "Command was executed successfully", mesmo não tendo usuário do sistema usando a tabela.

    De qualquer forma vou verificar fora do expediente. Obrigado!

    quarta-feira, 20 de janeiro de 2016 15:15
  • No geral, é recomendado que não nos preocupemos com índices abaixo de 1000 páginas. Essas são questões bem mais complexas do que valores definidos, pois cada DBA e seus Banco de Dados possuem suas características específicas.

    Outro ponto que eu recomendaria são para os seguintes Tresholds:

    Menos de 10% de fragmentação: Não fazer nada.
    Entre 10% e 30% de fragmentação: Executar um REORGANIZE.
    Mais de 30% de fragmentação: Executar REBUILD.

    Qualquer dúvida, permaneço à disposição.

    Att,

    Marcos Lanzarini
    Administrador de Banco de Dados
    Microsoft Certified Professional

    Se a resposta foi útil, por favor, vote-a como útil. Se isso resolveu seu caso, marque-a como resposta.

    • Marcado como Resposta lcristiano quarta-feira, 20 de janeiro de 2016 16:46
    quarta-feira, 20 de janeiro de 2016 15:30
  • Marcos, desconsidere o questionamento acima. Obrigado!
    quarta-feira, 20 de janeiro de 2016 15:37
  • Boa tarde lcristiano,

    Caso a tenha encontrado a solução para a sua questão inicial da thread, por gentileza, marque a(as) postagens que o ajudaram a encontrar a resposta. Clique em "Marcar como Resposta".

    Atenciosamente


    Marcos Roberto de Souza Junior

    Esse conteúdo e fornecido sem garantias de qualquer tipo, seja expressa ou implícita

    MSDN Community Support

    Por favor, lembre-se de Marcar como Resposta as respostas que resolveram o seu problema. Essa e uma maneira comum de reconhecer aqueles que o ajudaram e fazer com que seja mais fácil para os outros visitantes encontrarem a resolução mais tarde.



    • Marcado como Resposta lcristiano quarta-feira, 20 de janeiro de 2016 16:46
    • Não Marcado como Resposta lcristiano quarta-feira, 20 de janeiro de 2016 16:46
    • Editado Marcos SJ quarta-feira, 20 de janeiro de 2016 17:00
    quarta-feira, 20 de janeiro de 2016 16:05
  • Obrigado pelas informações Marcos.

    Eu recriei os índices de 12 tabelas de um determinado módulo do sistema em base teste. Notei que os primeiros acessos ficam lentos pois acredito que o banco deve esta atualizando as estatísticas. Porém mesmo após 30 minutos de utilização não notei melhoria na performance, mesmo executando alguns relatórios básicos e reprocessando algumas rotinas do sistema o tempo parece ser o mesmo ou as vezes ou pouco maior.

    Um detalhe importante é que as tabelas citadas possuem acima de 3 milhões de registros e a fragmentação estava acima de 60% e page_count acima de mil.

    Os parâmetros que usei são os padrões abaixo. O que me diz?

    (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)   


    quarta-feira, 20 de janeiro de 2016 16:19
  • lcristiano,

    O problema pode estar atrelado a diversos outros fatores, como por exemplo Estatísticas desatualizadas, dentre diversos outros fatores. Não necessariamente o problema será índices. Você pode tentar (sugiro não efetuar em produção em horário comercial) executar UPDATE STATISTICS.

    Caso o problema persista, sugiro efetuar uma análise utilizando Execution Plans, rodar um DTA e Profiler com esses relatórios, para identificar quais relatórios estão sobrecarregando IO, CPU, Duration, e verificar quais etapas desse relatório estão impactando mais, e as recomendações de melhoria.

    quarta-feira, 20 de janeiro de 2016 16:45
  • lcristiano,

    Gostaria de dar minha contribuição:

    O Mecanismo de Banco de Dados do SQL Server mantém os índices automaticamente sempre que são realizadas operações de entrada, atualização ou exclusão nos dados subjacentes. No decorrer do tempo, essas modificações podem fazer com que as informações do índice sejam dispersadas pelo banco de dados (fragmentadas). A fragmentação ocorre quando os índices têm páginas nas quais a ordem lógica, com base no valor de chave, não corresponde à ordem física do arquivo de dados. Índices com fragmentação pesada podem degradar o desempenho da consulta e causar lentidão de resposta do aplicativo.

    Identificando a fragmentação dos índices

    A primeira etapa para optar pelo método de fragmentação a ser usado é analisar o índice para determinar o grau de fragmentação. Usando a função de sistema sys.dm_db_index_physical_stats, você pode detectar a fragmentação em um índice específico, em todos os índices de uma tabela ou exibição indexada, em todos os índices de um banco de dados ou em todos os índices de todos os bancos de dados. Para índices particionados, sys.dm_db_index_physical_stats também fornece informações de fragmentação por partição.

    Veja este código abaixo, ele vai lhe ajudar a identificar índices fragmentados:

    SELECT
        OBJECT_NAME(B.object_id) AS TableName,
        B.name AS IndexName,
        A.index_type_desc AS IndexType,
        A.avg_fragmentation_in_percent
    FROM
        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')	A
        INNER JOIN sys.indexes							B	WITH(NOLOCK) ON B.object_id = A.object_id AND B.index_id = A.index_id
    WHERE
        A.avg_fragmentation_in_percent > 30
        AND OBJECT_NAME(B.object_id) NOT LIKE '[_]%'
        AND A.index_type_desc != 'HEAP'
    ORDER BY
        A.avg_fragmentation_in_percent DESC
    


    Este outro exemplo, vai lhe ajudar no processo de desfragmentação:

    Desfragmentando um índice fragmentado (REORGANIZE)
    ALTER INDEX SK01 ON dbo.Logins REORGANIZE
    
    Desfragmentando todos os índices da tabela (REORGANIZE)
    ALTER INDEX ALL ON dbo.Logins REORGANIZE
    
    Desfragmentando todos os índices da tabela (REBUILD)
    ALTER INDEX ALL ON dbo.Logins REBUILD
    
    Desfragmentando todos os índices da tabela (REBUILD – COMPLETO)
    ALTER INDEX ALL ON dbo.Logins 
    REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON)
    

    Este outro exemplo mostra realmente quais índices estão sendo utilizados:

    SELECT
        ObjectName = OBJECT_SCHEMA_NAME(idx.object_id) + '.' + OBJECT_NAME(idx.object_id),
        IndexName = idx.name,
        IndexType = CASE WHEN is_unique = 1 THEN 'UNIQUE ' ELSE '' END + idx.type_desc,
        User_Seeks = us.user_seeks,
        User_Scans = us.user_scans,
        User_Lookups = us.user_lookups,
        User_Updates = us.user_updates
    FROM
        sys.indexes idx
        LEFT JOIN sys.dm_db_index_usage_stats us ON idx.object_id = us.object_id AND idx.index_id = us.index_id AND us.database_id = DB_ID()
    WHERE
        OBJECT_SCHEMA_NAME(idx.object_id) != 'sys'
    ORDER BY
        us.user_seeks + us.user_scans + us.user_lookups DESC
    


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 21 de janeiro de 2016 14:36