Início / dba / Perguntas / 5365
Accepted
Justin Dearing
Asked: 2011-09-03 12:57:15 +0800 CST

Por que meu banco de dados ainda está fragmentado depois que reconstruí e reindexei tudo?

  • 772

Eu tenho um banco de dados que tentei desfragmentar todas as tabelas de uma só vez executando este T-SQL:

SELECT 
        'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) +
        'ALTER INDEX all ON ' + name + ' REBUILD;'
    FROM sys.tables

E, em seguida, copiando e colando a saída em uma nova janela de consulta e executando isso. Não obtive erros, mas ainda tenho fragmentação. Eu tentei executar os dois comandos separadamente também e ainda tenho fragmentação. Nota: Fui informado de que REORGANIZEé desnecessário por Aaron e estou ciente de que poderia usar sql dinâmico para automatizar isso.

Eu executei isso para determinar que ainda tenho fragmentação:

SELECT * FROM 
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) 
WHERE avg_fragmentation_in_percent > 0

E eu consegui:

database_id object_id ID_índice número_partição index_type_desc alloc_unit_type_desc índice_profundidade nível_índice avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages contagem de páginas avg_page_space_used_in_percent Contagem de registros ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forward_record_count compact_page_count
85 171147655 1 1 ÍNDICE AGRUPADO IN_ROW_DATA 2 0 36.3636363636364 5 2.2 11 NULO NULO NULO NULO NULO NULO NULO NULO NULO
85 421576540 1 1 ÍNDICE AGRUPADO IN_ROW_DATA 2 0 75 7 1.14285714285714 8 NULO NULO NULO NULO NULO NULO NULO NULO NULO
85 965578478 1 1 ÍNDICE AGRUPADO IN_ROW_DATA 2 0 14.7058823529412 6 5.66666666666667 34 NULO NULO NULO NULO NULO NULO NULO NULO NULO
85 1061578820 1 1 ÍNDICE AGRUPADO IN_ROW_DATA 2 0 40 4 1,25 5 NULO NULO NULO NULO NULO NULO NULO NULO NULO
85 1109578991 1 1 ÍNDICE AGRUPADO IN_ROW_DATA 2 0 30.7692307692308 5 2.6 13 NULO NULO NULO NULO NULO NULO NULO NULO NULO
85 1205579333 2 1 ÍNDICE NÃO ASSOCIADO IN_ROW_DATA 2 0 50 5 1,6 8 NULO NULO NULO NULO NULO NULO NULO NULO NULO
85 1493580359 1 1 ÍNDICE AGRUPADO IN_ROW_DATA 2 0 50 6 1.66666666666667 10 NULO NULO NULO NULO NULO NULO NULO NULO NULO

Eu sei que estou perdendo algo bem básico, mas não sei o quê.

sql-server-2008 index

4 respostas

  1. Best Answer

    As mesas são minúsculas. As contagens de páginas em suas tabelas são:

    11, 8, 6, 5, 13, 8, 10

    Eles ocupam 480kb no total. Não há literalmente nada para desfragmentar.

    Edit: Isso merece um pouco mais de explicação.

    Uma nova tabela ou índice geralmente é alocado nas primeiras 8 páginas de uma extensão mista, em vez de uniforme. Portanto, é possível que cada uma das primeiras 8 páginas seja alocada de diferentes extensões mistas. Uma tabela ou índice que consome 8 páginas pode, portanto, ter 8 fragmentos, 1 em cada uma das 8 extensões mistas diferentes.

    Os scripts de desfragmentação mais usados ​​(alguns exemplos vinculados abaixo) tendem a excluir pequenas tabelas por causa disso. IIRC, <500 páginas está em um ou ambos. Nesses tamanhos, há muito pouco benefício na desfragmentação e os números de fragmentação são potencialmente distorcidos pelas alocações de extensão mista.

    • 39

  2. Citação de " Práticas recomendadas de desfragmentação de índice do Microsoft SQL Server 2000 ":

    "A fragmentação afeta a E/S do disco. Portanto, concentre-se nos índices maiores porque suas páginas são menos propensas a serem armazenadas em cache pelo SQL Server. Use a contagem de páginas relatada pelo DBCC SHOWCONTIG para ter uma ideia do tamanho dos índices (cada página é 8 KB de tamanho). Geralmente, você não deve se preocupar com níveis de fragmentação de índices com menos de 1.000 páginas. Nos testes, índices com mais de 10.000 páginas obtiveram ganhos de desempenho, com os maiores ganhos em índices com significativamente mais páginas (maior de 50.000 páginas) ."

    Então, isso meio que responde sua pergunta e respalda as respostas de Mark e Aaron.

    Você pode encontrar boas informações sobre fragmentação de índice nos seguintes artigos de Brent Ozar:

    Além disso, um oceano de ótimas informações sobre índices em geral (também sobre questões de fragmentação) pode ser encontrado no blog de Kimberly Tripp .

    • 21

  3. Isso não é para responder à sua pergunta, mas nunca caberá em um comentário. Você pode construir este script dinamicamente sem ter que copiar e colar a saída em outra janela. Levando em conta que não há absolutamente nenhuma razão para REORGANIZEe então REBUILD:

    DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER INDEX all ON ' + name + ' REBUILD;
    ' FROM sys.tables;

PRINT @sql; -- to see the first 8,000 characters and make sure it checks out
-- EXEC sp_executesql @sql;
    • 13

  4. Eu acho que para reconstruir todos os índices você pode executar uma solução mais elegante, tente com

     EXEC sp_MSforeachtable ' ALTER INDEX ALL ON ? REBUILD'
    • -2

relate perguntas