sys.dm_db_index_physical_stats (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Retorna informações de tamanho e fragmentação para os dados e índices da tabela ou exibição especificada no Mecanismo de Banco de Dados do SQL Server. Para um índice, uma linha é retornada para cada nível da árvore B em cada partição. Para um heap, uma linha é retornada para a unidade de alocação IN_ROW_DATA de cada partição. Para dados de objeto grande (LOB), uma linha é retornada para a LOB_DATA unidade de alocação de cada partição. Se houver dados de estouro de linha na tabela, uma linha será retornada para a ROW_OVERFLOW_DATA unidade de alocação em cada partição.

Observação

A documentação usa o termo árvore B geralmente em referência a índices. Em índices rowstore, o Mecanismo de Banco de Dados implementa uma árvore B+. Isso não se aplica a índices columnstore ou índices em tabelas com otimização de memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.

sys.dm_db_index_physical_stats não retorna informações sobre índices com otimização de memória. Para obter informações sobre o uso de índice com otimização de memória, consulte sys.dm_db_xtp_index_stats.

Se você consultar sys.dm_db_index_physical_stats uma instância de servidor que está hospedando uma réplica secundária legível do grupo de disponibilidade, poderá encontrar um problema de REDO bloqueio. Isso ocorre porque essa exibição de gerenciamento dinâmico adquire um bloqueio Intent-Shared (IS) na tabela ou exibição de usuário especificada que pode bloquear solicitações de um REDO thread para um bloqueio Exclusivo (X) nessa tabela ou exibição de usuário.

Convenções de sintaxe de Transact-SQL

Sintaxe

sys.dm_db_index_physical_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

Argumentos

database_id | NULO | 0 | INADIMPLÊNCIA

A ID do banco de dados. database_id é smallint. As entradas válidas são a ID de um banco de dados, NULL, 0, ou DEFAULT. O padrão é 0. NULL, 0e DEFAULT são valores equivalentes neste contexto.

Especifique NULL para retornar informações para todos os bancos de dados na instância do SQL Server. Se você especificar NULL para database_id, também deverá especificar NULL para object_id, index_id e partition_number.

A função interna DB_ID pode ser especificada. Quando você usa DB_ID sem especificar um nome de banco de dados, o nível de compatibilidade do banco de dados atual deve ser 90 ou maior.

object_id | NULO | 0 | INADIMPLÊNCIA

A ID do objeto da tabela ou exibição em que o índice está. object_id é int. As entradas válidas são a ID de uma tabela e exibição, NULL, 0ou DEFAULT. O padrão é 0. NULL, 0e DEFAULT são valores equivalentes neste contexto.

No SQL Server 2016 (13.x) e versões posteriores, as entradas válidas também incluem o nome da fila do agente de serviços ou o nome da tabela interna da fila. Quando os parâmetros padrão são aplicados (ou seja, todos os objetos, todos os índices, etc.), as informações de fragmentação para todas as filas são incluídas no conjunto de resultados.

Especifique NULL para retornar informações para todas as tabelas e exibições no banco de dados especificado. Se você especificar NULL para object_id, também deverá especificar NULL para index_id e partition_number.

index_id | 0 | NULO | -1 | INADIMPLÊNCIA

A ID do índice. index_id é int. As entradas válidas são a ID de um índice, 0 se object_id for um heap, NULL, -1ou DEFAULT. O padrão é -1. NULL, -1e DEFAULT são valores equivalentes neste contexto.

Especifique NULL para retornar informações para todos os índices de uma tabela base ou exibição. Se você especificar NULL para index_id, também deverá especificar NULL para partition_number.

partition_number | NULO | 0 | INADIMPLÊNCIA

O número da partição no objeto. partition_number é int. As entradas válidas são a partion_number de um índice ou heap, NULL, 0, ou DEFAULT. O padrão é 0. NULL, 0e DEFAULT são valores equivalentes neste contexto.

Especifique NULL para retornar informações para todas as partições do objeto proprietário.

partition_number é baseado em 1. Um índice ou heap não particionado partition_number definido como 1.

modo | NULO | INADIMPLÊNCIA

O nome do modo. mode especifica o nível de varredura usado para obter estatísticas. mode é sysname. As entradas válidas são DEFAULT, NULL, LIMITED, SAMPLEDou DETAILED. O padrão (NULL) é LIMITED.

Tabela retornada

Nome da coluna	Tipo de dados	Descrição
database_id	smallint	Identificação do banco de dados da tabela ou exibição. No Banco de Dados SQL do Azure, os valores são exclusivos em um único banco de dados ou em um pool elástico, mas não em um servidor lógico.
object_id	int	Identificação de objeto da tabela ou exibição na qual o índice se encontra.
index_id	int	Identificação de um índice. 0 = Pilha.
partition_number	int	Número de partição de base 1 no objeto proprietário; uma tabela, exibição ou índice. 1 = Índice ou heap não particionado.
index_type_desc	nvarchar(60)	Descrição do tipo de índice: - HEAP - CLUSTERED INDEX - NONCLUSTERED INDEX - PRIMARY XML INDEX - EXTENDED INDEX - XML INDEX - COLUMNSTORE MAPPING INDEX (interno) - COLUMNSTORE DELETEBUFFER INDEX (interno) - COLUMNSTORE DELETEBITMAP INDEX (interno)
alloc_unit_type_desc	nvarchar(60)	Descrição do tipo de unidade de alocação: - IN_ROW_DATA - LOB_DATA - ROW_OVERFLOW_DATA A LOB_DATA unidade de alocação contém os dados armazenados em colunas do tipo text, ntext, image, varchar(max), nvarchar(max), varbinary(max) e xml. Para obter mais informações, consulte Tipos de dados. A ROW_OVERFLOW_DATA unidade de alocação contém os dados armazenados em colunas do tipo varchar(n), nvarchar(n), varbinary(n) e sql_variant que são enviadas para fora da linha.
index_depth	tinyint	Número de níveis de índice. 1 = Heap, ou LOB_DATA ou ROW_OVERFLOW_DATA unidade de alocação.
index_level	tinyint	Nível atual do índice. 0 para níveis de folha de índice, heaps e LOB_DATA /ou ROW_OVERFLOW_DATA unidades de alocação. Maior do que 0 para níveis de índice não foliar. index_level é o mais alto no nível raiz de um índice. Os níveis não folha de índices são processados apenas quando o modo é DETAILED.
avg_fragmentation_in_percent	float	Fragmentação lógica para índices ou fragmentação de extensão para heaps na IN_ROW_DATA unidade de alocação. O valor é medido como uma porcentagem e leva em consideração vários arquivos. Para obter definições de fragmentação lógica e de extensão, consulte Comentários. 0 para LOB_DATA e ROW_OVERFLOW_DATA unidades de alocação. NULL para heaps quando o modo é SAMPLED.
fragment_count	bigint	Número de fragmentos no nível folha de uma IN_ROW_DATA unidade de alocação. Para obter mais informações sobre fragmentos, consulte Comentários. NULL para níveis não folha de um índice e LOB_DATA /ou ROW_OVERFLOW_DATA unidades de alocação. NULL para heaps quando o modo é SAMPLED.
avg_fragment_size_in_pages	float	Número médio de páginas em um fragmento no nível folha de uma IN_ROW_DATA unidade de alocação. NULL para níveis não folha de um índice e LOB_DATA /ou ROW_OVERFLOW_DATA unidades de alocação. NULL para heaps quando o modo é SAMPLED.
page_count	bigint	Número total de páginas de índice ou dados. Para um índice, o número total de páginas de índice no nível atual da árvore B na IN_ROW_DATA unidade de alocação. Para um heap, o número total de páginas de dados na IN_ROW_DATA unidade de alocação. Para LOB_DATA unidades de alocação ou ROW_OVERFLOW_DATA , número total de páginas na unidade de alocação.
avg_page_space_used_in_percent	float	Porcentagem média de espaço de armazenamento de dados disponível usada em todas as páginas. Para um índice, a média se aplica ao nível atual da árvore B na IN_ROW_DATA unidade de alocação. Para um heap, a média de todas as páginas de dados na IN_ROW_DATA unidade de alocação. Para LOB_DATA unidades de alocação ou ROW_OVERFLOW_DATA , a média de todas as páginas na unidade de alocação. NULL Quando o modo é LIMITED.
record_count	bigint	Número total de registros. Para um índice, o número total de registros se aplica ao nível atual da árvore B na IN_ROW_DATA unidade de alocação. Para um heap, o número total de registros na IN_ROW_DATA unidade de alocação. Observação: Para um heap, o número de registros retornados dessa função pode não corresponder ao número de linhas retornadas pela execução de um SELECT COUNT(*) heap. Isso ocorre porque uma linha pode conter vários registros. Por exemplo, em algumas situações de atualização, uma única linha de heap pode ter um registro de encaminhamento e um registro encaminhado como resultado da operação de atualização. Além disso, a maioria das linhas LOB grandes é dividida em vários registros no LOB_DATA armazenamento. Para LOB_DATA unidades de alocação ou ROW_OVERFLOW_DATA , o número total de registros na unidade de alocação completa. NULL Quando o modo é LIMITED.
ghost_record_count	bigint	Número de registros fantasmas prontos para remoção pela tarefa de limpeza fantasma na unidade de alocação. 0 para níveis não folha de um índice na IN_ROW_DATA unidade de alocação. NULL Quando o modo é LIMITED.
version_ghost_record_count	bigint	Número de registros fantasmas retidos por uma transação de isolamento de instantâneo pendente em uma unidade de alocação. 0 para níveis não folha de um índice na IN_ROW_DATA unidade de alocação. NULL Quando o modo é LIMITED.
min_record_size_in_bytes	int	Tamanho de registro mínimo em bytes. Para um índice, o tamanho mínimo do registro se aplica ao nível atual da árvore B na IN_ROW_DATA unidade de alocação. Para um heap, o tamanho mínimo do IN_ROW_DATA registro na unidade de alocação. Para LOB_DATA unidades de alocação ou ROW_OVERFLOW_DATA , o tamanho mínimo do registro na unidade de alocação completa. NULL Quando o modo é LIMITED.
max_record_size_in_bytes	int	Tamanho de registro máximo em bytes. Para um índice, o tamanho máximo do registro se aplica ao nível atual da árvore B na IN_ROW_DATA unidade de alocação. Para um heap, o tamanho máximo do IN_ROW_DATA registro na unidade de alocação. Para LOB_DATA unidades de alocação ou ROW_OVERFLOW_DATA , o tamanho máximo do registro na unidade de alocação completa. NULL Quando o modo é LIMITED.
avg_record_size_in_bytes	float	Tamanho de registro médio em bytes. Para um índice, o tamanho médio do registro se aplica ao nível atual da árvore B na IN_ROW_DATA unidade de alocação. Para um heap, o tamanho médio do IN_ROW_DATA registro na unidade de alocação. Para LOB_DATA unidades de alocação ou ROW_OVERFLOW_DATA , o tamanho médio do registro na unidade de alocação completa. NULL Quando o modo é LIMITED.
forwarded_record_count	bigint	Número de registros em um heap com ponteiros encaminhados a outro local de dados. (Esse estado ocorre durante uma atualização, quando não há espaço suficiente para armazenar a nova linha no local original.) NULL para qualquer unidade de alocação diferente das IN_ROW_DATA unidades de alocação de um heap. NULL para heaps quando o modo é LIMITED.
compressed_page_count	bigint	O número total de páginas compactadas. Para heaps, as páginas recém-alocadas não PAGE são compactadas. Um heap é PAGE compactado sob duas condições especiais: quando os dados são importados em massa ou quando um heap é reconstruído. As operações DML típicas que causam alocações de página não PAGE são compactadas. Recompile um heap quando o compressed_page_count valor crescer maior do que o limite desejado. Para tabelas que têm um índice clusterizado, o compressed_page_count valor indica a eficácia da PAGE compactação.
hobt_id	bigint	Heap ou ID de árvore B do índice ou partição. Para índices columnstore, essa é a ID de um conjunto de linhas que rastreia dados internos de columnstore para uma partição. Os conjuntos de linhas são armazenados como heaps de dados ou árvores B. Eles têm a mesma ID de índice que o índice columnstore pai. Para obter mais informações, consulte sys.internal_partitions.
columnstore_delete_buffer_state	tinyint	0 = NOT_APPLICABLE 1 = OPEN 2 = DRAINING 3 = FLUSHING 4 = RETIRING 5 = READY Aplica-se a: SQL Server 2016 (13.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure
columnstore_delete_buffer_state_desc	nvarchar(60)	NOT VALID - O índice pai não é um índice columnstore. OPEN - Deleters e scanners usam isso. DRAINING - Os deletores estão se esgotando, mas os scanners ainda o usam. FLUSHING - O buffer está fechado e as linhas no buffer estão sendo gravadas no bitmap de exclusão. RETIRING - As linhas no buffer de exclusão fechada foram gravadas no bitmap de exclusão, mas o buffer não foi truncado porque os scanners ainda o estão usando. Novos scanners não precisam usar o buffer de desativação porque o buffer aberto é suficiente. READY - Esse buffer de exclusão está pronto para uso. Aplica-se a: SQL Server 2016 (13.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure
version_record_count	bigint	Essa é a contagem dos registros de versão de linha que estão sendo mantidos nesse índice. Essas versões de linha são mantidas pelo recurso Recuperação acelerada de banco de dados. Aplica-se a: SQL Server 2019 (15.x) e versões posteriores e Banco de Dados SQL do Azure
inrow_version_record_count	bigint	Contagem de registros de versão ADR mantidos na linha de dados para recuperação rápida. Aplica-se a: SQL Server 2019 (15.x) e versões posteriores e Banco de Dados SQL do Azure
inrow_diff_version_record_count	bigint	Contagem de registros de versão ADR mantidos na forma de diferenças em relação à versão base. Aplica-se a: SQL Server 2019 (15.x) e versões posteriores e Banco de Dados SQL do Azure
total_inrow_version_payload_size_in_bytes	bigint	Tamanho total em bytes dos registros de versão em linha para esse índice. Aplica-se a: SQL Server 2019 (15.x) e versões posteriores e Banco de Dados SQL do Azure
offrow_regular_version_record_count	bigint	Contagem de registros de versão mantidos fora da linha de dados original. Aplica-se a: SQL Server 2019 (15.x) e versões posteriores e Banco de Dados SQL do Azure
offrow_long_term_version_record_count	bigint	Contagem de registros de versão considerados de longo prazo. Aplica-se a: SQL Server 2019 (15.x) e versões posteriores e Banco de Dados SQL do Azure

Observação

A documentação usa o termo árvore B geralmente em referência a índices. Em índices rowstore, o Mecanismo de Banco de Dados implementa uma árvore B+. Isso não se aplica a índices columnstore ou índices em tabelas com otimização de memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.

Comentários

A função de gerenciamento dinâmico sys.dm_db_index_physical_stats substitui a declaração DBCC SHOWCONTIG.

Modos de digitalização

O modo em que a função é executada determina o nível do exame executado para obter os dados estatísticos usados pela função. mode é especificado como LIMITED, SAMPLED, ou DETAILED. A função atravessa as cadeias de páginas para as unidades de alocação que compõem as partições especificadas da tabela ou índice. sys.dm_db_index_physical_stats requer apenas um bloqueio de tabela Intent-Shared (IS), independentemente do modo em que ele é executado.

O LIMITED modo é o modo mais rápido e digitaliza o menor número de páginas. Para um índice, apenas as páginas de nível pai da árvore b (ou seja, aquelas acima do nível folha) são examinadas. Para um heap, as páginas PFS e IAM associadas são examinadas e as páginas de dados de um heap são verificadas no LIMITED modo.

Com LIMITED o modo compressed_page_count é NULL porque o Mecanismo de Banco de Dados verifica apenas páginas não folha da árvore B e as páginas IAM e PFS do heap. Use SAMPLED o modo para obter um valor estimado para compressed_page_count, e use DETAILED o modo para obter o valor real para compressed_page_count. O SAMPLED modo retorna estatísticas com base em uma amostra de 1% de todas as páginas no índice ou heap. Os resultados no SAMPLED modo devem ser considerados aproximados. Se o índice ou heap tiver menos de 10.000 páginas, DETAILED o modo será usado em vez de SAMPLED.

O DETAILED modo verifica todas as páginas e retorna todas as estatísticas.

Os modos são progressivamente mais lentos de LIMITED para DETAILED, porque mais trabalho é executado em cada modo. Para avaliar rapidamente o tamanho ou o nível de fragmentação de uma tabela ou índice, use o LIMITED modo. É o mais rápido e não retorna uma linha para cada nível não folha na IN_ROW_DATA unidade de alocação do índice.

Usar funções do sistema para especificar valores de parâmetro

Você pode usar as funções Transact-SQL DB_ID e OBJECT_ID para especificar um valor para os parâmetros database_id e object_id . No entanto, passar valores que não são válidos para essas funções pode causar resultados não intencionais. Por exemplo, se o nome do banco de dados ou do objeto não puder ser encontrado porque eles não existem ou estão escritos incorretamente, ambas as funções retornarão NULL. A sys.dm_db_index_physical_stats função é interpretada NULL como um valor curinga que especifica todos os bancos de dados ou todos os objetos.

Além disso, a função é processada OBJECT_ID antes de sys.dm_db_index_physical_stats ser chamada e, portanto, é avaliada no contexto do banco de dados atual, não no banco de dados especificado no database_id. Esse comportamento pode fazer com que a OBJECT_ID função retorne um NULL valor; ou, se o nome do objeto existir no contexto do banco de dados atual e no banco de dados especificado, uma mensagem de erro será retornada. Os exemplos seguintes demonstram esses resultados não intencionais.

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database.
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks2022'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2022;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

Melhor prática

Certifique-se sempre de que uma ID válida seja retornada ao usar DB_ID ou OBJECT_ID. Por exemplo, quando você usa OBJECT_ID, especifique um nome de três partes, como OBJECT_ID(N'AdventureWorks2022.Person.Address'), ou teste o valor retornado pelas funções antes de usá-las na sys.dm_db_index_physical_stats função. Os exemplos A e B a seguir demonstram um modo seguro de especificar identificações de banco de dados e objeto.

Detectar fragmentação

A fragmentação ocorre por meio do processo de modificações de dados (INSERT, UPDATEe DELETE instruções) que são feitas na tabela e, portanto, nos índices definidos na tabela. Como essas modificações normalmente não são distribuídas igualmente entre as linhas da tabela e dos índices, a plenitude de cada página pode variar ao longo do tempo. Para consultas que verificam parte ou todos os índices de uma tabela, esse tipo de fragmentação pode causar mais leituras de página, o que dificulta a verificação paralela de dados.

O nível de fragmentação de um índice ou heap é mostrado na avg_fragmentation_in_percent coluna. Para heaps, o valor representa a fragmentação de extensão do heap. Para índices, o valor representa a fragmentação lógica do índice. Ao contrário DBCC SHOWCONTIGdo , os algoritmos de cálculo de fragmentação em ambos os casos consideram o armazenamento que abrange vários arquivos e, portanto, são precisos.

Fragmentação lógica

É a porcentagem de páginas com problema nas páginas de folha de um índice. Uma página fora de ordem é aquela para a qual a próxima página física alocada no índice não é a apontada pelo ponteiro de próxima página na página folha atual.

Fragmentação de extensão

É a porcentagem de extensões com problema nas páginas de folha de um heap. Uma extensão fora de ordem é aquela para a qual a extensão que contém a página atual de um heap não é fisicamente a próxima extensão após a extensão que contém a página anterior.

O valor de avg_fragmentation_in_percent deve ser o mais próximo possível de zero para desempenho máximo. No entanto, valores de 0% a 10% podem ser aceitáveis. Todos os métodos de redução da fragmentação, como reconstrução, reorganização ou recriação, podem ser usados para reduzir esses valores. Para obter mais informações sobre como analisar o grau de fragmentação em um índice, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.

Reduzir a fragmentação em um índice

Quando um índice estiver fragmentado de forma que a fragmentação afete o desempenho da consulta, há três opções para reduzir a fragmentação:

• Descarte e recrie o índice clusterizado.

  A recriação de um índice clusterizado redistribui os dados e resulta em páginas de dados completas. O nível de preenchimento pode ser configurado com a opção FILLFACTOR em CREATE INDEX. As desvantagens desse método são que o índice está offline durante o ciclo de descarte e recriação e que a operação é atômica. Se a criação do índice for interrompida, o índice não será recriado. Para obter mais informações, confira CREATE INDEX.

• Use ALTER INDEX REORGANIZE, a substituição de DBCC INDEXDEFRAG, para reordenar as páginas de nível folha do índice em uma ordem lógica. Como essa operação é online, o índice permanecerá disponível enquanto a instrução estiver sendo executada. A operação também pode ser interrompida sem perda do trabalho já concluído. A desvantagem desse método é que ele não faz um trabalho tão bom de reorganização dos dados quanto uma operação de recompilação de índice e não atualiza estatísticas.

• Use ALTER INDEX REBUILD, a substituição de DBCC DBREINDEX, para recriar o índice online ou offline. Para mais informações, consulte ALTERAR ÍNDICE (Transact-SQL).

A fragmentação por si só não é um motivo suficiente para reorganizar ou recompilar um índice. O efeito principal da fragmentação é que ela reduz a velocidade da taxa de transferência read-ahead da página durante os exames de índice. O resultado é tempos de resposta mais lentos. Se a carga de trabalho de consulta em uma tabela ou índice fragmentado não envolver verificações, como a carga de trabalho é principalmente pesquisas singleton, a remoção da fragmentação não terá efeito.

Observação

Executar DBCC SHRINKFILE ou DBCC SHRINKDATABASE pode introduzir fragmentação se um índice for parcial ou completamente movido durante a operação de redução. Assim, se for necessário executar uma operação de redução, você deverá fazer isso antes da remoção da fragmentação.

Reduzir a fragmentação em um heap

Para reduzir a extensão da fragmentação de um heap, crie um índice clusterizado na tabela e descarte o índice. Isso redistribui os dados enquanto o índice clusterizado é criado. E também otimiza o máximo possível esse processo, enquanto considera a distribuição de espaço livre disponível no banco de dados. Quando o índice clusterizado é descartado para recriar o heap, os dados não são movidos e permanecem na posição ideal. Para obter informações sobre como executar essas operações, consulte CREATE INDEX e DROP INDEX.

Cuidado

Criar e descartar um índice clusterizado em uma tabela recria todos os índices não clusterizados nessa tabela duas vezes.

Compactar dados de objetos grandes

Por padrão, a ALTER INDEX REORGANIZE instrução compacta páginas que contêm dados de objeto grande (LOB). Como as páginas LOB não são desalocadas quando vazias, a compactação desses dados pode melhorar o uso do espaço em disco se muitos dados LOB forem excluídos ou uma coluna LOB for descartada.

Reorganizar um índice clusterizado especificado compacta todas as colunas LOB contidas no índice clusterizado. Reorganizar um índice não clusterizado compacta todas as colunas LOB não-chave (incluídas) no índice. Quando ALL é especificado na instrução, todos os índices associados à tabela ou exibição especificada são reorganizados. Além disso, todas as colunas LOB associadas ao índice clusterizado, à tabela subjacente ou ao índice não clusterizado com colunas incluídas são compactadas.

Avaliar o uso do espaço em disco

A avg_page_space_used_in_percent coluna indica o preenchimento da página. Para obter o uso ideal do espaço em disco, esse valor deve estar próximo de 100% para um índice que não tem muitas inserções aleatórias. No entanto, um índice que tem muitas inserções aleatórias e páginas muito inteiras tem um número maior de divisões de página. Isso causa mais fragmentação. Por isso, para reduzir as divisões de página, o valor deve ser menor que 100%. A recriação de um índice com a FILLFACTOR opção especificada permite que a plenitude da página seja alterada para se ajustar ao padrão de consulta no índice. Para obter mais informações sobre o fator de preenchimento, consulte Especificar o fator de preenchimento para um índice. Além disso, ALTER INDEX REORGANIZE compactará um índice tentando preencher as páginas com o FILLFACTOR que foi especificado pela última vez. Isso aumenta o valor em avg_space_used_in_percent. ALTER INDEX REORGANIZE não pode reduzir a plenitude da página. Em vez disso, o índice deverá ser recriado.

Avaliar fragmentos de índice

Um fragmento é composto de páginas de folha fisicamente consecutivas no mesmo arquivo de uma unidade de alocação. Um índice tem pelo menos um fragmento. O máximo de fragmentos que um índice pode ter é igual ao número de páginas no nível folha do índice. Fragmentos maiores indicam que menos E/S de disco é necessária para ler o mesmo número de páginas. Portanto, quanto maior o avg_fragment_size_in_pages valor, melhor o desempenho da varredura de intervalo. Os avg_fragment_size_in_pages valores e avg_fragmentation_in_percent são inversamente proporcionais entre si. Por isso, a reconstrução ou a reorganização de um índice deve reduzir a quantidade de fragmentação e aumentar o tamanho do fragmento.

Limitações

Não retorna dados para índices columnstore clusterizados.

Permissões

Requer as seguintes permissões:

• CONTROL no objeto especificado no banco de dados.

• VIEW DATABASE STATEou VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) para retornar informações sobre todos os objetos no banco de dados especificado, usando o @object_id = NULL curinga de objeto.

• VIEW SERVER STATEou VIEW SERVER PERFORMANCE STATE (SQL Server 2022) para retornar informações sobre todos os bancos de dados, usando o @database_id = NULL curinga do banco de dados.

A concessão VIEW DATABASE STATE permite que todos os objetos no banco de dados sejam retornados, independentemente de quaisquer CONTROL permissões negadas em objetos específicos.

A negação VIEW DATABASE STATE não permite que todos os objetos no banco de dados sejam retornados, independentemente de quaisquer CONTROL permissões concedidas em objetos específicos. Além disso, quando o @database_id = NULL curinga do banco de dados é especificado, o banco de dados é omitido.

Para obter mais informações, consulte Exibições de gerenciamento dinâmico do sistema.

Exemplos

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

R. Retornar informações sobre uma tabela especificada

O exemplo a seguir retorna as estatísticas de tamanho e fragmentação de todos os índices e partições da tabela Person.Address. O modo de exame é definido como LIMITED para oferecer melhor desempenho e limitar as estatísticas retornadas. A execução dessa consulta requer, no mínimo, CONTROL permissão na Person.Address tabela.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.Person.Address');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B. Retornar informações sobre um heap

O exemplo a seguir retorna todas as estatísticas do heap dbo.DatabaseLog no banco de dados AdventureWorks2022. Como a tabela contém dados LOB, uma linha é retornada para a unidade de alocação LOB_DATA, além da linha retornada para IN_ROW_ALLOCATION_UNIT que está armazenando as páginas de dados do heap. A execução dessa consulta requer, no mínimo, CONTROL permissão na dbo.DatabaseLog tabela.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.DatabaseLog');
IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

C. Retornar informações para todos os bancos de dados

O exemplo a seguir retorna todas as estatísticas de todas as tabelas e índices dentro da instância do SQL Server especificando o curinga NULL para todos os parâmetros. A execução dessa consulta requer a VIEW SERVER STATE permissão.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

D. Usar sys.dm_db_index_physical_stats em um script para recompilar ou reorganizar índices

O exemplo a seguir reorganiza ou reconstrói automaticamente em um banco de dados todas as partições que têm uma fragmentação média de 10%. A execução dessa consulta requer a VIEW DATABASE STATE permissão. Este exemplo especifica DB_ID como o primeiro parâmetro sem especificar um nome de banco de dados.

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
    AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT *
FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;

    FETCH NEXT
    FROM partitions
    INTO @objectid,
        @indexid,
        @partitionnum,
        @frag;

    IF @@FETCH_STATUS < 0
        BREAK;

    SELECT @objectname = QUOTENAME(o.name),
        @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
        ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = @objectid
        AND index_id = @indexid;

    SELECT @partitioncount = count(*)
    FROM sys.partitions
    WHERE object_id = @objectid
        AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    IF @partitioncount > 1
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));

    EXEC (@command);

    PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

