sys.dm_db_index_physical_stats (Transact-SQL)sys.dm_db_index_physical_stats (Transact-SQL)

APLICA-SE A: simSQL Server (a partir do 2008) simBanco de Dados SQL do Azure nãoSQL Data Warehouse do Azure nãoParallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Retorna informações de tamanho e fragmentação dos dados e índices da tabela ou exibição especificada no SQL ServerSQL Server.Returns size and fragmentation information for the data and indexes of the specified table or view in SQL ServerSQL Server. Para um índice, uma linha é retornada para cada nível da árvore B em cada partição.For an index, one row is returned for each level of the B-tree in each partition. Para um heap, uma linha é retornada para a unidade de alocação de IN_ROW_DATA de cada partição.For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. Para dados LOB (objeto grande), uma linha é retornada para a unidade de alocação de LOB_DATA de cada partição.For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. Se houver 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.If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition. Retorna informações sobre índices de columnstore otimizado de memória xVelocity.Does not return information about xVelocity memory optimized columnstore indexes.

Importante

Se você consultar db_index_physical_stats em uma instância de servidor que está hospedando um AlwaysOn réplica secundária legível, você pode encontrar um impedimento redo.If you query sys.dm_db_index_physical_stats on a server instance that is hosting an Always On readable secondary replica, you might encounter a REDO blocking issue. Isso ocorre porque essa exibição de gerenciamento dinâmico adquire um bloqueio IS na exibição ou tabela de usuário especificada, que pode bloquear solicitações por um thread REDO para um bloqueio X na exibição ou tabela de usuário.This is because this dynamic management view acquires an IS lock on the specified user table or view that can block requests by a REDO thread for an X lock on that user table or view.

db_index_physical_stats não retorna informações sobre índices com otimização de memória.sys.dm_db_index_physical_stats does not return information about memory-optimized indexes. Para obter informações sobre o uso de índice com otimização de memória, consulte sys.dm_db_xtp_index_stats (Transact-SQL).For information about memory-optimized index use, see sys.dm_db_xtp_index_stats (Transact-SQL).

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax


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

ArgumentosArguments

database_id | NULO | 0 | PADRÃOdatabase_id | NULL | 0 | DEFAULT
É a ID do banco de dados.Is the ID of the database. database_id está smallint.database_id is smallint. As entradas válidas são o número da ID de um banco de dados, NULL, 0 ou DEFAULT.Valid inputs are the ID number of a database, NULL, 0, or DEFAULT. O padrão é 0.The default is 0. NULL, 0 e DEFAULT são valores equivalentes neste contexto.NULL, 0, and DEFAULT are equivalent values in this context.

Especifique NULL para retornar informações de todos os bancos de dados na instância do SQL ServerSQL Server.Specify NULL to return information for all databases in the instance of SQL ServerSQL Server. Se você especificar NULL para database_id, você também deverá especificar NULL para object_id, index_id, e partition_number.If you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number.

A função interna DB_ID pode ser especificado.The built-in function DB_ID can be specified. Quando você usar DB_ID sem especificar um nome de banco de dados, o nível de compatibilidade do banco de dados atual deverá ser 90 ou mais.When using DB_ID without specifying a database name, the compatibility level of the current database must be 90 or greater.

object_id | NULO | 0 | PADRÃOobject_id | NULL | 0 | DEFAULT
É a ID do objeto da tabela ou exibição em que o índice está ativado.Is the object ID of the table or view the index is on. object_id é int.object_id is int.

As entradas válidas são o número da ID de uma tabela e de uma exibição, NULL, 0 ou DEFAULT.Valid inputs are the ID number of a table and view, NULL, 0, or DEFAULT. O padrão é 0.The default is 0. NULL, 0 e DEFAULT são valores equivalentes neste contexto.NULL, 0, and DEFAULT are equivalent values in this context. Como de SQL Server 2016 (13.x)SQL Server 2016 (13.x), as entradas válidas também incluem o nome de fila do service broker ou o nome de tabela interna da fila.As of SQL Server 2016 (13.x)SQL Server 2016 (13.x), valid inputs also include the service broker queue name or the queue internal table name. Quando os parâmetros padrão são aplicados (ou seja, todos os objetos, todos os índices, etc), informações de fragmentação para todas as filas são incluídos no conjunto de resultados.When default parameters are applied (i.e. all objects, all indexes, etc), fragmentation information for all queues are included in the result set.

Especifique NULL para retornar informações de todas as tabelas e exibições no banco de dados especificado.Specify NULL to return information for all tables and views in the specified database. Se você especificar NULL para object_id, você também deverá especificar NULL para index_id e partition_number.If you specify NULL for object_id, you must also specify NULL for index_id and partition_number.

index_id | 0 | NULO | -1 | PADRÃOindex_id | 0 | NULL | -1 | DEFAULT
É a ID do índice.Is the ID of the index. index_id está int. As entradas válidas são o número de identificação de um índice, 0 se object_id for um heap, NULL, -1 ou padrão.index_id is int. Valid inputs are the ID number of an index, 0 if object_id is a heap, NULL, -1, or DEFAULT. O padrão é -1.The default is -1. NULL, -1 e DEFAULT são valores equivalentes neste contexto.NULL, -1, and DEFAULT are equivalent values in this context.

