sys.dm_db_index_physical_stats (Transact-SQL)

Artigo
12/19/2024

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada 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 uma pilha, 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 unidade de alocação de LOB_DATA de cada partição. Se existirem dados de estouro de linha na tabela, uma linha será retornada para a unidade de alocação de ROW_OVERFLOW_DATA em cada partição.

Observação

A documentação usa o termo árvore B geralmente em referência a índices. Em índices de armazenamento de linha, 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, consulte o SQL Server e o guia de design de arquitetura e design de índice 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.

Se você consultar sys.dm_db_index_physical_stats em uma instância do servidor que esteja hospedando um grupo de disponibilidade réplica secundária legível, poderá encontrar um problema de bloqueio de REDO. 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 por um thread de REDO para um bloqueio Exclusivo (X) nessa tabela ou exibição de usuário.

Transact-SQL convenções de sintaxe

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 é pequena. As entradas válidas são o ID de um banco de dados, NULL, 0ou 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_ide 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 superior.

object_id | NULO | 0 | INADIMPLÊNCIA

O ID do objeto da tabela ou exibição do índice está ativado. object_id é int. As entradas válidas são o 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 retornar informações para todos os índices de uma tabela ou exibição base. 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 partition_number de um índice ou heap, NULL, 0ou DEFAULT. O padrão é 0. NULL, 0e DEFAULT são valores equivalentes neste contexto.