E. Use sys.dm_db_index_physical_stats para mostrar o número de páginas compactadas em página

O exemplo seguinte mostra como exibir e comparar o número total de páginas em relação às páginas que são compactadas por linha e página. Estas informações podem ser usadas para determinar o benefício que a compactação está fornecendo para um índice ou tabela.

SELECT o.name,
    ips.partition_number,
    ips.index_type_desc,
    ips.record_count,
    ips.avg_record_size_in_bytes,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.page_count,
    ips.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.objects o
    ON o.object_id = ips.object_id
ORDER BY record_count DESC;

F. Use sys.dm_db_index_physical_stats no modo SAMPLED

O exemplo a seguir mostra como SAMPLED mode retorna um aproximado diferente dos resultados do DETAILED mode.

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

DECLARE @idx INT = 0;

WHILE @idx < 1000000
BEGIN
    INSERT INTO t3 (col1, col2)
    VALUES (
        @idx,
        REPLICATE('a', 100) + CAST(@idx AS VARCHAR(10)) + REPLICATE('a', 380)
        )

    SET @idx = @idx + 1
END

COMMIT;
GO

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'SAMPLED');

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'DETAILED');

G. Consultar filas do agente de serviço para fragmentação de índice

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores

O exemplo a seguir mostra como consultar filas do agente do servidor em busca de fragmentação.

--Using queue internal table name
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT);

--Using queue name directly
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);

Conteúdo relacionado

• Exibições de gerenciamento dinâmico do sistema
• Exibições e funções de gerenciamento dinâmico relacionadas ao índice (Transact-SQL)
• sys.dm_db_index_operational_stats (Transact-SQL)
• sys.dm_db_index_usage_stats (Transact-SQL)
• sys.dm_db_partition_stats (Transact-SQL)
• sys.allocation_units (Transact-SQL)
• Referência Transact-SQL (Mecanismo de Banco de Dados)