Início / dba / Perguntas / 177006
Accepted
Danielle Paquette-Harvey
Asked: 2017-06-23 06:00:15 +0800 CST

Existe uma maneira de obter a fragmentação do índice sem usar dm_db_index_physical_stats? [duplicado]

  • 772

Estou usando a edição padrão do SQL Server 2016 SP1.

A visualização dm_db_index_physical_stats é muito lenta em bancos de dados grandes. Mesmo se eu especificar a opção 'LIMITED'.

Eu uso a view para obter a fragmentação do índice, para determinar se devo fazer um REBUILD ou um REORG na minha manutenção. Mas à medida que os bancos de dados crescem, a chamada para o DMV dm_db_index_physical_stats leva cada vez mais tempo. Em alguns bancos de dados, que podem ter até 1 TB, pode levar uma hora para consultar o DMV. (Não tenho tempo de inatividade, sempre há clientes conectados ao banco de dados trabalhando.)

Então eu queria saber se existe uma maneira de saber a fragmentação do índice sem consultar o DMV.

sql-server index

1 respostas

  1. Best Answer

    Vou apenas resumir as ótimas sugestões que foram feitas na seção de comentários:
    A resposta curta é Não, você tem que usar o DMV.

    Uma possível alternativa para usar o DMV em todo o banco seria fazer por tabela, mas preciso fazer para o banco inteiro, e já divido a manutenção do índice em vários dias para que caiba o máximo possível na manutenção janela.

    Outra alternativa seria restaurar o banco de dados em outro servidor e reunir a fragmentação do índice ali.

    Eu também posso tentar ver se há alguns índices não utilizados e ver se consigo remover alguns. Achei essa consulta muito útil, se puder servir para outra pessoa. Se você vir índices sem leituras e somente gravações, significa que o índice é usado apenas quando você insere linhas na tabela, mas nunca nas consultas SELECT. Você pode então analisar e decidir se é útil e deve ser mantido.

    SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups]
            AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        ddios.[leaf_insert_count] ,
        ddios.[leaf_delete_count] ,
        ddios.[leaf_update_count] ,
        ddios.[nonleaf_insert_count] ,
        ddios.[nonleaf_delete_count] ,
        ddios.[nonleaf_update_count]
FROM    sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id] AND i.[index_id] = ddius.[index_id]
INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id] AND SP.[index_id] = ddius.[index_id]
INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
INNER JOIN sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) AS ddios
  ON ddius.[index_id] = ddios.[index_id]
  AND ddius.[object_id] = ddios.[object_id]
  AND SP.[partition_number] = ddios.[partition_number]
  AND ddius.[database_id] = ddios.[database_id]
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
      AND ddius.[index_id] > 0
      AND ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name ]
    • 2

relate perguntas