Fragmentação do arquivo físico do banco de dados SQL

19

Eu sei que há realmente três tipos de fragmentação com os quais eu preciso me preocupar como DBA:

  1. Índice Fragmentação nos arquivos de dados SQL, incluindo fragmentação de índice (tabela) em cluster. Identifique isso usando DBCC SHOWCONTIG (no SQL 2000) ou sys.dm_db_index_static_stats (em 2005 +).

  2. Fragmentação de VLF dentro de arquivos de log do SQL. Execute o DBCC LOGINFO para ver quantos VLFs estão em cada um dos seus arquivos de log SQL.

  3. Fragmentação de arquivos físicos dos arquivos do banco de dados no disco rígido. Diagnostique isso usando o utilitário "Disk Defragmenter" no Windows. (inspirado por este excelente blog postar )

Muita atenção é dada à fragmentação do índice (veja esta excelente resposta Serverfault de Paul Randall), então esse não é o foco da minha pergunta.

Eu sei que posso prevenir fragmentação física (e fragmentação de VLF) quando o banco de dados é originalmente criado planejando um tamanho razoável de arquivo e tamanho de log, porque essa fragmentação ocorre mais frequentemente em freqüentes crescimentos e encolhimentos , mas tenho algumas perguntas sobre como corrigir a fragmentação física quando ela é identificada:

  • Em primeiro lugar, a fragmentação física é relevante até mesmo em uma SAN corporativa? Posso / devo usar o Desfragmentador do Windows em uma unidade SAN ou a equipe da SAN deve estar usando os utilitários de desfragmentação interna? A análise de fragmentação que recebo da ferramenta do Windows é precisa quando executada em uma unidade SAN?

  • Qual é o tamanho da fragmentação física no desempenho do SQL? (Vamos supor uma matriz de unidades internas, aguardando o resultado da questão anterior.) Trata-se de uma transação MAIOR que a fragmentação do índice interno? Ou é realmente o mesmo tipo de problema (a unidade ter que fazer leituras aleatórias em vez de leituras sequenciais)?

  • A desfragmentação (ou reconstrução) indexa uma perda de tempo se a unidade estiver fisicamente fragmentada? Preciso consertar um antes de abordar o outro?

  • Qual é a melhor maneira de corrigir a fragmentação de arquivos físicos em uma caixa SQL de produção? Eu sei que posso desativar os serviços SQL e executar o Windows Defrag, mas também ouvi falar de uma técnica em que você faz um backup completo, descarta o banco de dados e restaura do backup para uma unidade vazia. Esta última técnica é recomendada? A restauração de um backup como este também cria índices do zero, eliminando a fragmentação do índice interno? Ou simplesmente retorna a ordem das páginas para o mesmo de quando o backup foi feito? (Estamos usando os backups do Quest Lightspeed com compactação, se isso for importante.)

UPDATE : Boas respostas até agora sobre a possibilidade de desfragmentar unidades SAN (NO) e se a desfragmentação de índice ainda vale a pena em unidades fisicamente fragmentadas (YES).

Alguém mais se preocupa em avaliar os melhores métodos para realmente fazer a desfragmentação? Ou uma estimativa do tempo que você esperaria para desfragmentar uma grande unidade fragmentada, digamos 500 GB ou mais? Relevante, obviamente, porque essa é a hora em que meu servidor SQL ficará inativo!

Além disso, se alguém tiver alguma informação sobre as melhorias de desempenho do SQL feitas ao corrigir a fragmentação física, isso também seria ótimo. Postagem do blog de Mike fala sobre descobrir o problema, mas não é específico sobre o tipo de melhoria que ele fez.

    
por BradC 24.06.2009 / 16:29

6 respostas

9

Acho que este artigo oferece uma excelente visão geral da desfragmentação de unidades SAN

link

Os pontos básicos são que a desfragmentação não é recomendada no armazenamento SAN porque é difícil correlacionar a localização física dos blocos no disco quando o local foi virtualizado pela SAN ao apresentar o LUN.

Se você estava usando mapeamentos de dispositivos RAW ou tem acesso direto a um conjunto de RAID que é o LUN com o qual está trabalhando, a degragmentação pode ter um efeito positivo, mas se você receber um LUN "virtual" de um conjunto RAID-5 compartilhado, não.

    
por 24.06.2009 / 16:38
7

Várias partes para esta pergunta e resposta:

A fragmentação do arquivo físico não é realmente relevante para o armazenamento da SAN corporativa, como Kevin já destacou - portanto, nada para adicionar lá. Ele realmente se resume ao subsistema de E / S e a probabilidade de você conseguir fazer com que as unidades passem de E / Ss mais aleatórios ao executar uma varredura para E / Ss mais sequenciais ao executar uma varredura. para o DAS, é mais provável que você, por um SAN de slice-n-dice complexo, provavelmente não.

Desfragmentação no nível do sistema de arquivos - faça isso apenas com o SQL desligado. Eu mesmo nunca experimentei problemas aqui (já que nunca executei uma desfragmentação online de arquivos abertos de arquivos de banco de dados SQL), mas ouvi muitas evidências casuais de clientes e clientes de estranhos problemas de corrupção ocorrendo. A sabedoria geral não é fazer isso com o SQL online.

A fragmentação do índice é completamente ortogonal à fragmentação do arquivo. O SQL Server não tem idéia da fragmentação de arquivos - muitas camadas virtualizadas entre elas para ter qualquer esperança de trabalhar com as geometrias reais do subsistema de E / S. Fragmentação de índice, no entanto, SQL sabe tudo sobre. Sem me repetir muito da resposta que você já mencionou, a fragmentação do índice impedirá que o SQL faça uma leitura eficiente do intervalo de varredura, independentemente de quão fragmentados (ou não) os arquivos estejam no nível do sistema de arquivos. Então, absolutamente, você deve atenuar a fragmentação do índice se observar um desempenho de consulta degradante.

