Neste artigo, vamos aprender como identificar e resolver a Fragmentação do Índice no SQL Server. A identificação da fragmentação do índice e a manutenção do índice são partes importantes da tarefa de manutenção do banco de dados. O Microsoft SQL Server continua atualizando as estatísticas de índice com a atividade Inserir, Atualizar ou Excluir sobre a tabela. A fragmentação do índice é o valor do desempenho do índice em percentagem, que pode ser obtido pelo SQL Server DMV. De acordo com o valor de performance do índice, os usuários podem pegar os índices em manutenção, revisando a porcentagem de fragmentação com a ajuda de Rebuild ou Reorganize operation.
Por que a porcentagem de fragmentação do índice varia?
Percentual de fragmentação do índice varia quando as ordens lógicas de página não se coordenam com a ordem física de página na alocação de página de um índice. Com a modificação dos dados na tabela, as informações podem ser redimensionadas na página de dados. A página estava cheia no topo antes da operação de atualização na tabela. Entretanto, espaço livre pode ser encontrado na página de dados com uma operação de atualização na tabela. Os utilizadores podem observar a ordem perturbadora da página com a operação de eliminação massiva na tabela. Junto com as operações de atualização e exclusão, a página de dados não será uma página cheia no topo ou vazia. Portanto, o espaço livre não utilizado aumenta o descompasso de ordem entre a página lógica e a página física com o aumento da fragmentação, o que pode causar o pior desempenho da consulta e consumir mais recursos do servidor também.
Mais essencial para explicar que a fragmentação do índice afeta o desempenho da consulta somente com a varredura da página. Nesses casos, aumenta as chances da má performance de outras requisições SQL também, pois a consulta com o alto índice fragmentado sobre a tabela leva mais tempo para executar e consome mais recursos como Cache, CPU e IO. Portanto, o resto das requisições SQL tem dificuldade de terminar a operação com os recursos inconsistentes do servidor. Mesmo o bloqueio pode ocorrer pela operação Atualizar e Excluir porque o otimizador não irá reunir as informações da fragmentação do índice enquanto gera o plano de execução da consulta.
Pode haver um número de índices criados em uma única tabela com a combinação de várias colunas, e cada índice pode ter uma porcentagem de fragmentação diferente. Agora, antes de torná-lo apropriado ou tomar um índice em manutenção, os usuários têm de encontrar esse valor limiar a partir do banco de dados. A declaração T-SQL abaixo é uma maneira eficiente de encontrá-lo com detalhes do objeto.
Find Index Fragmentation status usando a instrução T-Instrução SQL
|
1
2
3
4
5
6
7
8
9
10
11
12
14
|
SELECCIONAR S.nome como ‘Esquema’,
T.name como ‘Tabela’,
I.name como ‘Índice’,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tabelas T em T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S em T.schema_id = S.schema_id
INNER JOIN sys.indexes I em I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDEM POR DDIPS.avg_fragmentation_in_percent desc
|
Aqui, podemos ver que a percentagem média máxima de fragmentação é perceptível como 99%, que deve ser engajada com uma ação para reduzir a fragmentação com as escolhas de REBUILDAR ou REORGANIZAR. REBUILD ou REORGANIZE é o comando de manutenção do índice que pode ser executado com a instrução ALTER INDEX. Os usuários também podem executar este comando usando SSMS.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Find and expand the table in Object Explorer >> Open Indexes >> Clique com o botão direito do mouse no índice alvo >> Rebuild or Reorganize.
Como visível na imagem acima, REBUILD e REORGANIZAR são as duas opções disponíveis para reproduzir a operação de acabamento sobre a página. Idealmente, esta operação deve ser feita no tempo de off-peak para evitar o seu impacto em outras transações e usuários. Microsoft SQL Server Enterprise Edition suporta características de índice online e offline com índice REBUILD.
REBUILD INDEX
INDEX REBUILD sempre abandona o índice e o reproduz com novas páginas de índice. Esta atividade pode ser executada em paralelo usando uma opção online (Enterprise Edition) com o comando ALTER INDEX, que não afeta as requisições e tarefas de uma tabela similar.
REBUILD Index pode ser definido online ou offline usando os comandos SQL abaixo:
|
1
2
3
4
5
|
–Comando Basic Rebuild
ALTER INDEX Index_Name ON Tabela_Name REBUILD
–REBUILD Index with ONLINE OPTION
ALTER INDEX Index_Name ON Tabela_Nome REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Se um usuário executa o REBUILD INDEX offline, então o recurso objeto (Tabela) do índice não estará acessível até o final do processo REBUILD. Ele também afeta inúmeras outras transações, que estão associadas a este objeto. A operação Reconstruir índice recria o índice. Portanto, ela gera novas estatísticas e anexa também os registros de log do índice no arquivo de log de transações do banco de dados.
Por exemplo, antes de reconstruir o índice, vamos pegar a atual alocação de páginas para o índice do banco de dados AdventureWorks, tabela Sales.OrderTracking e IX_OrderTracking_CarrierTrackingNumber nomeado índice.
|
1
2
3
4
5
|
SELECCIONAR OBJECT_NAME(IX.).object_id) como db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si em IX.object_id = si.object_id E IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Aqui, Existem páginas de 1961 no arquivo da base de dados para este índice, e as primeiras 5 páginas são as 861, 862, 1627, 1628, e 1904 na ordem do número da página. Agora, vamos reconstruir o índice usando SSMS.
Index REBUILD operação é completada com sucesso e tomar referências de alocação de página para o mesmo índice com a ajuda da mesma consulta T-SQL novamente.
|
1
2
3
4
5
6
|
SELECCIONAR OBJECT_NAME(IX.object_id) como db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si em IX.object_id = si.object_id E IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Depois de reconstruir o índice, a contagem de páginas actualizada é de 1457, que era de 1961 antes. Se você verificar as 5 primeiras páginas alocadas do mesmo índice, ele foi alterado com as novas referências de página. Presume-se que o índice é descartado e feito mais uma vez. Devemos verificar a porcentagem de fragmentação atualizada para o mesmo índice, e como pode ser visto abaixo, é 0,1% agora.
REBUILD índice agrupado sobre a tabela afeta outros índices da tabela também porque o índice agrupado REBUILD reconstrói o índice não agrupado da tabela também. Execute a operação de reconstrução em todos os índices da tabela ou banco de dados em conjunto; um usuário pode usar o comando DBCC DBREINDEX().
|
1
|
DBCC DBREINDEX (‘DatabaseName’, ‘TableName’);
|
INDEX REORGANIZADO
O comando REORGANIZAR INDEX reordena a página de índice expulsando o espaço livre ou não utilizado na página. O ideal é que as páginas de índice sejam reordenadas fisicamente no arquivo de dados. REORGANIZAR não deixa cair e cria o índice, mas simplesmente reestruturar a informação na página. REORGANIZE não tem nenhuma escolha offline, e REORGANIZE não afecta as estatísticas em comparação com a opção REBUILD. REORGANIZE executa online sempre.
Por exemplo, antes de executar o REORGANIZE sobre o índice, vamos fazer a leitura de fragmentação para a base de dados ‘AdventureWorks’, tabela ‘Sales.OrderTracking’ e índice chamado ‘IX_OrderTracking_SalesOrderID’.
Aqui, a porcentagem de fragmentação do índice é 98,39 antes do REORGANIZE. A lista abaixo na imagem é as páginas de alocação ao índice.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) como db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Vendas.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si em IX.object_id = si.object_id E IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Aqui, um total de 459 páginas estão listadas na imagem acima, e as primeiras cinco páginas são 1065, 1068, 1069, 1944, e 1945. Agora, vamos executar o comando REORGANIZAR no índice usando a declaração T-SQL abaixo e olhar novamente para a alocação da página.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Aqui, a contagem total de páginas é reduzida para 331, que era de 459 antes. Além disso, não vemos novas páginas na primeira lista de cinco páginas, o que implica que os dados são apenas reestruturados – e não reenchidos novamente. Mesmo que você pudesse ver novas páginas também, isso acontece na situação em que o grande índice está fortemente fragmentado, e a remodelação sobre os dados usa uma nova página.
Para realizar a operação REORGANIZAR índice em todos os índices da tabela ou banco de dados juntos, o usuário pode usar o comando DBCC INDEXDEFRAG():
|
1
|
DBCC INDEXDEFRAG(‘DatabaseName’, ‘TableName’);
|
Como visto, existe uma diferença substancial entre o índice REBUILD e REORGANIZAR. Aqui os usuários têm a opção de escolher uma das alternativas de acordo com a porcentagem de fragmentação do índice. Podemos compreender que não existem padrões documentados; contudo, o administrador da base de dados segue a equação padrão de acordo com a exigência do tamanho e tipo de informação do Índice.
Determinação habitual do uso da equação :
- Quando a percentagem de Fragmentação está entre 15-30: REORGANIZAR
- Quando a Fragmentação é maior que 30: REBUILD
A opção REBUILD é mais útil com a opção ONLINE quando a base de dados não está disponível para fazer a manutenção do índice em horas off-peak.
Conclusão
Fragmentação do índice é uma fragmentação interna no arquivo de dados. Os parâmetros principais de desempenho rápido da sua base de dados são a Arquitectura da Base de Dados, o Desenho da Base de Dados, e a escrita da Consulta. Um bom design de índice com manutenção sempre aumenta a performance da consulta no motor da base de dados.
- Autor
- Recent Posts
Ver todos os posts de Jignesh Raiyani
- Página Life Expectancy (PLE) no SQL Server – 17 de Julho, 2020
- Como automatizar o particionamento de tabelas no SQL Server – 7 de julho de 2020
- Configurar o SQL Server Always On Availability Groups no AWS EC2 – 6 de julho de 2020