Especifique NULL 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. modo especifica o nível de verificação usado para obter estatísticas. modo é 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`	pequeno	ID 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 pool elástico, mas não em um servidor lógico.
`object_id`	int	ID do objeto da tabela ou exibição em que o índice está.
`index_id`	int	ID de índice de um índice. `0` = Pilha.
`partition_number`	int	Número de partição baseado em 1 dentro do objeto proprietário; uma tabela, vista 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` (interna) - `COLUMNSTORE DELETEBUFFER INDEX` (interna) - `COLUMNSTORE DELETEBITMAP INDEX` (interna)
`alloc_unit_type_desc`	nvarchar(60)	Descrição do tipo de unidade de atribuição: - `IN_ROW_DATA` - `LOB_DATA` - `ROW_OVERFLOW_DATA` A unidade de alocação de `LOB_DATA` contém os dados armazenados em colunas do tipo texto, ntext, de imagem , varchar(max), nvarchar(max), varbinary(max)e xml. Para obter mais informações, consulte Tipos de dados. A unidade de alocação `ROW_OVERFLOW_DATA` contém os dados armazenados em colunas do tipo varchar(n), nvarchar(n), varbinary(n)e sql_variant que são empurradas para fora da linha.
`index_depth`	minúsculo	Número de níveis de índice. `1` = Heap, ou `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidade de alocação.
`index_level`	minúsculo	Nível atual do índice. `0` para níveis de folhas índices, pilhas e unidades de alocação `LOB_DATA` ou `ROW_OVERFLOW_DATA`. Maior que `0` para os níveis de índice não foliar. index_level é a mais alta no nível raiz de um índice. Os níveis não foliares dos índices só são processados quando modo é `DETAILED`.
`avg_fragmentation_in_percent`	flutuar	Fragmentação lógica para índices, ou fragmentação de extensão para pilhas na unidade de alocação `IN_ROW_DATA`. O valor é medido como uma porcentagem e leva em conta vários arquivos. Para definições de fragmentação lógica e de extensão, consulte Observações. `0` para `LOB_DATA` e `ROW_OVERFLOW_DATA` unidades de alocação. `NULL` para pilhas quando modo estiver `SAMPLED`.
`fragment_count`	bigint	Número de fragmentos no nível foliar de uma unidade de alocação de `IN_ROW_DATA`. Para obter mais informações sobre fragmentos, consulte Observações. `NULL` para níveis não foliares de um índice e `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de alocação. `NULL` para pilhas quando modo estiver `SAMPLED`.
`avg_fragment_size_in_pages`	flutuar	Número médio de páginas em um fragmento no nível de folha de uma unidade de alocação de `IN_ROW_DATA`. `NULL` para níveis não foliares de um índice e `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de alocação. `NULL` para pilhas quando modo estiver `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 unidade de alocação `IN_ROW_DATA`. Para uma pilha, o número total de páginas de dados na unidade de alocação de `IN_ROW_DATA`. Para `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de alocação, número total de páginas na unidade de repartição.
`avg_page_space_used_in_percent`	flutuar	Percentagem média de espaço de armazenamento de dados disponível utilizado em todas as páginas. Para um índice, a média aplica-se ao nível atual da árvore B na unidade de alocação `IN_ROW_DATA`. Para uma pilha, a média de todas as páginas de dados na unidade de alocação de `IN_ROW_DATA`. Para `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de alocação, a média de todas as páginas da unidade de alocação. `NULL` quando modo é `LIMITED`.
`record_count`	bigint	Número total de registos. Para um índice, o número total de registros se aplica ao nível atual da árvore B na unidade de alocação `IN_ROW_DATA`. Para uma pilha, o número total de registros na unidade de alocação de `IN_ROW_DATA`. 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()` no heap. Isso ocorre porque uma linha pode conter vários registros. Por exemplo, em algumas situações de atualização, uma única linha de pilha 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 são divididas em vários registros no armazenamento `LOB_DATA`. Para `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de atribuição, o número total de registos na unidade de atribuição completa. `NULL` quando 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 foliares de um índice na unidade de alocação `IN_ROW_DATA`. `NULL` quando 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 foliares de um índice na unidade de alocação `IN_ROW_DATA`. `NULL` quando modo é `LIMITED`.
`min_record_size_in_bytes`	int	Tamanho mínimo do registo em bytes. Para um índice, o tamanho mínimo do registro aplica-se ao nível atual da árvore B na unidade de alocação `IN_ROW_DATA`. Para uma pilha, o tamanho mínimo do registro na unidade de alocação de `IN_ROW_DATA`. Para `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de atribuição, a dimensão mínima do registo na unidade de atribuição completa. `NULL` quando modo é `LIMITED`.
`max_record_size_in_bytes`	int	Tamanho máximo do registo em bytes. Para um índice, o tamanho máximo do registro se aplica ao nível atual da árvore B na unidade de alocação `IN_ROW_DATA`. Para uma pilha, o tamanho máximo do registro na unidade de alocação de `IN_ROW_DATA`. Para `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de atribuição, a dimensão máxima do registo na unidade de atribuição completa. `NULL` quando modo é `LIMITED`.
`avg_record_size_in_bytes`	flutuar	Tamanho médio do registo em bytes. Para um índice, o tamanho médio do registro se aplica ao nível atual da árvore B na unidade de alocação `IN_ROW_DATA`. Para uma pilha, o tamanho médio do registro na unidade de alocação de `IN_ROW_DATA`. Para `LOB_DATA` ou `ROW_OVERFLOW_DATA` unidades de atribuição, a dimensão média do registo na unidade de atribuição completa. `NULL` quando modo é `LIMITED`.
`forwarded_record_count`	bigint	Número de registros em uma pilha que têm ponteiros de encaminhamento para 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 atribuição que não seja a unidade de atribuição `IN_ROW_DATA` para uma pilha. `NULL` para pilhas quando modo estiver `LIMITED`.
`compressed_page_count`	bigint	O número de páginas comprimidas. Para heaps, as páginas recém-alocadas não são `PAGE` compactadas. Uma pilha `PAGE` é compactada sob duas condições especiais: quando os dados são importados em massa ou quando uma pilha é reconstruída. As operações DML típicas que causam alocações de página não são compactadas `PAGE`. Reconstrua uma pilha quando o valor `compressed_page_count` crescer mais do que o limite desejado. Para tabelas que têm um índice clusterizado, o valor `compressed_page_count` indica a eficácia da compressão `PAGE`.
`hobt_id`	bigint	ID de heap ou árvore B do índice ou partição. Para índices columnstore, esta é a ID de um conjunto de linhas que rastreia dados columnstore internos para uma partição. Os conjuntos de linhas são armazenados como pilhas de dados ou árvores B. Eles têm o mesmo ID de índice que o índice columnstore pai. Para obter mais informações, consulte sys.internal_partitions.
`columnstore_delete_buffer_state`	minúsculo	`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 SQL do Azure
`columnstore_delete_buffer_state_desc`	nvarchar(60)	`NOT VALID` - O índice pai não é um índice columnstore. `OPEN` - deletores e scanners usam isso. `DRAINING` - os eliminadores estão a esgotar-se, mas os scanners continuam a utilizá-lo. `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. Novos scanners não precisam usar o buffer de desativação porque o buffer aberto é suficiente. `READY` - 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 de recuperação acelerada do 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 dos registos da versão ADR mantidos sob a forma de diferenças em relação à versão de 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 registos de versão em linha para este í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 instrução DBCC SHOWCONTIG.