Especifique NULL para retornar informações de todos os índices de uma tabela base ou exibição.Specify NULL to return information for all indexes for a base table or view. Se você especificar NULL para index_id, você também deverá especificar NULL para partition_number.If you specify NULL for index_id, you must also specify NULL for partition_number.

partition_number | NULO | 0 | PADRÃOpartition_number | NULL | 0 | DEFAULT
É o número da partição no objeto.Is the partition number in the object. partition_number está int. As entradas válidas são o partion_number de um índice ou heap, NULL, 0 ou DEFAULT.partition_number is int. Valid inputs are the partion_number of an index or heap, NULL, 0, or DEFAULT. O padrão é 0.The default is 0. NULL, 0 e DEFAULT são valores equivalentes neste contexto.NULL, 0, and DEFAULT are equivalent values in this context.

Especifique NULL para retornar informações de todas as partições do objeto proprietário.Specify NULL to return information for all partitions of the owning object.

partition_number é baseado em 1.partition_number is 1-based. Um heap ou índice não particionado tiver número_da_partição definido como 1.A nonpartitioned index or heap has partition_number set to 1.

modo | NULO | PADRÃOmode | NULL | DEFAULT
É o nome do modo.Is the name of the mode. modo Especifica o nível de verificação que é usado para obter estatísticas.mode specifies the scan level that is used to obtain statistics. modo está sysname.mode is sysname. Entradas válidas são DEFAULT, NULL, LIMITED, SAMPLED ou DETAILED.Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. O padrão (NULL) é LIMITED.The default (NULL) is LIMITED.

Tabela retornadaTable Returned

Nome da colunaColumn name Tipo de dadosData type DescriptionDescription
database_iddatabase_id smallintsmallint Identificação do banco de dados da tabela ou exibição.Database ID of the table or view.
object_idobject_id intint Identificação de objeto da tabela ou exibição na qual o índice se encontra.Object ID of the table or view that the index is on.
index_idindex_id intint Identificação de um índice.Index ID of an index.

0 = Heap.0 = Heap.
partition_numberpartition_number intint Número de partição de base 1 no objeto proprietário; uma tabela, exibição ou índice.1-based partition number within the owning object; a table, view, or index.

1 = Índice ou heap não particionado.1 = Nonpartitioned index or heap.
index_type_descindex_type_desc nvarchar(60)nvarchar(60) Descrição do tipo de índice:Description of the index type:

HEAPHEAP

CLUSTERED INDEXCLUSTERED INDEX

NONCLUSTERED INDEXNONCLUSTERED INDEX

PRIMARY XML INDEXPRIMARY XML INDEX

SPATIAL INDEXSPATIAL INDEX

XML INDEXXML INDEX

ÍNDICE de mapeamento do COLUMNSTORE (interno)COLUMNSTORE MAPPING INDEX (internal)

ÍNDICE de DELETEBUFFER COLUMNSTORE (interno)COLUMNSTORE DELETEBUFFER INDEX (internal)

ÍNDICE de DELETEBITMAP COLUMNSTORE (interno)COLUMNSTORE DELETEBITMAP INDEX (internal)
hobt_idhobt_id bigintbigint Heap ou a ID de árvore B do índice ou partição.Heap or B-Tree ID of the index or partition.

Além de retornar o hobt_id de índices definidos pelo usuário, isso também retorna o hobt_id dos índices columnstore interno.Besides returning the hobt_id of user-defined indexes, this also returns the hobt_id of the internal columnstore indexes.
alloc_unit_type_descalloc_unit_type_desc nvarchar(60)nvarchar(60) Descrição do tipo de unidade de alocação:Description of the allocation unit type:

IN_ROW_DATAIN_ROW_DATA

LOB_DATALOB_DATA

ROW_OVERFLOW_DATAROW_OVERFLOW_DATA

Unidade de alocação LOB_DATA contém os dados que são armazenados em colunas do tipo texto, ntext, imagem, varchar (max), nvarchar (max), varbinary (max), e xml.The LOB_DATA allocation unit contains the data that is stored in columns of type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml. Para obter mais informações, veja Tipos de dados (Transact-SQL).For more information, see Data Types (Transact-SQL).

Unidade de alocação ROW_OVERFLOW_DATA contém os dados que são armazenados em colunas do tipo varchar (n), nvarchar (n), varbinary (n), e SQL _ variante que foi colocada fora da linha.The ROW_OVERFLOW_DATA allocation unit contains the data that is stored in columns of type varchar(n), nvarchar(n), varbinary(n), and sql_variant that have been pushed off-row.
index_depthindex_depth tinyinttinyint Número de níveis de índice.Number of index levels.

1 = Heap ou unidade de alocação LOB_DATA ou ROW_OVERFLOW_DATA.1 = Heap, or LOB_DATA or ROW_OVERFLOW_DATA allocation unit.
index_levelindex_level tinyinttinyint Nível atual do índice.Current level of the index.

0 para níveis folha de índice, heaps e unidades de alocação LOB_DATA ou ROW_OVERFLOW_DATA.0 for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

Maior que 0 para níveis de índice nonleaf.Greater than 0 for nonleaf index levels. index_level será o mais alto no nível raiz de um índice.index_level will be the highest at the root level of an index.

