sys.dm_db_index_physical_stats (Transact-SQL)

Artigo
06/06/2024

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 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 existirem 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 do SQL Server usa o termo árvore B geralmente em referência a índices. Em índices de rowstore, o SQL Server implementa uma árvore B+. Isso não se aplica a índices columnstore ou armazenamentos de dados na 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 otimizados para memória. Para obter informações sobre o uso de índice otimizado para memória, consulte sys.dm_db_xtp_index_stats (Transact-SQL).

Se você consultar sys.dm_db_index_physical_stats em 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 esse modo de exibição de gerenciamento dinâmico adquire um IS bloqueio na tabela ou exibição de usuário especificada que pode bloquear solicitações por um REDO thread para um X bloqueio 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 é pequeno. As entradas válidas são a ID de um banco de dados, NULL, 0 ou DEFAULT. O padrão é 0. NULL, 0 e 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. Ao usar DB_ID sem especificar um nome de banco de dados, o nível de compatibilidade do banco de dados atual deve ser 90 ou superior.

object_id | NULO | 0 | INADIMPLÊNCIA

A ID do objeto da tabela ou exibição do índice está ativada. object_id é int.

As entradas válidas são a ID de uma tabela e exibição, NULL, 0 ou DEFAULT. O padrão é 0. NULL, 0 e DEFAULT, são valores equivalentes neste contexto. A partir do SQL Server 2016 (13.x), as entradas válidas também incluem o nome da fila do Service Broker 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 de 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 o ID de um índice, 0 se object_id for um heap, NULL, -1 ou DEFAULT. O padrão é -1. NULL, -1 e DEFAULT, são valores equivalentes neste contexto.

Especifique NULL para retornar informações de 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, 0 e DEFAULT, são valores equivalentes neste contexto.

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

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

modo | NULO | INADIMPLÊNCIA

