Partilhar via

sys.dm_db_index_operational_stats (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Devolve as estatísticas de acesso, bloqueio e latching de dados de nível inferior para cada partição de uma tabela ou índice numa base de dados.

Transact-SQL convenções de sintaxe

Sintaxe

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

Argumentos

database_id | NULO | 0 | INADIMPLÊNCIA

ID da base de dados. database_id é smallint. As entradas válidas são o número de ID de um banco de dados, NULL, 0 ou DEFAULT. O padrão é 0. NULL, 0 e DEFAULT são valores equivalentes neste contexto.

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

A função interna DB_ID pode ser especificada.

object_id | NULO | 0 | INADIMPLÊNCIA

O ID do objeto da tabela ou vista onde o índice está ativado. object_id é int.

As entradas válidas são o número de ID de uma tabela e exibição, NULL, 0 ou DEFAULT. O padrão é 0. NULL, 0 e DEFAULT são valores equivalentes neste contexto.

Especifique NULL para devolver informação para todas as tabelas e vistas na base de dados especificada. 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

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

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

partition_number | NULO | 0 | INADIMPLÊNCIA

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

Especifique NULL para devolver informação para todas as partições do índice ou heap.

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

Tabela retornada

Nome da coluna Tipo de dados Descrição
database_id pequeno ID da base de dados.

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 da tabela ou vista. Para mais informações, consulte sys.objects (Transact-SQL).
index_id int ID do índice ou heap. Para mais informações, consulte sys.indexes (Transact-SQL).
partition_number int Número de partição baseado em 1 dentro do índice ou heap. Para mais informações, consulte sys.partitions (Transact-SQL).
hobt_id bigint ID da pilha de dados ou do conjunto de linhas da árvore B que rastreia dados internos para um índice columnstore.

NULL - este não é um conjunto de linhas columnstore interno.

Para mais informações, consulte sys.internal_partitions (Transact-SQL).
leaf_insert_count bigint Contagem cumulativa de inserções ao nível das folhas. Para mais informações sobre níveis de índice, consulte Arquitetura de Índice e guia de design.
leaf_delete_count bigint Contagem cumulativa de eliminações ao nível da folha. leaf_delete_count é incrementado apenas para registos eliminados que não são marcados como fantasma primeiro. Para registos eliminados que são ghosted primeiro, leaf_ghost_count é incrementado em vez disso.
leaf_update_count bigint Contagem cumulativa de atualizações ao nível das folhas.
leaf_ghost_count bigint Contagem cumulativa de linhas ao nível das folhas que estão marcadas como eliminadas, mas ainda não removidas. Essa contagem não inclui registros que são excluídos imediatamente sem serem marcados como fantasmas. Uma thread de limpeza remove linhas fantasmas em intervalos definidos. Este valor não inclui linhas fantasmas que são mantidas devido a uma transação de snapshot pendente.
nonleaf_insert_count bigint Contagem cumulativa de inserções acima do nível foliar. Aplica-se apenas a índices da árvore B. 0 para heaps ou índices de columnstore.
nonleaf_delete_count bigint Contagem cumulativa de eliminações acima do nível foliar. Aplica-se apenas a índices da árvore B. 0 para heaps ou índices de columnstore.
nonleaf_update_count bigint Contagem cumulativa de atualizações acima do nível da folha. Aplica-se apenas a índices da árvore B. 0 para heaps ou índices de columnstore.
leaf_allocation_count bigint Contagem cumulativa das alocações de páginas ao nível da folha no índice ou heap.

Para um índice, uma alocação de página corresponde a uma divisão de página.
nonleaf_allocation_count bigint Contagem cumulativa de alocações de páginas causadas por divisões de página acima do nível da folha. Aplica-se apenas a índices da árvore B. 0 para heaps ou índices de columnstore.
leaf_page_merge_count bigint Contagem cumulativa de mesclagens de páginas no nível da folha. Sempre 0 para índices de colunstore.
nonleaf_page_merge_count bigint A contagem cumulativa de páginas mescla acima do nível da folha. Aplica-se apenas a índices da árvore B. 0 para heaps ou índices de columnstore.
range_scan_count bigint Contagem cumulativa de varreduras de intervalo e tabela iniciadas no índice ou heap.
singleton_lookup_count bigint Contagem cumulativa de recuperações de linha única do índice ou heap.
forwarded_fetch_count bigint Contagem de linhas que foram obtidas através de um registro de encaminhamento. Aplica-se apenas a heaps, 0 para índices de árvore B.
lob_fetch_in_pages bigint Contagem cumulativa de páginas de objetos grandes (LOB) recuperadas de uma LOB_DATA unidade de alocação. Estas páginas contêm dados armazenados em colunas do tipo , , , , , , , xml, e json. nvarchar(max)varbinary(max)varchar(max)imagentexttext Para obter mais informações, consulte Tipos de dados (Transact-SQL).
lob_fetch_in_bytes bigint Contagem cumulativa de bytes de dados LOB recuperados.
lob_orphan_create_count bigint Contagem cumulativa de valores de LOB órfãos criados para operações em massa. Aplica-se apenas a heaps e índices agrupados em árvores B, 0 para índices não agrupados e columnstore.
lob_orphan_insert_count bigint Contagem cumulativa de valores de LOB órfãos inseridos durante operações em massa. Aplica-se apenas a heaps e índices agrupados em árvores B, 0 para índices não agrupados e columnstore.
row_overflow_fetch_in_pages bigint Contagem cumulativa de páginas de dados de overflow de linhas recuperadas de uma ROW_OVERFLOW_DATA unidade de alocação.

Estas páginas contêm dados armazenados em colunas do tipo varchar(n), nvarchar(n), varbinary(n), e sql_variant para linhas grandes.
row_overflow_fetch_in_bytes bigint Contagem cumulativa de bytes de dados de estouro de linha recuperados.
column_value_push_off_row_count bigint Contagem cumulativa de valores de coluna para dados LOB e dados de estouro de linha que são empurrados para fora da linha para fazer com que uma linha inserida ou atualizada caiba em uma página.
column_value_pull_in_row_count bigint Contagem cumulativa de valores de coluna para dados LOB e dados de estouro de linha que são puxados em linha. Isto ocorre quando uma operação de atualização liberta espaço num registo e oferece a oportunidade de puxar um ou mais valores fora da linha de unidades LOB_DATAROW_OVERFLOW_DATA de alocação para a IN_ROW_DATA unidade de alocação.
row_lock_count bigint Número acumulado de bloqueios de linha solicitados.
row_lock_wait_count bigint Número acumulado de vezes que o Mecanismo de Banco de Dados esperou em um bloqueio de linha.
row_lock_wait_in_ms bigint Número total de milissegundos que o Mecanismo de Banco de Dados esperou em um bloqueio de linha.
page_lock_count bigint Número acumulado de bloqueios de página solicitados.
page_lock_wait_count bigint Número acumulado de vezes que o Mecanismo de Banco de Dados esperou em um bloqueio de página.
page_lock_wait_in_ms bigint Número total de milissegundos que o Mecanismo de Banco de Dados esperou em um bloqueio de página.
index_lock_promotion_attempt_count bigint Número acumulado de vezes que o Mecanismo de Banco de Dados tentou escalar bloqueios.
index_lock_promotion_count bigint Número acumulado de vezes que o Mecanismo de Banco de Dados escalonou bloqueios.
page_latch_wait_count bigint Número cumulativo de vezes que o Motor de Base de Dados esperou para adquirir um latch.
page_latch_wait_in_ms bigint Número cumulativo de milissegundos que o Motor de Base de Dados esperou para adquirir um latch.
page_io_latch_wait_count bigint Número cumulativo de vezes que o Motor de Base de Dados esperou por um latch de E/S de página.
page_io_latch_wait_in_ms bigint Número acumulado de milissegundos que o Mecanismo de Banco de Dados esperou em uma trava de E/S de página.
tree_page_latch_wait_count bigint Um subconjunto disso page_latch_wait_count inclui apenas as páginas de árvore B de nível superior. Sempre 0 para um índice de armazenamento de pilha ou coluna.
tree_page_latch_wait_in_ms bigint Um subconjunto disso page_latch_wait_in_ms inclui apenas as páginas de árvore B de nível superior. Sempre 0 para um índice de armazenamento de pilha ou coluna.
tree_page_io_latch_wait_count bigint Um subconjunto disso page_io_latch_wait_count inclui apenas as páginas de árvore B de nível superior. Sempre 0 para um índice de armazenamento de pilha ou coluna.
tree_page_io_latch_wait_in_ms bigint Um subconjunto disso page_io_latch_wait_in_ms inclui apenas as páginas de árvore B de nível superior. Sempre 0 para um índice de armazenamento de pilha ou coluna.
page_compression_attempt_count bigint Número de páginas avaliadas para compressão ao nível da PÁGINA para uma partição específica de uma tabela, índice ou vista indexada. Inclui páginas que não foram comprimidas porque não se conseguiram poupanças significativas. Sempre 0 para índices de colunstore.
page_compression_success_count bigint Número de páginas de dados que foram compactadas usando a compactação PAGE para partições específicas de uma tabela, índice ou exibição indexada. Sempre 0 para índices de colunstore.
version_generated_inrow bigint Contagem cumulativa de versões em linha com carga útil gerada no heap ou na árvore B para uma operação de atualização, fusão ou inserção sobre fantasma. Uma versão em linha armazena a imagem da linha antiga (ou um diferencial) diretamente na linha, evitando uma ida à loja de versões. Esta contagem é um superconjunto que inclui versões contadas por insert_over_ghost_version_inrow. Para mais informações sobre versões in-row e off-row, consulte Espaço usado pelo armazenamento persistente de versões (PVS).
version_generated_offrow bigint Contagem cumulativa de versões enviadas para o armazenamento fora da linha para uma operação de eliminação, atualização, fusão ou inserção sobre ghost de heap, árvore B ou LOB. Uma versão fora da linha é gerada quando a imagem da linha antiga não pode ser mantida na linha. Esta contagem é um superconjunto que inclui versões contadas por ghost_version_offrow e insert_over_ghost_version_offrow.
ghost_version_inrow bigint A contagem cumulativa de vezes em que uma eliminação ou atualização (realizada como uma eliminação seguida de uma inserção) marcou a linha existente como fantasma com informação de versionamento na linha. A versão na linha armazena apenas um carimbo temporal da transação e uma carga útil de comprimento zero, pelo que desfazer a eliminação requer apenas o desalojamento da linha.
ghost_version_offrow bigint A contagem cumulativa de vezes em que uma eliminação ou atualização (realizada como eliminação seguida de inserção) empurrava os dados existentes da linha ou coluna LOB para o armazenamento fora da linha, deixando um stub na linha para a informação de versionamento. Este contador é incrementado juntamente version_generated_offrow com as operações fantasma.
insert_over_ghost_version_inrow bigint Contagem cumulativa de versões em linha com carga útil gerada para uma operação de inserção sobre ghost em árvore B. Um insert-over-ghost ocorre quando uma nova linha é inserida no slot de um registo anteriormente ghosted, seja a partir de uma eliminação explícita seguida de uma inserção, ou de uma atualização ou fusão implementada como eliminação seguida de inserção. Este contador é um subconjunto de version_generated_inrow.
insert_over_ghost_version_offrow bigint Contagem cumulativa de vezes em que a linha fantasma existente foi enviada para o armazenamento fora da linha durante uma operação de inserção sobre fantasma na árvore B, deixando um stub na linha recém-inserida para informação de versionamento. Este contador é um subconjunto de version_generated_offrow.

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.

Comentários

Esta função não devolve informação sobre índices em tabelas otimizadas para memória. Para informações sobre índices em tabelas otimizadas para memória, veja sys.dm_db_xtp_index_stats (Transact-SQL).

Esta função não aceita parâmetros correlacionados de CROSS APPLY e OUTER APPLY.

Pode usar sys.dm_db_index_operational_stats para rastrear dados de leitura e escrita de estatísticas de operações, e estatísticas de bloqueio, bloqueio de página e latch de I/O de página para uma tabela, índice ou partição. Pode identificar as tabelas, índices e partições que estão a enfrentar atividade ou contenção significativa.

As estatísticas são fornecidas ao nível da partição e são aditivas. Isto significa que pode obter estatísticas ao nível de índice ou de tabela escrevendo uma consulta de agregação em T-SQL. Para mais informações, consulte o exemplo de varrimentos e procuras de índice para todas as tabelas .

Para analisar estatísticas de operações de leitura e escrita para uma tabela, índice ou partição, use estas colunas:

  • leaf_insert_count
  • leaf_delete_count
  • leaf_update_count
  • leaf_ghost_count
  • range_scan_count
  • singleton_lookup_count

Para identificar a contenda de latch, use estas colunas:

  • page_latch_wait_count
  • page_latch_wait_in_ms

Para identificar a contenda de bloqueios, use estas colunas:

  • row_lock_count
  • page_lock_count
  • row_lock_wait_in_ms
  • page_lock_wait_in_ms

Para analisar as estatísticas físicas de E/S, use estas colunas:

  • page_io_latch_wait_count
  • page_io_latch_wait_in_ms

Observações na coluna

Os valores nas colunas lob_fetch_in_pages e lob_fetch_in_bytes podem ser maiores que zero para índices não agrupados que contenham uma ou mais colunas LOB como colunas incluídas. Para obter mais informações, consulte Criar índices com colunas incluídas. De forma semelhante, os valores nas colunas row_overflow_fetch_in_pages e row_overflow_fetch_in_bytes podem ser maiores que 0 para índices não agrupados se o índice contiver linhas grandes.

Como os contadores na cache de metadados são reiniciados

Os dados devolvidos por sys.dm_db_index_operational_stats existem apenas enquanto estiver disponível um objeto de cache de metadados que represente o heap ou a árvore B. Estes dados não são persistentes. Isto significa que não pode usar estes contadores para determinar conclusivamente se um índice foi usado ou não, ou quando o índice foi usado pela última vez. Em vez disso, usa sys.dm_db_index_usage_stats (Transact-SQL).

Os valores de cada coluna numérica são definidos para zero sempre que os metadados do heap ou da árvore B são introduzidos na cache de metadados. As estatísticas são acumuladas até que o objeto cache seja removido da cache de metadados. Um heap ativo ou B-tree normalmente tem os seus metadados na cache, e as contagens cumulativas refletem a atividade desde que a instância do Motor de Base de Dados foi iniciada pela última vez. Os metadados de um heap menos ativo ou B-tree podem mover-se para dentro e fora da cache à medida que são usados, especialmente se a instância do Motor de Base de Dados estiver sob pressão de memória. Como resultado, as estatísticas operacionais do índice podem por vezes não ser refletidas em sys.dm_db_index_operational_stats. Isso não é comum.

As estatísticas são removidas da cache e deixam de ser reportadas por esta função se uma tabela ou índice for eliminado, ou se uma partição for truncada. Outras operações DDL contra o índice podem fazer com que o valor das estatísticas seja reiniciado para zero.

Usando 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. Certifique-se sempre de que é devolvido um ID válido quando utiliza DB_ID ou OBJECT_ID. Para mais informações, consulte Informação de Retorno para uma tabela especificada.

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 para devolver informação sobre todos os objetos dentro da base de dados especificada, quando um valor para @object_id não é especificado.

  • VIEW SERVER STATE ou VIEW SERVER PERFORMANCE STATE permissão para devolver informação sobre todas as bases de dados, quando um valor para @database_id não é especificado.

Conceder VIEW DATABASE STATE ou VIEW SERVER PERFORMANCE STATE permitir que todos os objetos da base de dados sejam devolvidos, independentemente de quaisquer CONTROL permissões negadas em objetos específicos.

Negar VIEW DATABASE STATE ou VIEW SERVER PERFORMANCE STATE impedir que todos os objetos na base de dados sejam devolvidos, independentemente de quaisquer CONTROL permissões concedidas a objetos específicos.

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

Exemplos

Informação de retorno para uma tabela especificada

O exemplo seguinte devolve informações para todos os índices e partições da Person.Address tabela na base de dados AdventureWorks2025.

Importante

Quando estiver a usar as funções DB_ID Transact-SQL e OBJECT_ID para devolver um valor de parâmetro, certifique-se sempre de que um ID válido é devolvido. Se a base de dados ou o nome do objeto não puder ser encontrado, como quando não existem ou estão escritos incorretamente, ambas as funções retornam NULL. A sys.dm_db_index_operational_stats função é interpretada como um valor curinga NULL que especifica todos os bancos de dados ou todos os objetos. Como isso pode ser uma operação não intencional, os exemplos nesta seção demonstram a maneira segura de determinar IDs de banco de dados e objeto.

DECLARE @db_id int = DB_ID(N'AdventureWorks2025');
DECLARE @object_id int = OBJECT_ID(N'AdventureWorks2025.Person.Address');

SELECT *
FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL)
WHERE @db_id IS NOT NULL AND @object_id IS NOT NULL;

Informação de retorno para todas as tabelas e índices

O exemplo seguinte devolve informação para todas as tabelas e índices numa instância do Motor de Base de Dados.

SELECT *
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);

Varreduras e buscas de índice para todas as tabelas

O exemplo seguinte agrega dados ao nível de partição para devolver estatísticas de busca e varrimento de índice para todas as tabelas da base de dados atual.

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS object_name,
       COUNT(DISTINCT(index_id)) AS index_count,
       COUNT(DISTINCT(partition_number)) AS partition_count,
       SUM(range_scan_count) AS index_scan_count,
       SUM(singleton_lookup_count) AS index_seek_count
FROM sys.dm_db_index_operational_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT)
GROUP BY OBJECT_SCHEMA_NAME(object_id),
         OBJECT_NAME(object_id)
ORDER BY schema_name, object_name;

Conteúdo relacionado

  • Last updated on