Você não tem para fazer isso em uma determinada ordem, embora se você cuida da fragmentação do sistema de arquivos e então reconstrua todos os seus índices e cause mais fragmentação do sistema de arquivos ao crescer vários arquivos um volume desfragmentado, você provavelmente ficará incomodado. Isso causará algum problema no perf? Como discutido acima, isso depende :-D

Espero que isso ajude!

    
por 24.06.2009 / 17:01
3

What's the best way to fix physical file fragmentation on a production SQL box?

Eu corro o contig do SYSINTERNALS em meus arquivos de banco de dados.

Veja o link

    
por 24.06.2009 / 16:35
2

Eu recomendaria dimensionar o db apropriadamente, desligar o sql server, copiar o arquivo do banco de dados para outro array de disco e copiá-lo de volta para desfragmentá-lo. Muito mais rápido do que usar o Windows defrag em minha experiência.

    
por 29.06.2009 / 19:57
1

Eu tentei desfragmentar os discos físicos em uma solução scsi uma vez, mas recebi pouco ou nenhum aumento de desempenho. A lição que aprendi é que, se você experimentar um desempenho lento devido ao sistema de disco, isso não tem nada a ver com a fragmentação, até onde falamos arquivo de dados, já que ele está usando acesso aleatório.

Se os seus índices forem desfragmentados e as estatísticas forem atualizadas (muito importante) e você ainda ver E / S como gargalo, você sofre de outras coisas além da fragmentação física. Você já usou mais de 80% da unidade? Você tem unidades suficientes? Suas consultas são otimizadas o suficiente? Você está fazendo um monte de varredura de tabela ou pior ainda um monte de busca de índice seguido de pesquisa de índice de cluster? Veja os planos de consulta e use "set statistics io on" para descobrir o que realmente está acontecendo com sua consulta. (procure por um grande número de leituras lógicas ou físicas)

Por favor, deixe-me saber se estou completamente errado.

/ Håkan Winther

    
por 24.06.2009 / 22:49
1

Talvez os índices não estejam otimizados o suficiente para o seu aplicativo e você não tenha o Veritas I3 para otimizar seu banco de dados, então você poderia usar uma instrução como essa para encontrar índices ausentes:

       SELECT
      mid.statement,
      mid.equality_columns,
      mid.inequality_columns,
      mid.included_columns,
      migs.user_seeks,
      migs.user_scans,
      migs.last_user_seek,
      migs.avg_user_impact,
      user_scans,
      avg_total_user_cost,
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS [weight]--, migs.*--, mid.*
   FROM
      sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig
         ON (migs.group_handle = mig.index_group_handle)
      INNER JOIN sys.dm_db_missing_index_details AS mid
         ON (mig.index_handle = mid.index_handle)
   ORDER BY
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ;

Ou uma declaração como essa para encontrar índices que não são usados em instruções selecionadas e reduzem o desempenho de atualizações / inserções:

    CREATE PROCEDURE [ADMIN].[spIndexCostBenefit]
    @dbname [nvarchar](75)
WITH EXECUTE AS CALLER
AS
--set @dbname='Chess'
declare @dbid nvarchar(5)
declare @sql nvarchar(2000)
select @dbid = convert(nvarchar(5),db_id(@dbname))

set @sql=N'select ''object'' = t.name,i.name
        ,''user reads'' = iu.user_seeks + iu.user_scans + iu.user_lookups
        ,''system reads'' = iu.system_seeks + iu.system_scans + iu.system_lookups
        ,''user writes'' = iu.user_updates
        ,''system writes'' = iu.system_updates
from '+ @dbname + '.sys.dm_db_index_usage_stats iu
,' + @dbname + '.sys.indexes i
,' + @dbname + '.sys.tables t
where 
    iu.database_id = ' + @dbid + '
and iu.index_id=i.index_id
and iu.object_id=i.object_id
and iu.object_id=t.object_id
AND (iu.user_seeks + iu.user_scans + iu.user_lookups)<iu.user_updates
order by ''user reads'' desc'

exec sp_executesql @sql

set @sql=N'SELECT
   ''object'' = t.name,
   o.index_id,
   ''usage_reads'' = user_seeks + user_scans + user_lookups,
   ''operational_reads'' = range_scan_count + singleton_lookup_count,
   range_scan_count,
   singleton_lookup_count,
   ''usage writes'' = user_updates,
   ''operational_leaf_writes'' = leaf_insert_count + leaf_update_count + leaf_delete_count,
   leaf_insert_count,
   leaf_update_count,
   leaf_delete_count,
   ''operational_leaf_page_splits'' = leaf_allocation_count,
   ''operational_nonleaf_writes'' = nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count,
   ''operational_nonleaf_page_splits'' = nonleaf_allocation_count
FROM
   ' + @dbname + '.sys.dm_db_index_operational_stats(' + @dbid + ', NULL, NULL, NULL) o,
   ' + @dbname + '.sys.dm_db_index_usage_stats u,
    ' + @dbname + '.sys.tables t
WHERE
   u.object_id = o.object_id
   AND u.index_id = o.index_id
    and u.object_id=t.object_id
ORDER BY
   operational_reads DESC,
   operational_leaf_writes,
   operational_nonleaf_writes'

exec sp_executesql @sql

GO

Eu tenho algumas outras instruções SQL que estou usando quando analiso problemas de desempenho no ambiente de produção, mas acho que esses dois são um bom começo.

(Eu sei, este post é um pouco de tópico, mas eu pensei que você poderia estar interessado, pois tem a ver com a estratégia de indexação)

/ Håkan Winther

    
por 25.06.2009 / 08:02