• Artigo

Conjuntos de coleta de dados do sistema

O coletor de dados instala três conjuntos de coleta de Dados do Sistema durante o processo de Instalação do SQL Server 2008. Esses conjuntos de coleta podem ser configurados para se adequarem a seus requisitos de monitoramento, mas não podem ser excluídos. Os conjuntos de coleta de Dados do Sistema consistem no seguinte:

  • Uso do Disco. Coleta dados sobre o disco e o uso de log de todos os bancos de dados instalados no sistema.

  • Atividade do servidor. Coleta estatísticas de uso de recursos e dados de desempenho do servidor e do SQL Server.

  • Estatísticas de consulta. Coleta estatísticas de consulta, texto de consulta individual, planos de consulta e consultas específicas.

Conjunto de coleta de uso de disco

O conjunto de coleta de Uso de Disco controla o aumento do banco de dados e dos arquivos de log e fornece estatísticas relacionadas a arquivos, como o aumento médio (em megabytes) diário.

O conjunto de coleta tem dois itens de coleta, Uso do Disco – Arquivos de Dados e Uso do Disco – Arquivos de Log. Os dois usam o tipo de coletor de Consultas T-SQL Genérico. O conjunto de coleta reúne os seguintes dados:

  • Instantâneos de tamanhos de arquivos de dados obtidos das exibições sys.partitions e sys.allocation_units.

  • Instantâneos de tamanhos de arquivos de log obtidos do comando DBCC SQLPERF (LOGSPACE).

  • Instantâneos de estatísticas de E/S da função sys.dm_io_virtual_file_stats.

As tabelas a seguir fornecem informações detalhadas sobre o conjunto de coleta Uso do Disco e seus itens de coleta.

Nome do conjunto de coleta

Uso do Disco

Modo de coleta

Não armazenado em cache

Frequência da agenda de carregamento

A cada 6 horas

Retenção de dados

730 dias

Itens de coleta

Uso do Disco - Arquivos de Dados

Uso do Disco - Arquivos de Log

Nome do item de coleta

Uso do Disco - Arquivos de Dados

Tipo de coletor

Consulta T-SQL Genérico

Consulta 1

 
SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 
      ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 
      ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files
SELECT @reservedpages = SUM(a.total_pages) 
       ,@usedpages = SUM(a.used_pages) 
       ,@pages = SUM(CASE 
                        WHEN it.internal_type IN (202,204) THEN 0 
                        WHEN a.type != 1 THEN a.used_pages 
                        WHEN p.index_id < 2 THEN a.data_pages 
                        ELSE 0 
                     END) 
FROM sys.partitions p  
JOIN sys.allocation_units a ON p.partition_id = a.container_id 
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 
SELECT 
        @dbsize as ''dbsize'',
        @logsize as ''logsize'',
        @ftsize as ''ftsize'',
        @reservedpages as ''reservedpages'',
        @usedpages as ''usedpages'',
        @pages as ''pages''

Saída da consulta 1

disk_usage

Nome do item de coleta

Uso do Disco - Arquivos de Log

Tipo de coletor

Consulta T-SQL Genérico

Consulta 1

 
INSERT INTO @tran_log_space_usage 
EXEC(''DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'');
SELECT 
    database_name,
    log_size_mb,
    log_space_used,
    status    
FROM @tran_log_space_usage

Saída da consulta 1

log_usage

Conjunto de coleta de atividade do servidor

O conjunto de coleta de Atividade do Servidor fornece uma visão geral da atividade do SQL Server, utilização de recursos do SQL Server e contenção de recursos do SQL Server. O conjunto de coleta também fornece uma exibição encapsulada da utilização geral de recursos do sistema, o que permite a um usuário determinar se os problemas de desempenho estão relacionados às atividades fora do escopo do SQL Server.

Esse conjunto de coleta reúne exemplos de dados das exibições de gerenciamento dinâmico a seguir:

  • sys.dm_os_wait_stats

  • sys.dm_os_latch_stats

  • sys.dm_os_schedulers

  • sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_os_waiting_tasks (usando uma consulta unida)

  • sys.dm_os_process_memory

  • sys.dm_os_memory_nodes

