sys.dm_db_index_operational_stats (Transact-SQL)
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure
Retorna a atividade atual de método de E/S de nível inferior, bloqueio, travamento e acesso para cada partição de uma tabela ou índice no banco de dados.
Os índices com otimização de memória não aparecem nessa DMV.
Observação
sys.dm_db_index_operational_stats não retorna informações sobre índices com otimização de memória. Para obter informações sobre o uso de índice com otimização de memória, consulte sys.dm_db_xtp_index_stats (Transact-SQL).
Convenções de sintaxe de Transact-SQL
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 | NULL | 0 | PADRÃO
ID do banco de dados. database_id é pequeno. As entradas válidas são o número da ID de um banco de dados, NULL, 0 ou DEFAULT. O padrão é 0. NULL, 0 e DEFAULT são valores equivalentes neste contexto.
Especifique NULL para retornar informações para todos os bancos de dados na instância do SQL Server. Se você especificar NULL para database_id, também deverá especificar NULL para object_id, index_id e partition_number.
A função interna DB_ID pode ser especificada.
object_id | NULL | 0 | PADRÃO
ID do objeto da tabela ou exibição em que o índice está. object_id é int.
As entradas válidas são o número da ID de uma tabela e de uma 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 em cache de todas as tabelas e exibições no banco de dados especificado. Se você especificar NULL para object_id, também deverá especificar NULL para index_id e partition_number.
index_id | 0 | NULL | -1 | PADRÃO
ID do índice. index_id é int. 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 em cache de todos os índices de uma tabela base ou exibição. Se você especificar NULL para index_id, também deverá especificar NULL para partition_number.
partition_number | NULL | 0 | PADRÃO
O número da partição no objeto. partition_number é int. Entradas válidas são o partion_number de um índice ou heap, NULL, 0 ou DEFAULT. O padrão é 0. NULL, 0 e DEFAULT são valores equivalentes neste contexto.
Especifique NULL para retornar informações em cache de 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 | smallint | ID do banco de dados. Em SQL do Azure Banco de Dados, os valores são exclusivos em um único banco de dados ou um pool elástico, mas não dentro de um servidor lógico. |
| object_id | int | ID da tabela ou exibição. |
| index_id | int | ID do índice ou heap. 0 = Heap |
| partition_number | int | Número de partição com base 1 no índice ou heap. |
| hobt_id | bigint | Aplica-se a: SQL Server 2016 (13.x) e versões posteriores, SQL do Azure Banco de Dados. ID do heap de dados ou do conjunto de linhas de árvore B que rastreia dados internos de 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 de nível folha. |
| leaf_delete_count | bigint | Contagem cumulativa de exclusões de nível folha. 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 de nível folha. |
| leaf_ghost_count | bigint | Contagem cumulativa de linhas de nível folha marcadas como excluídas, mas não removidas ainda. Essa contagem não inclui registros que são excluídos imediatamente sem serem marcados como fantasmas. Essas filas são removidas por um thread de limpeza em intervalos definidos. Esse valor não inclui linhas retidas que foram 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 folha. 0 = Heap ou columnstore |
| nonleaf_delete_count | bigint | Contagem cumulativa de exclusões acima do nível folha. 0 = Heap ou columnstore |
| nonleaf_update_count | bigint | Contagem cumulativa de atualizações acima do nível folha. 0 = Heap ou columnstore |
| leaf_allocation_count | bigint | Contagem cumulativa de alocações de página de nível folha no índice ou heap. Para um índice, uma alocação de página corresponde a uma página dividida. |
| nonleaf_allocation_count | bigint | Contagem cumulativa de alocações de página provocadas por divisões de página acima do nível folha. 0 = Heap ou columnstore |
| leaf_page_merge_count | bigint | Contagem cumulativa de mesclagens de página no nível folha. Sempre 0 para índice columnstore. |
| nonleaf_page_merge_count | bigint | Contagem cumulativa de mesclagens de página acima do nível folha. 0 = Heap ou columnstore |
| range_scan_count | bigint | Contagem cumulativa de exames de intervalo e de tabela iniciados 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 buscadas por meio de um registro de encaminhamento. 0 = Índices |
| lob_fetch_in_pages | bigint | Contagem cumulativa de páginas de LOB (objeto grande) recuperadas da unidade de alocação LOB_DATA. Essas páginas contêm dados armazenados em colunas do tipo text, ntext, image, varchar(max), nvarchar(max), varbinary(max)e xml. Para obter mais informações, consulte Tipos de dados (Transact-SQL). |
| lob_fetch_in_bytes | bigint | Contagem cumulativa de bytes de dados LOB recuperados. |
| lob_orphan_create_count | bigint | Contagem cumulativa de valores LOB órfãos criados para operações em massa. 0 = Índice não clusterizado |
| lob_orphan_insert_count | bigint | Contagem cumulativa de valores LOB órfãos inseridos durante operações em massa. 0 = Índice não clusterizado |
| row_overflow_fetch_in_pages | bigint | Contagem cumulativa de páginas de dados de estouro de linha recuperada 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 foram enviadas 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 empurrados para fora da linha para que uma linha atualizada ou inserida coubesse na página. |
| column_value_pull_in_row_count | bigint | Contagem cumulativa de valores de coluna para dados de LOB e dados de estouro de linha puxados para dentro da linha. Isso ocorre quando uma operação de atualização libera espaço em um registro e fornece uma oportunidade de empurrar um ou mais valores para fora da linha das unidades de alocação LOB_DATA ou ROW_OVERFLOW_DATA. |
| row_lock_count | bigint | Número cumulativo de bloqueios solicitados. |
| row_lock_wait_count | bigint | Número cumulativo 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 cumulativo de bloqueios de página solicitados. |
| page_lock_wait_count | bigint | Número cumulativo 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 cumulativo de vezes que o Mecanismo de Banco de Dados tentou escalonar bloqueios. |
| index_lock_promotion_count | bigint | Número cumulativo de vezes que o Mecanismo de Banco de Dados escalonou bloqueios. |
| page_latch_wait_count | bigint | Número cumulativo de vezes que o Mecanismo de Banco de Dados esperou, devido à contenção de trava. |
| page_latch_wait_in_ms | bigint | Número cumulativo de milissegundos que o Mecanismo de Banco de Dados esperou devido à contenção de trava. |
| page_io_latch_wait_count | bigint | Número cumulativo 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 cumulativo 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 heaps ou índices columnstore. |
| tree_page_latch_wait_in_ms | bigint | Subconjunto de page_latch_wait_in_ms que inclui apenas as páginas da árvore B de nível superior. Sempre 0 para heaps ou índices columnstore. |
| 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 heaps ou índices columnstore. |
| tree_page_io_latch_wait_in_ms | bigint | Subconjunto de page_io_latch_wait_in_ms que inclui apenas as páginas da árvore B de nível superior. Sempre 0 para heaps ou índices columnstore. |
| page_compression_attempt_count | bigint | Número de páginas avaliadas por compactação de nível de PAGE para partições específicas de tabela, índice ou exibição indexada. Inclui páginas que não foram compactadas porque economias significativas não puderam ser obtidas. Sempre 0 para índice columnstore. |
| page_compression_success_count | bigint | Número de páginas de dados que foram compactadas com a compactação de PAGE para partições específicas de tabela, índice ou exibição indexada. Sempre 0 para índice columnstore. |
Observação
A documentação do SQL Server usa o termo árvore B geralmente em referência a índices. Em índices de armazenamento de linha, o SQL Server implementa uma árvore B+. Isso não se aplica a índices columnstore ou armazenamentos de dados na memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.
Comentários
Esse 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 rastrear o período de tempo que os usuários devem aguardar 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 seguintes colunas 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 bloqueio e trava, use estas colunas:
page_latch_wait_count e page_latch_wait_in_ms
Essas colunas indicam se há uma contenção de trava no índice ou heap e a importância 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
Estas colunas indicam se há uma contenção de bloqueio no índice ou heap e a importância da contenção.
Para analisar estatísticas de E/S física em uma partição de índice ou de heap
page_io_latch_wait_count e page_io_latch_wait_in_ms
Essas colunas indicam se foram emitidas E/S físicas para trazer as páginas de índice ou heap para a memória e quantas E/S foram emitidas.
Comentários sobre colunas
Os valores em lob_orphan_create_count e lob_orphan_insert_count devem ser iguais sempre.
O valor nas colunas lob_fetch_in_pages e lob_fetch_in_bytes podem ser maiores do que zero para índices não clusterizados que contenham uma ou mais colunas LOB como colunas incluídas. Para obter mais informações, consulte Create Indexes with Included Columns. Do mesmo modo, o valor nas colunas row_overflow_fetch_in_pages e row_overflow_fetch_in_bytes poderão ser maiores do que 0 para índices não clusterizados se o índice contiver colunas que possam 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 só existem enquanto o objeto do cache de metadados que representa o heap ou o índice está 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 do índice forem trazidos para o cache de metadados e as estatísticas são acumuladas até que o objeto do 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 um heap ou índice menos ativo são movidos para dentro e para fora do cache à medida que ele é usado. Como resultado, ele pode ou não ter valores disponíveis. Descartar um índice fará com que as estatísticas correspondentes sejam removidas da memória e não sejam mais reportadas pela função. Outras operações DDL em relação ao índice podem fazer com que o valor das estatísticas seja redefinido como zero.
Usando funções do sistema para especificar valores de parâmetro
Você pode usar as funções Transact-SQL DB_ID e OBJECT_ID para especificar um valor para os parâmetros database_id e object_id . No entanto, passar valores que não sejam válidos a essas funções pode causar resultados não intencionais. Sempre verifique se uma ID válida é retornada ao usar 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 no banco de dadosVIEW DATABASE STATEouVIEW DATABASE PERFORMANCE STATE(SQL Server 2022) permissão para retornar informações sobre todos os objetos dentro do banco de dados especificado, usando o objeto curinga @object_id = NULLVIEW SERVER STATEVIEW SERVER PERFORMANCE STATE(SQL Server 2022) permissão para retornar informações sobre todos os bancos de dados, usando o curinga do banco de dados @database_id = NULL
A concessão 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 curinga @database_id=NULL 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
a. Retornando informações de uma tabela especificada
O exemplo a seguir retorna informações para todos os índices e partições da Person.Address tabela no banco de dados AdventureWorks2022. A execução dessa consulta exige, no mínimo, a permissão CONTROL para a tabela Person.Address.
Importante
Quando você estiver usando as funções Transact-SQL DB_ID e OBJECT_ID para retornar um valor de parâmetro, sempre verifique se uma ID válida é retornada. 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. A função sys.dm_db_index_operational_stats interpreta NULL como um valor de curinga que especifica todos os bancos de dados ou todos os objetos. Como pode se tratar de uma operação não intencional, os exemplos nesta seção demonstram a maneira segura de determinar a identificação do banco de dados e do 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 dessa consulta exige a permissão VIEW SERVER STATE.
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);
GO
Consulte Também
Exibições e funções de gerenciamento dinâmico (Transact-SQL)
Funções e exibições de gerenciamento dinâmico relacionadas ao índice (Transact-SQL)
Monitorar e ajustar o 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)
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de