Os níveis não folha dos índices só são processados quando modo = DETAILED.The nonleaf levels of indexes are only processed when mode = DETAILED.
avg_fragmentation_in_percentavg_fragmentation_in_percent floatfloat Fragmentação lógica para índices ou fragmentação de extensão para heaps na unidade de alocação IN_ROW_DATA.Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

O valor é medido como uma porcentagem e leva em consideração vários arquivos.The value is measured as a percentage and takes into account multiple files. Para definições de fragmentação lógica e de extensão, consulte Comentários.For definitions of logical and extent fragmentation, see Remarks.

0 para unidades de alocação LOB_DATA e ROW_OVERFLOW_DATA.0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

NULL para heaps quando modo = SAMPLED.NULL for heaps when mode = SAMPLED.
fragment_countfragment_count bigintbigint Número de fragmentos no nível folha de uma unidade de alocação IN_ROW_DATA.Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. Para obter mais informações sobre fragmentos, consulte Comentários.For more information about fragments, see Remarks.

NULL para níveis não folha de um índice e unidades de alocação LOB_DATA ou ROW_OVERFLOW_DATA.NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

NULL para heaps quando modo = SAMPLED.NULL for heaps when mode = SAMPLED.
avg_fragment_size_in_pagesavg_fragment_size_in_pages floatfloat Número médio de páginas em um fragmento no nível folha de uma unidade de alocação IN_ROW_DATA.Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit.

NULL para níveis não folha de um índice e unidades de alocação LOB_DATA ou ROW_OVERFLOW_DATA.NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

NULL para heaps quando modo = SAMPLED.NULL for heaps when mode = SAMPLED.
page_countpage_count bigintbigint Número total de páginas de índice ou dados.Total number of index or data pages.

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.For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

Para um heap, o número total de páginas de dados na unidade de alocação IN_ROW_DATA.For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

Para as unidades de alocação LOB_DATA ou ROW_OVERFLOW_DATA, o número total de páginas na unidade de alocação.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.
avg_page_space_used_in_percentavg_page_space_used_in_percent floatfloat Porcentagem média de espaço de armazenamento de dados disponível usada em todas as páginas.Average percentage of available data storage space used in all pages.

Para um índice, a média se aplica ao nível atual da árvore b na unidade de alocação IN_ROW_DATA.For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

Para um heap, a média de todas as páginas de dados na unidade de alocação IN_ROW_DATA.For a heap, the average of all data pages in the IN_ROW_DATA allocation unit.

Para as unidades de alocação LOB_DATA ou ROW_OVERFLOW DATA, a média de todas as páginas na unidade de alocação.For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit.

NULL quando modo = LIMITED.NULL when mode = LIMITED.
record_countrecord_count bigintbigint Número total de registros.Total number of records.

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.For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

Para um heap, o número total de registros na unidade de alocação IN_ROW_DATA.For a heap, the total number of records in the IN_ROW_DATA allocation unit.

Observação: para um heap, o número de registros retornados por essa função pode não corresponder ao número de linhas que são retornados ao executar um SELECT COUNT (*) contra o heap.Note: For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. Isso porque uma linha pode conter vários registros.This is because a row may contain multiple records. 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.For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. Da mesma forma, a maior parte das linhas de LOB grandes é dividida em vários registros no armazenamento LOB_DATA.Also, most large LOB rows are split into multiple records in LOB_DATA storage.

Para as unidades de alocação LOB_DATA ou ROW_OVERFLOW_DATA, o número total de registros na unidade de alocação completa.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit.

NULL quando modo = LIMITED.NULL when mode = LIMITED.
ghost_record_countghost_record_count bigintbigint Número de registros fantasmas prontos para remoção pela tarefa de limpeza fantasma na unidade de alocação.Number of ghost records ready for removal by the ghost cleanup task in the allocation unit.

0 para níveis não folha de um índice na unidade de alocação de IN_ROW_DATA.0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL quando modo = LIMITED.NULL when mode = LIMITED.
version_ghost_record_countversion_ghost_record_count bigintbigint Número de registros fantasmas retidos por uma transação de isolamento de instantâneo pendente em uma unidade de alocação.Number of ghost records retained by an outstanding snapshot isolation transaction in an allocation unit.

0 para níveis não folha de um índice na unidade de alocação de IN_ROW_DATA.0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL quando modo = LIMITED.NULL when mode = LIMITED.
min_record_size_in_bytesmin_record_size_in_bytes intint Tamanho de registro mínimo em bytes.Minimum record size in bytes.

Para um índice, o tamanho de registro mínimo aplica-se ao nível atual da árvore b na unidade de alocação IN_ROW_DATA.For an index, minimum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

Para um heap, o tamanho de registro mínimo na unidade de alocação IN_ROW_DATA.For a heap, the minimum record size in the IN_ROW_DATA allocation unit.

Para as unidades de alocação LOB_DATA ou ROW_OVERFLOW_DATA, o tamanho de registro mínimo na unidade de alocação completa.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the minimum record size in the complete allocation unit.

NULL quando modo = LIMITED.NULL when mode = LIMITED.
max_record_size_in_bytesmax_record_size_in_bytes intint Tamanho de registro máximo em bytes.Maximum record size in bytes.