Adicionalmente, exemplos de dados são coletados de vários sistemas e de contadores de desempenho do SQL Server.

O conjunto de coleta de Atividade do Servidor fornece uma visão geral do sistema em termos de utilização de recursos e de afunilamentos de recursos. O uso de recursos é rastreado em quatro áreas gerais: CPU, E/S de disco, memória e rede. A amostragem de sys.dm_exec_sessions, sys.dm_exec_requests e sys.dm_os_waiting_tasks permite a correlação da atividade do sistema com os afunilamentos de recursos e com problemas de bloqueio.

Executado por conta própria, esse conjunto de coleta permite associar os afunilamentos de recursos a uma sessão bloqueada e mostrar cadeias de bloqueio no nível da sessão. Embora os textos de consulta não sejam coletados, o usuário pode usar as informações de sql_handle e plan_handle coletadas pelo conjunto de coleta Estatísticas de Consulta para fazer busca detalhada abaixo do nível da sessão.

As tabelas a seguir fornecem informações detalhadas sobre o conjunto de coleta de Atividade do Servidor e seus itens de coleta.

Nome do conjunto de coleta

Atividade do servidor

Modo de coleta

Em cache

Frequência da agenda de carregamento

A cada 15 minutos

Retenção de dados

14 dias

Itens de coleta

Atividade do Servidor - Instantâneos DMV

Atividade do Servidor – Contadores de Desempenho

Nome do item de coleta

Atividade do Servidor – Instantâneos DMV

Tipo de coletor

Consulta T-SQL Genérico

Frequência da coleta

60 segundos

Consulta 1

 
SELECT 
    LEFT (wait_type, 45) AS wait_type, 
    SUM (waiting_tasks_count) AS waiting_tasks_count, 
    SUM (wait_time_ms) AS wait_time_ms, 
    SUM (signal_wait_time_ms) AS signal_wait_time_ms
FROM
 (SELECT 
    LEFT (wait_type, 45) AS wait_type, 
    waiting_tasks_count, 
    wait_time_ms,  
    signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE waiting_tasks_count > 0 OR wait_time_ms > 0 OR signal_wait_time_ms > 0
UNION ALL 
    SELECT 
        LEFT (wait_type, 45) AS wait_type, 
        1 AS waiting_tasks_count, 
        wait_duration_ms AS wait_time_ms, 
        0 AS signal_wait_time_ms
    FROM sys.dm_os_waiting_tasks
    WHERE wait_duration_ms > 60000
) AS merged_wait_stats
GROUP BY wait_type

Saída da consulta 1

snapshots.os_wait_stats

Consulta 2

 
SELECT 
  LEFT(latch_class,45) as latch_class,
  waiting_requests_count,
  wait_time_ms
FROM sys.dm_os_latch_stats 
WHERE waiting_requests_count > 0 OR wait_time_ms > 0

Saída da consulta 2

snapshots.os_latch_stats

Consulta 3

 
SELECT 
    pm.physical_memory_in_use_kb            AS sql_physical_memory_in_use_kb, 
    pm.large_page_allocations_kb            AS sql_large_page_allocations_kb, 
    pm.locked_page_allocations_kb           AS sql_locked_page_allocations_kb, 
    pm.total_virtual_address_space_kb       AS sql_total_virtual_address_space_kb, 
    pm.virtual_address_space_reserved_kb    AS sql_virtual_address_space_reserved_kb, 
    pm.virtual_address_space_committed_kb   AS sql_virtual_address_space_committed_kb, 
    pm.virtual_address_space_available_kb   AS sql_virtual_address_space_available_kb, 
    pm.page_fault_count                     AS sql_page_fault_count, 
    pm.memory_utilization_percentage        AS sql_memory_utilization_percentage, 
    pm.available_commit_limit_kb            AS sql_available_commit_limit_kb, 
    pm.process_physical_memory_low          AS sql_process_physical_memory_low, 
    pm.process_virtual_memory_low           AS sql_process_virtual_memory_low, 
    
    sm.total_physical_memory_kb             AS system_total_physical_memory_kb, 
    sm.available_physical_memory_kb         AS system_available_physical_memory_kb, 
    sm.total_page_file_kb                   AS system_total_page_file_kb, 
    sm.available_page_file_kb               AS system_available_page_file_kb, 
    sm.system_cache_kb                      AS system_cache_kb, 
    sm.kernel_paged_pool_kb                 AS system_kernel_paged_pool_kb, 
    sm.kernel_nonpaged_pool_kb              AS system_kernel_nonpaged_pool_kb, 
    sm.system_high_memory_signal_state      AS system_high_memory_signal_state, 
    sm.system_low_memory_signal_state       AS system_low_memory_signal_state, 
    
    si.bpool_commit_target                  AS bpool_commit_target, 
    si.bpool_committed                      AS bpool_committed, 
    si.bpool_visible                        AS bpool_visible
