sys.dm_db_index_operational_stats (Transact-SQL)

Retorna a atividade atual de E/S de baixo nível, de bloqueio, de trava e de método de acesso de cada partição de uma tabela ou índice no banco de dados.

Convenções da sintaxe 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 | DEFAULT
  ID do banco de dados. database_id é smallint. 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 de todos os bancos de dados na instância do SQL Server. Se você especificar NULL para database_id, especifique também NULL para object_id, index_id e partition_number.

  A função interna DB_ID pode ser especificada. Ao usar DB_ID sem especificar um nome de banco de dados, o nível de compatibilidade do banco de dados atual deve ser 90.

• object_id | NULL | 0 | DEFAULT
  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 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 | DEFAULT
  ID do índice. index_id é int. As entradas válidas são o número da 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 | DEFAULT
  O número da partição no objeto. partition_number é int. As 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 é de base 1. Um índice ou heap não particionado tem partition_number definido como 1.

Tabela retornada

Comentários

Esse objeto de gerenciamento dinâmico não aceita parâmetros correlatos 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 Criar índices com colunas incluídas. 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 são reiniciados os contadores no cache de metadados

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 mais informações a respeito, 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 poderão 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 de sistema para especificar valores de parâmetro

É possível usar as funções Transact-SQL DB_ID e OBJECT_ID para especificar um valor para os parâmetros database_id e object_id. Entretanto, passar valores que não sejam válidos para essas funções pode provocar 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:

• Permissão CONTROL para o objeto especificado dentro do banco de dados

• A permissão VIEW DATABASE STATE para retornar informações sobre todos os objetos dentro do banco de dados especificado, usando o curinga de objeto @object\_id = NULL

• A permissão VIEW SERVER STATE para retornar informações sobre todos os banco de dados usando o curinga de banco de dados @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.

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. Além disso, quando o curinga do banco de dados @database\_id= NULL é 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 de todos os índices e partições da tabela Person.Address no banco de dados AdventureWorks2012. A execução dessa consulta exige, no mínimo, a permissão CONTROL para a tabela Person.Address.

Importante

Ao usar 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 o nome do 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 essa pode ser uma operação não intencional, os exemplos desta seção demonstram a maneira segura de determinar as IDs do banco de dados e do objeto.

DECLARE @db_id int;
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_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 na 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

Referência

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)

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)

Conceitos

Monitorar e ajustar o desempenho