Para um índice, o tamanho de registro máximo aplica-se ao nível atual da árvore b na unidade de alocação IN_ROW_DATA.For an index, the maximum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

Para um heap, o tamanho de registro máximo na unidade de alocação IN_ROW_DATA.For a heap, the maximum record size in the IN_ROW_DATA allocation unit.

Para as unidades de alocação LOB_DATA ou ROW_OVERFLOW_DATA, o tamanho de registro máximo na unidade de alocação completa.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit.

NULL quando modo = LIMITED.NULL when mode = LIMITED.
avg_record_size_in_bytesavg_record_size_in_bytes floatfloat Tamanho de registro médio em bytes.Average record size in bytes.

Para um índice, o tamanho de registro médio aplica-se ao nível atual da árvore b na unidade de alocação IN_ROW_DATA.For an index, the average record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

Para um heap, o tamanho de registro médio na unidade de alocação IN_ROW_DATA.For a heap, the average record size in the IN_ROW_DATA allocation unit.

Para as unidades de alocação LOB_DATA ou ROW_OVERFLOW_DATA, o tamanho de registro médio na unidade de alocação completa.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the average record size in the complete allocation unit.

NULL quando modo = LIMITED.NULL when mode = LIMITED.
forwarded_record_countforwarded_record_count bigintbigint Número de registros em um heap com ponteiros encaminhados a outro local de dados.Number of records in a heap that have forward pointers to another data location. (Esse estado ocorre durante uma atualização, quando não há espaço suficiente para armazenar a nova linha no local original.)(This state occurs during an update, when there is not enough room to store the new row in the original location.)

NULL para qualquer unidade de alocação diferente das unidades de alocação IN_ROW_DATA de um heap.NULL for any allocation unit other than the IN_ROW_DATA allocation units for a heap.

NULL para heaps quando modo = LIMITED.NULL for heaps when mode = LIMITED.
compressed_page_countcompressed_page_count bigintbigint O número total de páginas compactadas.The number of compressed pages.

Para heaps, as páginas alocadas recentemente não são compactadas com PAGE.For heaps, newly allocated pages are not PAGE compressed. Um heap é compactado com PAGE em duas condições especiais: quando os dados são importados em massa ou quando um heap é reconstruído.A heap is PAGE compressed under two special conditions: when data is bulk imported or when a heap is rebuilt. Operações DML típicas que causam alocações de página não terão compactação PAGE.Typical DML operations that cause page allocations will not be PAGE compressed. Reconstrua um heap quando o valor compressed_page_count aumentar ultrapassando o limite desejado.Rebuild a heap when the compressed_page_count value grows larger than the threshold you want.

Para tabelas que têm um índice clusterizado, o valor compressed_page_count indica a eficiência da compactação PAGE.For tables that have a clustered index, the compressed_page_count value indicates the effectiveness of PAGE compression.
hobt_idhobt_id BIGINTbigint Aplica-se a: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) até a versão atual), Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Banco de dados SQL do AzureAzure SQL Database.

Somente índices columnstore, essa é a ID para um conjunto de linhas que rastreia dados de columnstore interno para uma partição.For columnstore indexes only, this is the ID for a rowset that tracks internal columnstore data for a partition. Os conjuntos de linhas são armazenadas como dados heaps ou binário árvores.The rowsets are stored as data heaps or binary trees. Eles têm a mesma ID de índice que o índice de columnstore do pai.They have the same index ID as the parent columnstore index. Para obter mais informações, consulte sys.internal_partitions (Transact-SQL).For more information, see sys.internal_partitions (Transact-SQL).

NULO seNULL if
column_store_delete_buffer_statecolumn_store_delete_buffer_state TINYINTtinyint Aplica-se a: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) até a versão atual), Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Banco de dados SQL do AzureAzure SQL Database.

0 = NOT_APPLICABLE0 = NOT_APPLICABLE

1 = OPEN1 = OPEN

2 = DRENAGEM2 = DRAINING

3 = LIBERANDO3 = FLUSHING

4 = DESATIVANDO4 = RETIRING

5 = PRONTO5 = READY
column_store_delete_buff_state_desccolumn_store_delete_buff_state_desc Aplica-se a: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) até a versão atual), Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Banco de dados SQL do AzureAzure SQL Database.

NÃO é válido-o índice de pai não é um índice columnstore.NOT VALID –the parent index is not a columnstore index.

Abra – Excluidores e scanners de usá-lo.OPEN – deleters and scanners use this.

DRENAGEM – Excluidores são drenagem mas scanners ainda usá-lo.DRAINING – deleters are draining out but scanners still use it.

LIBERANDO – buffer é fechada e linhas no buffer de estão sendo gravadas no bitmap de exclusão.FLUSHING – buffer is closed and rows in the buffer are being written to the delete bitmap.

DESATIVANDO – linhas no buffer de exclusão fechado ter sido escritos para o bitmap de exclusão, mas o buffer não foi truncado porque os scanners ainda estão usando.RETIRING – rows in the closed delete buffer have been written to the delete bitmap, but the buffer has not been truncated because scanners are still using it. Novo scanners não precisam usar o buffer obsoletos porque o buffer aberto é suficiente.New scanners don’t need to use the retiring buffer because the open buffer is enough.