O nome do modo. Especifica o nível de verificação usado para obter estatísticas. mode é sysname. Entradas válidas são DEFAULT, NULL, LIMITED, SAMPLED ou 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 = Heap.
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 - ÍNDICE CLUSTERIZADO - ÍNDICE NÃO AGRUPADO - ÍNDICE XML PRIMÁRIO - ÍNDICE ESTENDIDO - ÍNDICE XML - ÍNDICE DE MAPEAMENTO COLUMNSTORE (interno) - COLUMNSTORE DELETEBUFFER INDEX (interno) - COLUMNSTORE DELETEBITMAP INDEX (interno)
hobt_id	bigint	ID de heap ou árvore B do índice ou partição. Para índices columnstore, essa é a ID de um conjunto de linhas que rastreia dados internos 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 (Transact-SQL).
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 (Transact-SQL). 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 foram empurradas para fora da linha.
index_depth	tinyint	Número de níveis de índice. 1 = Heap, ou `LOB_DATAROW_OVERFLOW_DATA` unidade de alocação.
index_level	tinyint	Nível atual do índice. 0 para níveis de folha índice, heaps e `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de alocação. Maior que 0 para níveis de índice nonleaf. index_level é o mais alto no nível raiz de um índice. Os níveis não foliares dos índices só são processados quando mode = 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 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 mode = SAMPLED.
fragment_count	bigint	Número de fragmentos no nível foliar 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 foliares de um índice e `LOB_DATA` /ou `ROW_OVERFLOW_DATA` unidades de alocação. NULL para heaps quando mode = SAMPLED.
avg_fragment_size_in_pages	float	Número médio de páginas em um fragmento no nível de folha de uma `IN_ROW_DATA` unidade de alocação. NULL para níveis não foliares de um índice e `LOB_DATA` /ou `ROW_OVERFLOW_DATA` unidades de alocação. NULL para heaps quando mode = 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` ou `ROW_OVERFLOW_DATA` unidades de alocação, 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` ou `ROW_OVERFLOW_DATA` unidades de alocação, a média de todas as páginas na unidade de alocação. NULL quando mode = 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. Nota: Para um heap, o número de registros retornados dessa função pode não corresponder ao número de linhas retornadas executando um `SELECT COUNT()` contra o heap. Isso 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 de uma operação de atualização. Além disso, a maioria das linhas LOB grandes são divididas em vários registros no `LOB_DATA` armazenamento. Para `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de alocação, o número total de registros na unidade de alocação completa. NULL quando mode* = 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 foliares de um índice na `IN_ROW_DATA` unidade de alocação. NULL quando mode = 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 foliares de um índice na `IN_ROW_DATA` unidade de alocação. NULL quando mode = 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 registro na `IN_ROW_DATA` unidade de alocação. Para `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de alocação, o tamanho mínimo do registro na unidade de alocação completa. NULL quando mode = 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` ou `ROW_OVERFLOW_DATA` unidades de alocação, o tamanho máximo do registro na unidade de alocação completa. NULL quando mode = 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` ou `ROW_OVERFLOW_DATA` unidades de alocação, o tamanho médio do registro na unidade de alocação completa. NULL quando mode = 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 que não seja as unidades de `IN_ROW_DATA` alocação para um heap. NULL para heaps quando mode = LIMITED.
compressed_page_count	bigint	O número total de páginas compactadas. Para heaps, as páginas recém-alocadas não são compactadas com PAGE. Um heap é compactado com PAGE em 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 são compactadas com PAGE. Recrie um heap quando o `compressed_page_count` valor aumentar mais do que o limite desejado. Para tabelas que têm um índice clusterizado, o `compressed_page_count` valor indica a eficácia da compactação PAGE.
columnstore_delete_buffer_state	tinyint	0 = NOT_APPLICABLE 1 = OPEN 2 = DRENAGEM 3 = LAVAGEM 4 = APOSENTAÇÃO 5 = PRONTO Aplica-se a: SQL Server 2016 (13.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada 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. DRENAGEM - os deletores estão drenando, mas os scanners ainda o usam. FLUSHING - o buffer é fechado e as linhas no buffer estão sendo gravadas no bitmap de exclusão. RETIRING - as linhas no buffer de exclusão fechado foram gravadas no bitmap de exclusão, mas o buffer não foi truncado porque os scanners ainda o estão usando. Os novos scanners não precisam usar o buffer de desativação porque o buffer aberto é suficiente. PRONTO - Este 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 SQL do Azure
version_record_count	bigint	Esta é a contagem dos registros de versão de linha que estão sendo mantidos neste í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 sendo 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

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. modo é especificado como LIMITADO, AMOSTRADO ou DETALHADO. 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 de Intenção Compartilhada (IS), independentemente do modo em que ele é executado.

O modo LIMITED é o mais rápido e examina 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 examinadas no modo LIMITED.

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

O modo DETAILED examina 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 medir rapidamente o tamanho ou o nível de fragmentação de uma tabela ou índice, use o modo LIMITED. Ele é 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 interpreta NULL como um valor curinga especificando todos os bancos de dados ou todos os objetos.

Além disso, a OBJECT_ID função é processada antes que a sys.dm_db_index_physical_stats função seja chamada e, portanto, é avaliada no contexto do banco de dados atual, não no banco de dados especificado em database_id. Esse comportamento pode fazer com que a OBJECT_ID função retorne um valor NULL; ou, se o nome do objeto existe no contexto de banco de dados atual e no banco de dados especificado, uma mensagem de erro pode 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 um ID válido é devolvido quando utiliza DB_ID ou OBJECT_ID. Por exemplo, quando você usa OBJECT_IDo , 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 dos processos de modificações de dados (instruções INSERT, UPDATE e DELETE) 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 examinam parte dos índices de uma tabela ou todos eles, esse tipo de fragmentação pode causar leituras de página adicionais. Isso impede o exame paralelo 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 da 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 para avg_fragmentation_in_percent deve ser o mais próximo de zero possível para o desempenho máximo. Porém, valores de 0% a 10% podem ser aceitáveis. Podem ser usados todos os métodos de redução de fragmentação, como reconstruir, reorganizar ou recriar, para reduzir esses valores. Para obter mais informações sobre como analisar o grau de fragmentação em um índice, consulte Reorganizar e recriar índices.

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:

Descartar e recriar o índice clusterizado.

Recriar um índice clusterizado redistribui os dados e resulta em páginas de dados completas. O nível de preenchimento pode ser configurado usando a opção FILLFACTOR em CREATE INDEX. As desvantagens desse método são que o índice permanece offline durante o ciclo de descarte e recriação e que a operação é atômica. Se a criação do índice for interrompida, ele não será recriado. Para obter mais informações, veja CREATE INDEX (Transact-SQL).
Use ALTER INDEX REORGANIZE, o substituto de DBCC INDEXDEFRAG, para reordenar as páginas de nível de 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 reorganizar os dados quanto uma operação de reconstrução de índice e não atualiza estatísticas.
Use ALTER INDEX REBUILD, o substituto do DBCC DBREINDEX, para reconstruir 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 reconstruir 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 varreduras, porque a carga de trabalho é principalmente pesquisas de uma única tonelada, a remoção da fragmentação pode não ter efeito.

Observação

Executando 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 instrução ALTER INDEX REORGANIZE compacta páginas que contêm dados LOB (objetos grandes). Como as páginas LOB não são desalocadas quando vazias, compactar esses dados pode melhorar o uso do espaço em disco se muitos dados LOB tiverem sido 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 í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 a plenitude da página. Para obter o uso ideal do espaço em disco, esse valor deve ser próximo de 100% para um índice que não tenha muitas inserções aleatórias. No entanto, um índice que tem muitas inserções aleatórias e tem páginas muito cheias 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 opção FILLFACTOR especificada permite que o preenchimento da página seja alterado para atender ao padrão de consulta do índice. Para obter mais informações sobre o fator de preenchimento, consulte Especificar fator de preenchimento para um índice. Além disso, ALTER INDEX REORGANIZE compactará um índice tentando preencher páginas para o FILLFACTOR 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 e restrições

Não retorna dados para índices columnstore clusterizados.

Permissões

Requer as seguintes permissões:

Permissão CONTROL no objeto especificado no banco de dados.
EXIBIR ESTADO DO BANCO DE DADOS ou EXIBIR ESTADO DE DESEMPENHO DO BANCO DE DADOS (SQL Server 2022) permissão para retornar informações sobre todos os objetos no banco de dados especificado, usando o curinga do objeto @object_id=NULL.
Permissão VIEW SERVER STATE ou VIEW SERVER PERFORMANCE STATE (SQL Server 2022) para retornar informações sobre todos os bancos de dados, usando o curinga do banco de dados @database_id = NULL.

Conceder VIEW DATABASE STATE permite que todos os objetos no banco de dados sejam retornados, independentemente de qualquer permissão CONTROL negada a objetos específicos.

Negar VIEW DATABASE STATE impede que todos os objetos do banco de dados sejam retornados, independentemente de qualquer permissão CONTROL concedida a objetos específicos. Além disso, quando o curinga do banco de dados @database_id=NULL é especificado, o banco de dados é omitido.

Para obter mais informações, consulte Exibições e funções de gerenciamento dinâmico (Transact-SQL).

Exemplos

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, a permissão CONTROL na tabela Person.Address.

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 para o 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, a permissão CONTROL na tabela dbo.DatabaseLog.

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 para todas as tabelas e índices na instância do SQL Server especificando o curinga NULL para todos os parâmetros. A execução dessa consulta requer a permissão VIEW SERVER STATE.

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 recriar 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 desta consulta requer a permissão VIEW DATABASE STATE. Este exemplo especifica DB_ID como o primeiro parâmetro sem especificar um nome de banco de dados. Um erro será gerado se o banco de dados atual tiver um nível de compatibilidade de 80 ou inferior. Para resolver o erro, substitua DB_ID() por um nome de banco de dados válido. Para obter mais informações sobre níveis de compatibilidade de banco de dados, consulte Nível de compatibilidade ALTER DATABASE (Transact-SQL).

-- 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

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. Uso `sys.dm_db_index_physical_stats` no modo SAMPLED

O exemplo a seguir mostra como o modo SAMPLED retorna um aproximado que é diferente dos resultados do modo DETAILED.

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. Filas do agente de serviço de consulta 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 de servidor para 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);

Compartilhar via

sys.dm_db_index_physical_stats (Transact-SQL)

Sintaxe

Argumentos

database_id | NULO | 0 | INADIMPLÊNCIA

object_id | NULO | 0 | INADIMPLÊNCIA

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

partition_number | NULO | 0 | INADIMPLÊNCIA

modo | NULO | INADIMPLÊNCIA

Tabela retornada

Comentários

Modos de digitalização

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

Melhor prática

Detectar fragmentação

Fragmentação lógica

Fragmentação da extensão

Reduzir a fragmentação em um índice

Reduzir a fragmentação em um heap

Compactar dados de objetos grandes

Avaliar o uso do espaço em disco

Avaliar fragmentos de índice

Limitações e restrições

Permissões

Exemplos

R. Retornar informações sobre uma tabela especificada

B. Retornar informações sobre um heap

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

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

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

F. Uso `sys.dm_db_index_physical_stats` no modo SAMPLED

G. Filas do agente de serviço de consulta para fragmentação de índice

Confira também

Comentários

Comentários

Recursos adicionais

Compartilhar via

sys.dm_db_index_physical_stats (Transact-SQL)

Sintaxe

Argumentos

database_id | NULO | 0 | INADIMPLÊNCIA

object_id | NULO | 0 | INADIMPLÊNCIA

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

partition_number | NULO | 0 | INADIMPLÊNCIA

modo | NULO | INADIMPLÊNCIA

Tabela retornada

Comentários

Modos de digitalização

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

Melhor prática

Detectar fragmentação

Fragmentação lógica

Fragmentação da extensão

Reduzir a fragmentação em um índice

Reduzir a fragmentação em um heap

Compactar dados de objetos grandes

Avaliar o uso do espaço em disco

Avaliar fragmentos de índice

Limitações e restrições

Permissões

Exemplos

R. Retornar informações sobre uma tabela especificada

B. Retornar informações sobre um heap

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

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

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

F. Uso sys.dm_db_index_physical_stats no modo SAMPLED

G. Filas do agente de serviço de consulta para fragmentação de índice

Confira também

Comentários

Comentários

Recursos adicionais

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

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

F. Uso `sys.dm_db_index_physical_stats` no modo SAMPLED