FROM sys.dm_os_process_memory AS pm
CROSS JOIN sys.dm_os_sys_memory AS sm   -- single-row DMV
CROSS JOIN sys.dm_os_sys_info AS si;    -- single-row DMV

Saída da consulta 3

snapshots.sql_process_and_system_memory

Consulta 4

 
SELECT 
    memory_node_id, 
    virtual_address_space_reserved_kb, 
    virtual_address_space_committed_kb, 
    locked_page_allocations_kb, 
    single_pages_kb, 
    multi_pages_kb, 
    shared_memory_reserved_kb, 
    shared_memory_committed_kb
FROM sys.dm_os_memory_nodes

Saída da consulta 4

snapshots.os_memory_nodes

Consulta 5

 
SELECT 
    type,
    memory_node_id as memory_node_id,
    SUM(single_pages_kb) as single_pages_kb,
    SUM(multi_pages_kb) as multi_pages_kb,
    SUM(virtual_memory_reserved_kb) as virtual_memory_reserved_kb,
    SUM(virtual_memory_committed_kb) as virtual_memory_committed_kb,
    SUM(awe_allocated_kb) as awe_allocated_kb,
    SUM(shared_memory_reserved_kb) as shared_memory_reserved_kb,
    SUM(shared_memory_committed_kb) as shared_memory_committed_kb
FROM sys.dm_os_memory_clerks
GROUP BY type, memory_node_id

Saída da consulta 5

snapshots.os_memory_clerks

Consulta 6

 
SELECT 
    [parent_node_id],
    [scheduler_id],
    [cpu_id],
    [status],
    [is_idle],
    [preemptive_switches_count],
    [context_switches_count],
    [yield_count],
    [current_tasks_count],
    [runnable_tasks_count],
    [work_queue_count],
    [pending_disk_io_count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 128

Saída da consulta 6

snapshots.os_schedulers

Consulta 7

 
SELECT 
    DB_NAME (f.database_id) AS database_name, f.database_id, f.name AS logical_file_name, f.[file_id], f.type_desc, 
    CAST (CASE 
        -- Handle UNC paths (e.g. ''\\fileserver\readonlydbs\dept_dw.ndf'' --&gt; ''\\fileserver\readonlydbs'')
        WHEN LEFT (LTRIM (f.physical_name), 2) = ''\\'' 
            THEN LEFT (LTRIM (f.physical_name), 
            CHARINDEX (''\'', 
            LTRIM (f.physical_name), 
            CHARINDEX (''\'', 
            LTRIM (f.physical_name), 3) + 1) - 1)
        -- Handle local paths (e.g. ''C:\Program Files\...\master.mdf'' --&gt; ''C:'') 
        WHEN CHARINDEX (''\'', LTRIM(f.physical_name), 3) &gt; 0 
            THEN UPPER (LEFT (LTRIM (f.physical_name), CHARINDEX (''\'', LTRIM (f.physical_name), 3) - 1))
        ELSE f.physical_name
    END AS nvarchar(255)) AS logical_disk, 
    fs.num_of_reads, fs.num_of_bytes_read, fs.io_stall_read_ms, fs.num_of_writes, fs.num_of_bytes_written, 
    fs.io_stall_write_ms, fs.size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats (default, default) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]