PRONTO – esse buffer de exclusão está pronto para uso.READY – This delete buffer is ready for use.

ComentáriosRemarks

A função de gerenciamento dinâmico sys.dm_db_index_physical_stats substitui a instrução DBCC SHOWCONTIG.The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

Modos de exameScanning Modes

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.The mode in which the function is executed determines the level of scanning performed to obtain the statistical data that is used by the function. modo é especificado como LIMITED, SAMPLED ou DETAILED.mode is specified as LIMITED, SAMPLED, or 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.The function traverses the page chains for the allocation units that make up the specified partitions of the table or index. db_index_physical_stats requer apenas um tentativa compartilhada (IS) bloqueio de tabela, independentemente do modo que ele é executado no.sys.dm_db_index_physical_stats requires only an Intent-Shared (IS) table lock, regardless of the mode that it runs in.

O modo LIMITED é o mais rápido e examina o menor número de páginas.The LIMITED mode is the fastest mode and scans the smallest number of pages. 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.For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. 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.For a heap, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode.

Com o modo LIMITED, compressed_page_count é NULL porque o Mecanismo de Banco de DadosDatabase Engine só examina as páginas não folha da árvore B e as páginas IAM e PFS do heap.With LIMITED mode, compressed_page_count is NULL because the Mecanismo de Banco de DadosDatabase Engine only scans non-leaf pages of the B-tree and the IAM and PFS pages of the heap. Use o modo SAMPLED para obter um valor estimado de compressed_page_count e use modo DETAILED para obter o valor real de compressed_page_count.Use SAMPLED mode to get an estimated value for compressed_page_count, and use DETAILED mode to get the actual value for 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.The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. Os resultados em modo SAMPLED devem ser considerados aproximados.Results in SAMPLED mode should be regarded as approximate. Se o índice ou heap tiver menos que 10.000 páginas, o modo DETAILED será usado em vez do SAMPLED.If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.

O modo DETAILED examina todas as páginas e retorna todas as estatísticas.The DETAILED mode scans all pages and returns all statistics.

Os modos são progressivamente mais lentos de LIMITED para DETAILED, porque mais trabalho é executado em cada modo.The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. Para medir rapidamente o tamanho ou o nível de fragmentação de uma tabela ou índice, use o modo LIMITED.To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. Ele é o mais rápido e não retornará uma linha para cada nível não folha na unidade de alocação IN_ROW_DATA do índice.It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA allocation unit of the index.

Usando funções de sistema para especificar valores de parâmetroUsing System Functions to Specify Parameter Values

Você pode usar o Transact-SQLTransact-SQL funções DB_ID e OBJECT_ID para especificar um valor para o database_id e object_id parâmetros.You can use the Transact-SQLTransact-SQL functions DB_ID and OBJECT_ID to specify a value for the database_id and object_id parameters. No entanto, passar valores que não sejam válidos a essas funções pode causar resultados não intencionais.However, passing values that are not valid to these functions may cause unintended results. Por exemplo, se o banco de dados ou nome de objeto não puder ser encontrado por não existir ou por estar escrito incorretamente, ambas as funções retornarão NULL.For example, if the database or object name cannot be found because they do not exist or are spelled incorrectly, both functions will return NULL. A função sys.dm_db_index_physical_stats interpreta o NULL como um valor curinga que especifica todos os bancos de dados ou todos os objetos.The sys.dm_db_index_physical_stats function interprets NULL as a wildcard value specifying all databases or all objects.

Além disso, a função OBJECT_ID é processada antes da função db_index_physical_stats é chamada e, portanto, é avaliada no contexto do banco de dados atual, não o banco de dados especificado na database_id.Additionally, the OBJECT_ID function is processed before the sys.dm_db_index_physical_stats function is called and is therefore evaluated in the context of the current database, not the database specified in 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 poderá ser exibida.This behavior may cause the OBJECT_ID function to return a NULL value; or, if the object name exists in both the current database context and the specified database, an error message may be returned. Os exemplos seguintes demonstram esses resultados não intencionais.The following examples demonstrate these unintended results.

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'AdventureWorks'), 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 AdventureWorks2012;  
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  

Prática recomendadaBest Practice

Sempre verifique se uma ID válida é retornada ao usar DB_ID ou OBJECT_ID.Always make sure that a valid ID is returned when you use DB_ID or OBJECT_ID. Por exemplo, ao usar OBJECT_ID, especifique um nome de três partes, como OBJECT_ID(N'AdventureWorks2012.Person.Address'), ou o valor retornado pelas funções antes de usá-los na função db_index_physical_stats de teste.For example, when you use OBJECT_ID, specify a three-part name such as OBJECT_ID(N'AdventureWorks2012.Person.Address'), or test the value returned by the functions before you use them in the sys.dm_db_index_physical_stats function. Os exemplos A e B a seguir demonstram um modo seguro de especificar identificações de banco de dados e objeto.Examples A and B that follow demonstrate a safe way to specify database and object IDs.

