sys.dm_db_index_operational_stats (Transact-SQL)
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure
Retorna a E/S atual de nível inferior, bloqueio, travamento e atividade do método de acesso para cada partição de uma tabela ou índice no banco de dados.
Índices otimizados para memória não aparecem neste Detran.
Observação
sys.dm_db_index_operational_stats não retorna informações sobre índices otimizados para memória. Para obter informações sobre o uso de índice otimizado para memória, consulte sys.dm_db_xtp_index_stats (Transact-SQL).
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 é pequena. 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
ID do objeto da tabela ou exibição em que 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 retornar informações armazenadas em cache 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
ID do índice. index_id é int. As entradas válidas são o número de ID de um índice, 0 se object_id for um heap, NULL, -1 ou DEFAULT. O padrão é -1, NULL, -1 e DEFAULT são valores equivalentes neste contexto.
Especifique NULL para retornar informações armazenadas em cache 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
Número da partição no objeto. partition_number é int. As entradas válidas são o 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 retornar informações armazenadas em cache 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. |
| index_id | int | ID do índice ou heap. 0 = Pilha |
| partition_number | int | Número de partição baseado em 1 dentro do índice ou heap. |
| hobt_id | bigint |
Aplica-se a: SQL Server 2016 (13.x) e versões posteriores, Banco de Dados SQL do Azure. 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 obter mais detalhes, consulte sys.internal_partitions (Transact-SQL) |
| leaf_insert_count | bigint | Contagem cumulativa de inserções ao nível das folhas. |
| leaf_delete_count | bigint | Contagem cumulativa de eliminações ao nível das folhas. leaf_delete_count só é incrementado para registros excluídos que não são marcados como fantasma primeiro. Para registros excluídos que são fantasmas primeiro, leaf_ghost_count é incrementado. |
| leaf_update_count | bigint | Contagem cumulativa de atualizações em nível de folha. |
| leaf_ghost_count | bigint | Contagem cumulativa de linhas de nível de folha marcadas como excluídas, mas ainda não removidas. Essa contagem não inclui registros que são excluídos imediatamente sem serem marcados como fantasmas. Essas linhas são removidas por um thread de limpeza em intervalos definidos. Esse valor não inclui linhas que são retidas devido a uma transação de isolamento de instantâneo pendente. |
| nonleaf_insert_count | bigint | Contagem cumulativa de inserções acima do nível foliar. 0 = Pilha ou columnstore |
| nonleaf_delete_count | bigint | Contagem cumulativa de eliminações acima do nível foliar. 0 = Pilha ou columnstore |
| nonleaf_update_count | bigint | Contagem cumulativa de atualizações acima do nível da folha. 0 = Pilha ou columnstore |
| leaf_allocation_count | bigint | Contagem cumulativa de alocações de páginas em nível de 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. 0 = Pilha ou columnstore |
| leaf_page_merge_count | bigint | Contagem cumulativa de mesclagens de páginas no nível da folha. Sempre 0 para o índice columnstore. |
| nonleaf_page_merge_count | bigint | A contagem cumulativa de páginas mescla acima do nível da folha. 0 = Pilha ou 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. 0 = Índices |
| lob_fetch_in_pages | bigint | Contagem cumulativa de páginas de objeto grande (LOB) recuperadas da unidade de alocação de LOB_DATA. Essas páginas contêm 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 (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. 0 = Índice não agrupado |
| lob_orphan_insert_count | bigint | Contagem cumulativa de valores de LOB órfãos inseridos durante operações em massa. 0 = Índice não agrupado |
| row_overflow_fetch_in_pages | bigint | Contagem cumulativa de páginas de dados de estouro de linha recuperadas da unidade de alocação ROW_OVERFLOW_DATA. Essas páginas contêm dados armazenados em colunas do tipo varchar(n), nvarchar(n), varbinary(n)e sql_variant que foi empurrado para fora da linha. |
| 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. Isso ocorre quando uma operação de atualização libera espaço em um registro e fornece uma oportunidade de extrair um ou mais valores fora da linha das unidades de alocação LOB_DATA ou ROW_OVERFLOW_DATA para a unidade de alocação IN_ROW_DATA. |
| 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 acumulado de vezes que o Mecanismo de Banco de Dados esperou, devido à contenção de trava. |
| page_latch_wait_in_ms | bigint | Número acumulado de milissegundos que o Mecanismo de Banco de Dados esperou, devido à contenção de trava. |
| page_io_latch_wait_count | bigint | Número acumulado de vezes que o Mecanismo de Banco de Dados esperou em uma trava de página de E/S. |
| 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 | Subconjunto de page_latch_wait_count que inclui apenas as páginas da árvore B de nível superior. Sempre 0 para um índice de armazenamento de pilha ou coluna. |
| tree_page_latch_wait_in_ms | bigint | Subconjunto de page_latch_wait_in_ms que 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 | Subconjunto de page_io_latch_wait_count que inclui apenas as páginas da á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 | Subconjunto de page_io_latch_wait_in_ms que 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 que foram avaliadas quanto à compactação no nível PAGE para partições específicas de uma tabela, índice ou exibição indexada. Inclui páginas que não foram comprimidas porque não foi possível obter poupanças significativas. Sempre 0 para o índice columnstore. |
| 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 o índice columnstore. |
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
Este objeto de gerenciamento dinâmico não aceita parâmetros correlacionados de CROSS APPLY e OUTER APPLY.
Você pode usar sys.dm_db_index_operational_stats para controlar o período de tempo que os usuários devem esperar para ler ou gravar em uma tabela, índice ou partição e identificar as tabelas ou índices que estão encontrando atividade de E/S significativa ou pontos de acesso.
Use as colunas a seguir para identificar áreas de contenção.
Para analisar um padrão de acesso comum à tabela ou partição de índice, use estas colunas:
leaf_insert_count
leaf_delete_count
leaf_update_count
leaf_ghost_count
range_scan_count
singleton_lookup_count
Para identificar a contenção de travamento e bloqueio, use estas colunas:
page_latch_wait_count e page_latch_wait_in_ms
Essas colunas indicam se há contenção de trava no índice ou na pilha e o significado da contenção.
row_lock_count e page_lock_count
Essas colunas indicam quantas vezes o Mecanismo de Banco de Dados tentou adquirir bloqueios de linha e página.
row_lock_wait_in_ms e page_lock_wait_in_ms
Essas colunas indicam se há contenção de bloqueio no índice ou na pilha e o significado da contenção.
Para analisar estatísticas de E/S físicas em uma partição de índice ou pilha
page_io_latch_wait_count e page_io_latch_wait_in_ms
Essas colunas indicam se E/S físicas foram emitidas para trazer o índice ou páginas de pilha para a memória e quantas E/S foram emitidas.
Comentários da coluna
Os valores em lob_orphan_create_count e lob_orphan_insert_count devem ser sempre iguais.
O valor nas colunas lob_fetch_in_pages e lob_fetch_in_bytes pode ser maior que zero para índices não clusterizados que contêm uma ou mais colunas LOB como colunas incluídas. Para obter mais informações, consulte Criar índices com colunas incluídas. Da mesma forma, o valor nas colunas row_overflow_fetch_in_pages e row_overflow_fetch_in_bytes pode ser maior que 0 para índices não clusterizados se o índice contiver colunas que podem ser empurradas para fora da linha.
Como os contadores no Cache de Metadados são redefinidos
Os dados retornados por sys.dm_db_index_operational_stats existem apenas enquanto o objeto de cache de metadados que representa a pilha ou o índice estiver disponível. Esses dados não são persistentes nem transacionalmente consistentes. Isso significa que você não pode usar esses contadores para determinar se um índice foi usado ou não, ou quando o índice foi usado pela última vez. Para obter informações sobre isso, consulte sys.dm_db_index_usage_stats (Transact-SQL).
Os valores de cada coluna são definidos como zero sempre que os metadados do heap ou índice são trazidos para o cache de metadados e as estatísticas são acumuladas até que o objeto de cache seja removido do cache de metadados. Portanto, um heap ou índice ativo provavelmente sempre terá seus metadados no cache, e as contagens cumulativas podem refletir a atividade desde que a instância do SQL Server foi iniciada pela última vez. Os metadados de uma pilha ou índice menos ativo entrarão e sairão do cache à medida que forem usados. Como resultado, pode ou não ter valores disponíveis. A queda de um índice fará com que as estatísticas correspondentes sejam removidas da memória e não sejam mais relatadas pela função. Outras operações DDL em relação ao índice podem fazer com que o valor das estatísticas seja redefinido 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 obter mais informações, consulte a seção Comentários em sys.dm_db_index_physical_stats (Transact-SQL).
Permissões
Requer as seguintes permissões:
CONTROLpermissão no objeto especificado dentro do banco de dadosVIEW DATABASE STATEouVIEW DATABASE PERFORMANCE STATEpermissão (SQL Server 2022) para retornar informações sobre todos os objetos no banco de dados especificado, usando o curinga de objeto @object_id = NULLVIEW SERVER STATEVIEW SERVER PERFORMANCE STATEpermissã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 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 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 e funções de gerenciamento dinâmico (Transact-SQL).
Exemplos
Um. Retornando informações para uma tabela especificada
O exemplo a seguir retorna informações para todos os índices e partições da tabela Person.Address no banco de dados AdventureWorks2022. A execução dessa consulta exige, no mínimo, a permissão CONTROL em Person.Address tabela.
Importante
Quando você estiver usando as funções Transact-SQL DB_ID e OBJECT_ID para retornar um valor de parâmetro, sempre certifique-se de que uma ID válida seja retornada. Se o banco de dados ou o nome do objeto não puder ser encontrado, como quando eles não existirem ou estiverem escritos incorretamente, ambas as funções retornarão NULL. A função sys.dm_db_index_operational_stats interpreta NULL como um valor curinga 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;
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_operational_stats(@db_id, @object_id, NULL, NULL);
END;
GO
B. Retornando informações para todas as tabelas e índices
O exemplo a seguir retorna informações para todas as tabelas e índices dentro da instância do SQL Server. A execução desta consulta requer a permissão VIEW SERVER STATE.
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);
GO
Ver também
Visões e funções de gerenciamento dinâmico (Transact-SQL)
Visualizações e funções de gerenciamento dinâmico relacionadas ao índice de (Transact-SQL)
monitore e ajuste para de desempenho
sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)