Saída da consulta 7

snapshots.io_virtual_file_stats

Nome do item de coleta

Atividade do Servidor – Contadores de Desempenho

Tipo de coletor

Contadores de desempenho

Frequência da coleta

60 segundos

Contadores de desempenho usados

Contadores de "memória"="% bytes confirmados em uso"

Contadores de "memória"="bytes disponíveis"

Contadores de "memória"="bytes de cache"

Contadores de "memória"="Falhas de cache/segundo"

Contadores de "memória"="bytes confirmados"

Contadores de "memória"="Liberar &amp; Zero bytes de lista de páginas"

Contadores de "memória"="Bytes da lista de páginas modificadas"

Contadores de "memória"="Páginas/s"

Contadores de "memória"="Leituras de página/s"

Contadores de "memória"="Gravações de página/s"

Contadores de "memória"="Falhas de página/s"

Contadores de "memória"="Bytes não paginados do pool"

Contadores de "memória"="Bytes paginados do pool"

Contadores de "memória"="Bytes de núcleo de cache em espera"

Contadores de "memória"="Bytes de prioridade normal do cache em espera"

Contadores de "memória"="Bytes de reserva de cache em espera"

Contadores de "memória"="Bytes paginados do pool"

Contadores de "memória"="Cópias de gravação/s"

Contadores de "processo"="*" Instâncias="_Total"

Contadores de "processo"="*" Instâncias="$(TARGETPROCESS)"

Contadores de "processo"=Instâncias de "contagem de threads"="*"

Contadores de "processo"="Instâncias de "% tempo do processador"="*"

Contadores de "processo"=Instâncias de "bytes de leitura de E/S/segundo"="*"

Contadores de "processo"=Instâncias de "Bytes de gravação de E/S/segundo"="*"

Contadores de "processo"=Instâncias de "bytes privados"="*"

Contadores de "processo"=Instâncias de "conjunto de trabalho"="*"

Contadores de "processador"=Instâncias de "% de tempo do processador"="*"

Contadores de "processo"=Instâncias de "% de tempo do usuário"="*"

Contadores de "processo"=Instâncias de "% de tempo privilegiado"="*"

Contadores de "filas de trabalho do servidor"=Instâncias de "comprimento da fila"="*"

Contadores de "LogicalDisk"=Instâncias de "% de tempo de disco"="*"

Contadores de "LogicalDisk" = Instâncias de "comprimento médio da fila do disco"="*"

Contadores de "LogicalDisk" = Instâncias de "comprimento médio da fila de leitura do disco"="*"

Contadores de "LogicalDisk" = Instâncias de "comprimento médio da fila de gravação em disco"="*"

Contadores de "LogicalDisk"=Instâncias da "média de leitura do disco/s"="*"

Contadores de "LogicalDisk" = Instâncias da "média de gravação em disco/s"="*"

Contadores de "LogicalDisk" = Instâncias da "média de transferência do disco/s"="*"

Contadores de "LogicalDisk"=Instâncias de "leituras de disco/s"="*"

Contadores de "LogicalDisk"=Instâncias de "bytes de disco/s"="*"

Contadores de "LogicalDisk"=Instâncias de "gravações em disco/s"="*"

Contadores de "LogicalDisk"=Instâncias de "E/S dividida/s"="*"

Contadores de "sistema"="Comprimento da fila do processador"

Contadores de "sistema"="Operações de leitura de arquivo/s"

Contadores de "sistema"="Operações de gravação de arquivo/s"

Contadores de "Sistema"="Operações de controle de arquivo/segundo"

Contadores de "Sistema"="Bytes de leitura de arquivo/segundo"

Contadores de "sistema"="Bytes de gravação de arquivo/s"

Contadores de "sistema"="Bytes de controle de arquivo/s"

Contadores de "interface de rede"=Instâncias de "total de bytes/s"="*"

Contadores de "interface de rede"=Instâncias de "comprimento da fila de saída"="*"

Contadores de "SQLServer:Gerenciador de Buffer" = "Páginas roubadas"

Contadores de "SQLServer:Gerenciador de Buffer" = "Expectativa de vida da página"