Detectando a fragmentaçãoDetecting Fragmentation

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.Fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and, therefore, to the indexes defined on the table. Como essas modificações não são distribuídas uniformemente entre as linhas da tabela e os índices, o preenchimento de cada página pode variar com o tempo.Because these modifications are not ordinarily distributed equally among the rows of the table and indexes, the fullness of each page can vary over time. 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.For queries that scan part or all of the indexes of a table, this kind of fragmentation can cause additional page reads. Isso impede o exame paralelo de dados.This hinders parallel scanning of data.

O nível de fragmentação de um índice ou heap é mostrado na coluna avg_fragmentation_in_percent.The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. Para heaps, o valor representa a fragmentação de extensão do heap.For heaps, the value represents the extent fragmentation of the heap. Para índices, o valor representa a fragmentação lógica do índice.For indexes, the value represents the logical fragmentation of the index. Ao contrário de DBCC SHOWCONTIG, os algoritmos de cálculo de fragmentação em ambos os casos consideram o armazenamento que se estende por vários arquivos e, por isso, são precisos.Unlike DBCC SHOWCONTIG, the fragmentation calculation algorithms in both cases consider storage that spans multiple files and, therefore, are accurate.

Fragmentação lógicaLogical Fragmentation

É a porcentagem de páginas com problema nas páginas de folha de um índice.This is the percentage of out-of-order pages in the leaf pages of an index. Uma página fora de ordem é aquela cuja próxima página física alocada ao índice não é a página apontada pelo ponteiro de próxima página na página de folha atual.An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.

Fragmentação de extensãoExtent Fragmentation

É a porcentagem de extensões com problema nas páginas de folha de um heap.This is the percentage of out-of-order extents in the leaf pages of a heap. Uma extensão com problema é 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 depois da extensão que contém a página anterior.An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

O valor de avg_fragmentation_in_percent deve ser o mais próximo possível de zero para um máximo desempenho.The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. Porém, valores de 0% a 10% podem ser aceitáveis.However, values from 0 percent through 10 percent may be acceptable. Podem ser usados todos os métodos de redução de fragmentação, como reconstruir, reorganizar ou recriar, para reduzir esses valores.All methods of reducing fragmentation, such as rebuilding, reorganizing, or re-creating, can be used to reduce these values. Para obter mais informações sobre como analisar o grau de fragmentação em um índice, consulte reorganizar e recompilar índices.For more information about how to analyze the degree of fragmentation in an index, see Reorganize and Rebuild Indexes.

Reduzindo a fragmentação em um índiceReducing Fragmentation in an Index

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:When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:

  • Descartar e recriar o índice clusterizado.Drop and re-create the clustered index.

    Recriar um índice clusterizado redistribui os dados e resulta em páginas de dados completas.Re-creating a clustered index redistributes the data and results in full data pages. O nível de preenchimento pode ser configurado usando a opção FILLFACTOR em CREATE INDEX.The level of fullness can be configured by using the FILLFACTOR option in 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.The drawbacks in this method are that the index is offline during the drop and re-create cycle, and that the operation is atomic. Se a criação de índice for suspensa, o índice não será recriado.If the index creation is interrupted, the index is not re-created. Para obter mais informações, consulte CREATE INDEX (Transact-SQL).For more information, see CREATE INDEX (Transact-SQL).

  • Usar 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.Use ALTER INDEX REORGANIZE, the replacement for DBCC INDEXDEFRAG, to reorder the leaf level pages of the index in a logical order. Como essa operação é online, o índice permanecerá disponível enquanto a instrução estiver sendo executada.Because this is an online operation, the index is available while the statement is running. A operação também pode ser interrompida sem perda do trabalho já concluído.The operation can also be interrupted without losing work already completed. A desvantagem desse método é que ele não reorganiza muito bem os dados como uma operação de reconstrução de índice e não atualiza as estatísticas.The drawback in this method is that it does not do as good a job of reorganizing the data as an index rebuild operation, and it does not update statistics.

  • Usar ALTER INDEX REBUILD, a substituição de DBCC DBREINDEX, para reconstruir o índice online ou offline.Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline. Para obter mais informações, consulte ALTER INDEX (Transact-SQL).For more information, see ALTER INDEX (Transact-SQL).

    A fragmentação sozinha não é uma razão suficiente para reorganizar ou reconstruir um índice.Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. 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.The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. O resultado é tempos de resposta mais lentos.This causes slower response times. Se a carga de trabalho da consulta em uma tabela ou índice fragmentado não envolver exames porque a carga de trabalho é composta por pesquisas singleton, a remoção da fragmentação poderá não ter efeito algum.If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect. Para obter mais informações, consulte este site da Microsoft.For more information, see this Microsoft Web site.

Observação

Executar DBCC SHRINKFILE ou DBCC SHRINKDATABASE poderá apresentar fragmentação se um índice for movido parcial ou completamente durante a operação de redução.Running DBCC SHRINKFILE or DBCC SHRINKDATABASE may introduce fragmentation if an index is partly or completely moved during the shrink operation. Assim, se for necessário executar uma operação de redução, você deverá fazer isso antes da remoção da fragmentação.Therefore, if a shrink operation must be performed, you should do it before fragmentation is removed.

Reduzindo fragmentação em um heapReducing Fragmentation in a Heap