Modos de digitalização

O modo em que a função é executada determina o nível de varredura realizada para obter os dados estatísticos que são usados pela função. modo é especificado como LIMITED, SAMPLEDou 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 é executado.

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

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

O modo DETAILED 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 é realizado em cada modo. Para avaliar rapidamente o tamanho ou o nível de fragmentação de uma tabela ou índice, use o modo LIMITED. É o mais rápido e não retorna uma linha para cada nível não folha na unidade de alocação IN_ROW_DATA do índice.

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

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 banco de dados ou o nome 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 função sys.dm_db_index_physical_stats interpreta NULL como um valor curinga especificando todos os bancos de dados ou todos os objetos.

Além disso, a função OBJECT_ID é processada antes que a função sys.dm_db_index_physical_stats 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 função OBJECT_ID retorne um valor NULL; 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 a seguir 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

Melhores práticas

Certifique-se sempre de que é devolvido um ID válido quando utiliza 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 função sys.dm_db_index_physical_stats. Os exemplos A e B a seguir demonstram uma maneira segura de especificar IDs de banco de dados e objeto.

Detetar fragmentação

A fragmentação ocorre através do processo de modificações de dados (instruçõesINSERT, UPDATEe DELETE) que são feitas em relação à tabela e, portanto, aos í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 varredura paralela de dados.

O nível de fragmentação de um índice ou heap é mostrado na coluna avg_fragmentation_in_percent. Para heaps, o valor representa a fragmentação da extensão da heap. Para índices, o valor representa a fragmentação lógica do índice. Ao contrário DBCC SHOWCONTIG, 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

Esta é a percentagem de páginas fora de ordem nas páginas de folha de um índice. Uma página fora de ordem é uma página para a qual a próxima página física alocada ao índice não é a página apontada pelo ponteiro próxima página na página folha atual.

Fragmentação da extensão

Esta é a percentagem de extensões fora de ordem nas páginas de folha de uma pilha. Uma extensão fora de ordem é aquela para a qual a extensão que contém a página atual para uma pilha 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 possível de zero para o 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 reconstruir, reorganizar ou recriar, 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 é fragmentado de uma forma que a fragmentação está afetando o desempenho da consulta, há três opções para reduzir a fragmentação:

Solte 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 plenitude pode ser configurado usando a opção FILLFACTOR em CREATE INDEX. As desvantagens neste método são que o índice está offline durante o ciclo de queda 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, consulte CREATE INDEX.
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 esta é uma operação online, o índice está disponível enquanto a instrução está em execução. A operação também pode ser interrompida sem perder o 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 para DBCC DBREINDEX, para reconstruir o índice online ou offline. Para obter mais informações, consulte ALTER INDEX (Transact-SQL).

A fragmentação por si só não é motivo suficiente para reorganizar ou reconstruir um índice. O principal efeito da fragmentação é que ela diminui a taxa de transferência de leitura antecipada da página durante as verificações de índice. Isso causa tempos de resposta mais lentos. Se a carga de trabalho de consulta em uma tabela ou índice fragmentado não envolver verificações, porque a carga de trabalho é principalmente pesquisas singleton, a remoção da fragmentação não poderá ter efeito.

Observação

A execução de DBCC SHRINKFILE ou DBCC SHRINKDATABASE pode introduzir fragmentação se um índice for parcial ou completamente movido durante a operação de redução. Portanto, se uma operação de redução deve ser executada, você deve fazê-lo antes que a fragmentação seja removida.

Reduzir a fragmentação em uma pilha

Para reduzir a fragmentação de extensão de uma pilha, crie um índice clusterizado na tabela e, em seguida, solte o índice. Isso redistribui os dados enquanto o índice clusterizado é criado. Isso também o torna o melhor possível, considerando a distribuição do espaço livre disponível no banco de dados. Quando o índice clusterizado é descartado para recriar a pilha, 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.

Atenção

Criar e soltar 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 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.

A reorganização de um índice clusterizado especificado compacta todas as colunas LOB contidas no índice clusterizado. A reorganização de um índice não clusterizado compacta todas as colunas LOB que são colunas 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 de espaço em disco

A coluna avg_page_space_used_in_percent indica a plenitude da página. Para obter o uso ideal de espaço em disco, esse valor deve estar 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. Portanto, para reduzir as divisões de página, o valor deve ser inferior a 100%. A reconstrução de um índice com a opção FILLFACTOR 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 fator de preenchimento para um índice. Além disso, ALTER INDEX REORGANIZE compactará um índice tentando preencher páginas para 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, uma reconstrução de índice deve ser executada.