Contadores de "SQLServer:Gerenciador de Memória"="Concessões de memória emitidas"

Contadores de "SQLServer:Gerenciador de Memória"="Concessões de memória pendentes"

Contadores de "SQLServer:Bancos de dados" = Instâncias de "transações/s"="_Total"

Contadores de "SQLServer:Bancos de dados" = Instâncias de "transações/s"="tempdb"

Contadores de "SQLServer:Bancos de dados" = Instâncias de "transações ativas"="*"

Contadores de "SQLServer:estatísticas gerais"="Logons/s"

Contadores de "Estatísticas de SQLServer:General"="Logoffs/segundo"

Contadores de "estatísticas de SQLServer:General"="Conexões do usuário"

Contadores de "estatísticas de SQLServer:General"="Conexões lógicas"

Contadores de "estatísticas de SQLServer:General"="Transações"

Contadores de "SQLServer:estatísticas gerais"="Processos bloqueados"

Contadores de "SQLServer:estatísticas gerais"="Tabelas temporárias ativas"

Contadores de "SQLServer:estatísticas SQL"="Solicitações em lotes/s"

Contadores de "estatísticas de SQLServer:SQL"="Compilações SQL/segundo"

Contadores de "estatísticas de SQLServer:SQL"="Recompilações/segundo"

Contadores de "SQLServer:estatísticas SQL"="Taxa de atenção do SQL"

Contadores de "SQLServer:estatísticas SQL"=Tentativas de param. autom./s"

Contadores de "SQLServer:estatísticas SQL"="Param. autom. com falha/s"

Contadores de "SQLServer:cache de planos"=Instâncias de "taxa de acertos do cache"="_Total"

Contadores de "SQLServer:cache de planos"=Instâncias de "taxa de acertos do cache"="planos do objeto"

Contadores de "SQLServer:cache de planos"=Instâncias de "taxa de acertos do cache"="Planos do SQL"

Contadores de "SQLServer:cache de planos=Instâncias de "taxa de acertos do cache"=Tabelas temporárias" && variáveis da tabela"

Contadores de "SQLServer:transações" = "Espaço livre no tempdb (KB)"

Contadores de "SQLServer:estatísticas de grupo de cargas de trabalho"=Instâncias de "solicitações ativas"="*"

Contadores de "SQLServer:estatísticas de grupo de cargas de trabalho"=Instâncias de "tarefas bloqueadas"="*"

Contadores de "SQLServer:estatísticas de grupo de cargas de trabalho"=Instâncias de "% de uso de CPU"="*"

Conjunto de coleta de estatísticas de consulta

O conjunto de coleta de Estatísticas de Consulta reúne dados sobre estatísticas de consulta e texto de consulta individual, planos de consulta e consultas específicas. Esses dados, quando vinculados com as estatísticas e atividades no nível do sistema, permitem que os usuários façam busca detalhada abaixo do nível de sessão para uma consulta individual.

Esse conjunto de coleta reúne dados das fontes a seguir:

  • sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats e outras exibições de gerenciamento dinâmico relacionado.

  • O texto de lotes e consultas selecionados.

  • O plano de lotes e consultas selecionados.

  • O texto normalizado de lotes selecionados.

O conjunto de coleta de Estatísticas de Consultas usa o tipo de coletor de Atividade de Consulta. O tipo de coletor de Atividade de Consulta coleta dados usando o pacote SSIS de QueryActivityCollect.dtsx e carrega dados usando o pacote SSIS de QueryActivityUpload.dtsx. Para obter mais informações sobre as fases de coleta e de carregamento do tipo de coleta Atividade de Consulta, inclusive as consultas que são usadas, consulte Tipo de coletor Atividade de Consulta.

As tabelas a seguir fornecem informações sobre o conjunto de coleta de Estatísticas de Consulta e seu item de coleta.

Nome do conjunto de coleta

Estatísticas de consulta

Modo de coleta

Em cache

Frequência da agenda de carregamento

A cada 15 minutos

Retenção de dados

14 dias

Item de coleta

Estatísticas de Consulta – Atividade de Consulta