Para reduzir a extensão da fragmentação de um heap, crie um índice clusterizado na tabela e descarte o índice.To reduce the extent fragmentation of a heap, create a clustered index on the table and then drop the index. Isso redistribui os dados enquanto o índice clusterizado é criado.This redistributes the data while the clustered index is created. 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.This also makes it as optimal as possible, considering the distribution of free space available in the database. Quando o índice clusterizado é descartado para a recriação de um heap, os dados não são movidos e permanecem no mesmo lugar.When the clustered index is then dropped to re-create the heap, the data is not moved and remains optimally in position. Para obter informações sobre como executar essas operações, consulte CREATE INDEX e DROP INDEX.For information about how to perform these operations, see CREATE INDEX and DROP INDEX.

Cuidado

Criar e descartar um índice clusterizado em uma tabela recria todos os índices não clusterizados nessa tabela duas vezes.Creating and dropping a clustered index on a table, rebuilds all non-clustered indexes on that table twice.

Compactando dados de objetos grandesCompacting Large Object Data

Por padrão, a instrução ALTER INDEX REORGANIZE compacta páginas que contêm dados LOB (objetos grandes).By default, the ALTER INDEX REORGANIZE statement compacts pages that contain large object (LOB) data. Como as páginas LOB não são desalocadas quando vazias, a compactação desses dados poderá melhorar o espaço em disco se vários dados LOB tiverem sido excluídos ou se uma coluna LOB foi descartada.Because LOB pages are not deallocated when empty, compacting this data can improve disk space use if lots of LOB data have been deleted, or a LOB column is dropped.

Reorganizar um índice clusterizado especificado compacta todas as colunas LOB contidas no índice clusterizado.Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Reorganizar um índice não clusterizado compacta todas as colunas LOB não-chave (incluídas) no índice.Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index. Quando ALL é especificado na instrução, todos os índices associados à tabela ou exibição especificada são reorganizados.When ALL is specified in the statement, all indexes that are associated with the specified table or view are reorganized. 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.Additionally, all LOB columns that are associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.

Avaliando o uso do espaço em discoEvaluating Disk Space Use

A coluna avg_page_space_used_in_percent indica que a página está cheia.The avg_page_space_used_in_percent column indicates page fullness. Para se obter um ótimo uso do espaço em disco, esse valor deverá estar perto de 100% para um índice que não terá muitas inserções aleatórias.To achieve optimal disk space use, this value should be close to 100 percent for an index that will not have many random inserts. Entretanto, um índice que tem muitas inserções aleatórias e páginas muito cheias terá um número maior de divisões de página.However, an index that has many random inserts and has very full pages will have an increased number of page splits. Isso causa mais fragmentação.This causes more fragmentation. Por isso, para reduzir as divisões de página, o valor deve ser menor que 100%.Therefore, in order to reduce page splits, the value should be less than 100 percent. 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.Rebuilding an index with the FILLFACTOR option specified allows the page fullness to be changed to fit the query pattern on the index. Para obter mais informações sobre o fator de preenchimento, consulte especificar o fator de preenchimento para um índice.For more information about fill factor, see Specify Fill Factor for an Index. Além disso, ALTER INDEX REORGANIZE compactará um índice tentando preencher páginas para o FILLFACTOR especificado pela última vez.Also, ALTER INDEX REORGANIZE will compact an index by trying to fill pages to the FILLFACTOR that was last specified. Isso aumenta o valor em avg_space_used_in_percent.This increases the value in avg_space_used_in_percent. Observe que ALTER INDEX REORGANIZE não pode reduzir o preenchimento da página.Note that ALTER INDEX REORGANIZE cannot reduce page fullness. Em vez disso, o índice deverá ser recriado.Instead, an index rebuild must be performed.

Avaliando fragmentos de índiceEvaluating Index Fragments

Um fragmento é composto de páginas de folha fisicamente consecutivas no mesmo arquivo de uma unidade de alocação.A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. Um índice tem pelo menos um fragmento.An index has at least one fragment. O máximo de fragmentos que um índice pode ter é igual ao número de páginas no nível folha do índice.The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. Fragmentos maiores indicam que menos E/S de disco é necessária para ler o mesmo número de páginas.Larger fragments mean that less disk I/O is required to read the same number of pages. Por isso, quanto maior o valor avg_fragment_size_in_pages, melhor o desempenho de exame de intervalo.Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance. Os valores avg_fragment_size_in_pages e avg_fragmentation_in_percent são inversamente proporcionais entre si.The avg_fragment_size_in_pages and avg_fragmentation_in_percent values are inversely proportional to each other. 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.Therefore, rebuilding or reorganizing an index should reduce the amount of fragmentation and increase the fragment size.

Limitações e restriçõesLimitations and Restrictions

Não retorna dados para índices columnstore clusterizados.Does not return data for clustered columnstore indexes.

PermissõesPermissions