Avaliar fragmentos de índice

Um fragmento é constituído por páginas de folhas fisicamente consecutivas no mesmo ficheiro para uma unidade de atribuição. Um índice tem pelo menos um fragmento. Os fragmentos máximos que um índice pode ter são iguais ao número de páginas no nível de folha do índice. Fragmentos maiores significam que menos E/S de disco é necessária para ler o mesmo número de páginas. Portanto, quanto maior o valor avg_fragment_size_in_pages, melhor o desempenho da varredura de intervalo. Os valores avg_fragment_size_in_pages e avg_fragmentation_in_percent são inversamente proporcionais entre si. Portanto, reconstruir ou reorganizar 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 permissão no objeto especificado dentro do banco de dados.
VIEW DATABASE STATE ou VIEW DATABASE PERFORMANCE STATE permissão (SQL Server 2022) para retornar informações sobre todos os objetos no banco de dados especificado, usando o curinga de objeto @object_id = NULL.
VIEW SERVER STATE ou VIEW SERVER PERFORMANCE STATE permissão (SQL Server 2022) para retornar informações sobre todos os bancos de dados, usando o curinga de banco de dados @database_id = NULL.

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

Negar VIEW DATABASE STATE não permite que todos os objetos no banco de dados sejam retornados, independentemente de quaisquer permissões de CONTROL 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 Transact-SQL neste artigo usam o banco de dados de exemplo ou , que você pode baixar da home page Microsoft SQL Server Samples and Community Projects.

Um. Retornar informações sobre uma tabela especificada

O exemplo a seguir retorna estatísticas de tamanho e fragmentação para todos os índices e partições da tabela Person.Address. O modo de verificação está definido como LIMITED para obter o melhor desempenho e para limitar as estatísticas que são retornadas. A execução dessa consulta exige, no mínimo, CONTROL permissão 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 uma pilha

O exemplo a seguir retorna todas as estatísticas para o dbo.DatabaseLog de pilha no banco de dados AdventureWorks2022. Como a tabela contém dados LOB, uma linha é retornada para a unidade de alocação de LOB_DATA, além da linha retornada para o IN_ROW_ALLOCATION_UNIT que está armazenando as páginas de dados da pilha. A execução dessa consulta exige, no mínimo, CONTROL permissão 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. Informações de retorno 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 NULL curinga para todos os parâmetros. A execução desta 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 reconstruir ou reorganizar índices

O exemplo a seguir reorganiza ou reconstrói automaticamente todas as partições em um banco de dados que têm uma fragmentação média superior a 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.

-- 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 a seguir mostra como exibir e comparar o número total de páginas com as páginas que são compactadas de linha e página. Essas informações podem ser usadas para determinar o benefício que a compactação está proporcionando 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. Usar sys.dm_db_index_physical_stats no modo SAMPLED

O exemplo a seguir mostra como SAMPLED modo 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 de 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);

Visualizações de gerenciamento dinâmico do sistema
Visualizações e funções de gerenciamento dinâmico relacionadas ao índice de (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)
Transact-SQL referência (Mecanismo de Banco de Dados)

Partilhar 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âmetros

Melhores práticas

Detetar fragmentação

Fragmentação lógica

Fragmentação da extensão

Reduzir a fragmentação em um índice

Reduzir a fragmentação em uma pilha

Compactar dados de objetos grandes

Avaliar o uso de espaço em disco

Avaliar fragmentos de índice

Limitações

Permissões

Exemplos

Um. Retornar informações sobre uma tabela especificada

B. Retornar informações sobre uma pilha

C. Informações de retorno para todos os bancos de dados

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

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

F. Usar sys.dm_db_index_physical_stats no modo SAMPLED

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

Comentários

Recursos adicionais

Partilhar 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âmetros

Melhores práticas

Detetar fragmentação

Fragmentação lógica

Fragmentação da extensão

Reduzir a fragmentação em um índice

Reduzir a fragmentação em uma pilha

Compactar dados de objetos grandes

Avaliar o uso de espaço em disco

Avaliar fragmentos de índice

Limitações

Permissões

Exemplos

Um. Retornar informações sobre uma tabela especificada

B. Retornar informações sobre uma pilha

C. Informações de retorno para todos os bancos de dados

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

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

F. Usar sys.dm_db_index_physical_stats no modo SAMPLED

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

Conteúdo relacionado

Comentários

Recursos adicionais