Requer as seguintes permissões:Requires the following permissions:

  • Permissão CONTROL no objeto especificado no banco de dados.CONTROL permission on the specified object within the database.

  • A permissão VIEW DATABASE STATE para retornar informações sobre todos os objetos de banco de dados especificado, usando o curinga de objeto @object_id= NULL.VIEW DATABASE STATE permission to return information about all objects within the specified database, by using the object wildcard @object_id=NULL.

  • A permissão VIEW SERVER STATE para retornar informações sobre todos os bancos de dados, usando o curinga de banco de dados @database_id = NULL.VIEW SERVER STATE permission to return information about all databases, by using the database wildcard @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.Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.

    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.Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. Além disso, quando o curinga de banco de dados @database_id= NULL for especificado, o banco de dados é omitido.Also, when the database wildcard @database_id=NULL is specified, the database is omitted.

    Para obter mais informações, consulte funções e exibições de gerenciamento dinâmico (Transact-SQL).For more information, see Dynamic Management Views and Functions (Transact-SQL).

ExemplosExamples

A.A. Retornando informações sobre uma tabela especificadaReturning information about a specified table

O exemplo a seguir retorna as estatísticas de tamanho e fragmentação de todos os índices e partições da tabela Person.Address.The following example returns size and fragmentation statistics for all indexes and partitions of the Person.Address table. O modo de exame é definido como 'LIMITED' para oferecer melhor desempenho e limitar as estatísticas retornadas.The scan mode is set to 'LIMITED' for best performance and to limit the statistics that are returned. A execução dessa consulta requer, no mínimo, a permissão CONTROL na tabela Person.Address.Executing this query requires, at a minimum, CONTROL permission on the Person.Address table.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  

SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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.B. Retornando informações sobre um heapReturning information about a heap

O exemplo a seguir retorna todas as estatísticas do heap dbo.DatabaseLog no banco de dados AdventureWorks2012AdventureWorks2012.The following example returns all statistics for the heap dbo.DatabaseLog in the AdventureWorks2012AdventureWorks2012 database. 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.Because the table contains LOB data, a row is returned for the LOB_DATA allocation unit in addition to the row returned for the IN_ROW_ALLOCATION_UNIT that is storing the data pages of the heap. A execução dessa consulta requer, no mínimo, a permissão CONTROL na tabela dbo.DatabaseLog.Executing this query requires, at a minimum, CONTROL permission on the dbo.DatabaseLog table.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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.C. Retornando informações de todos os bancos de dadosReturning information for all databases

O exemplo a seguir retorna todas as estatísticas de todas as tabelas e índices na instância do SQL ServerSQL Server através da especificação do curinga NULL para todos os parâmetros.The following example returns all statistics for all tables and indexes within the instance of SQL ServerSQL Server by specifying the wildcard NULL for all parameters. A execução desta consulta requer a permissão VIEW SERVER STATE.Executing this query requires the VIEW SERVER STATE permission.

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

D.D. Usando sys.dm_db_index_physical_stats em um script para reconstruir ou reorganizar índicesUsing sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

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%.The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. A execução desta consulta requer a permissão VIEW DATABASE STATE.Executing this query requires the VIEW DATABASE STATE permission. Este exemplo especifica DB_ID como o primeiro parâmetro sem especificar um nome de banco de dados.This example specifies DB_ID as the first parameter without specifying a database name. Um erro será gerado se o banco de dados atual tiver um nível de compatibilidade de 80 ou menos.An error will be generated if the current database has a compatibility level of 80 or lower. Para resolver o erro, substitua DB_ID() por um nome de banco de dados válido.To resolve the error, replace DB_ID() with a valid database name. Para obter mais informações sobre níveis de compatibilidade do banco de dados, consulte nível de compatibilidade de ALTER DATABASE (Transact-SQL).For more information about database compatibility levels, see ALTER DATABASE Compatibility Level (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  
        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.E. Usando sys.dm_db_index_physical_stats para mostrar o número de páginas compactadas por páginaUsing sys.dm_db_index_physical_stats to show the number of page-compressed pages

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.The following example shows how to display and compare the total number of pages against the pages that are row and page compressed. Estas informações podem ser usadas para determinar o benefício que a compactação está fornecendo para um índice ou tabela.This information can be used to determine the benefit that compression is providing for an index or table.

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  
JOIN sys.objects o on o.object_id = ips.object_id  
ORDER BY record_count DESC;  

F.F. Usando sys.dm_db_index_physical_stats em modo SAMPLEDUsing sys.dm_db_index_physical_stats in SAMPLED mode

O exemplo a seguir mostra como o modo SAMPLED retorna um aproximado que é diferente dos resultados do modo DETAILED.The following example shows how SAMPLED mode returns an approximate that is different than the DETAILED mode results.

CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);  
GO  
BEGIN TRAN  
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.G. Consultando filas do service broker para a fragmentação de índiceQuerying service broker queues for index fragmentation

||
|-|
|Aplica-se a: do SQL Server 2016 (13.x)SQL Server 2016 (13.x) ao SQL ServerSQL Server.Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL ServerSQL Server.|

Os exemplos a seguir mostra como consultar filas de agente do servidor para fragmentação.The following examples shows how to query server broker queues for fragmentation.

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

Consulte tambémSee Also

Exibições e funções de gerenciamento dinâmico (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
Exibições e funções de gerenciamento dinâmico relacionadas ao índice (Transact-SQL) Index Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL) sys.dm_db_index_operational_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL) sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL) sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL) sys.allocation_units (Transact-SQL)
Exibições do sistema (Transact-SQL)System Views (Transact-SQL)