• Artículo

System Data Collection Sets

El recopilador de datos instala tres conjuntos de recopilación de datos del sistema durante el proceso de instalación de SQL Server 2008. Estos conjuntos de recopilación se pueden configurar para que se adapten a los requisitos de supervisión, pero no se pueden eliminar. Los conjuntos de recopilación de datos del sistema constan de lo siguiente:

  • Uso de disco. Recopila datos sobre el uso del disco y del registro para todas las bases de datos instaladas en el sistema.

  • Actividad del servidor. Recopila estadísticas de uso de recursos y datos de rendimiento del servidor y SQL Server.

  • Estadísticas de consultas. Recopila estadísticas de consulta, texto de consultas individuales, planes de consulta y consultas concretas.

Conjunto de recopilación Uso de disco

El conjunto de recopilación Uso de disco realiza el seguimiento del crecimiento de las bases de datos y los archivos de registro y proporciona estadísticas relacionadas con los archivos, como el crecimiento medio (en megabytes) por día.

El conjunto de recopilación tiene dos elementos de recopilación, Uso de disco - Archivos de datos y Uso de disco - Archivos de registro. Ambos elementos usan el tipo de recopilador de consultas T-SQL genérico. El conjunto de recopilación recoge los datos siguientes:

  • Instantáneas de los tamaños de los archivos de datos obtenidas de las vistas sys.partitions y sys.allocation_units.

  • Instantáneas de los tamaños de los archivos de registro obtenidas del comando DBCC SQLPERF (LOGSPACE).

  • Instantáneas de estadísticas de E/S obtenidas de la función sys.dm_io_virtual_file_stats.

Las tablas siguientes proporcionan información detallada sobre el conjunto de recopilación Uso de disco y sus elementos de recopilación.

Nombre del conjunto de recopilación

Uso de disco

Modo de recopilación

No almacenado en memoria caché

Frecuencia de la programación de carga

Cada 6 horas

Retención de datos

730 días

Elementos de recopilación

Uso de disco - Archivos de datos

Uso de disco - Archivos de registro

Nombre del elemento de recopilación

Uso de disco - Archivos de datos

Tipo de recopilador

Consultas 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''

Resultado de la consulta 1

disk_usage

Nombre del elemento de recopilación

Uso de disco - Archivos de registro

Tipo de recopilador

Consultas 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

Resultado de la consulta 1

log_usage

Conjunto de recopilación Actividad del servidor

El conjunto de recopilación Actividad del servidor proporciona una visión general de la actividad de SQL Server, la utilización de recursos de SQL Server y la contención de recursos de SQL Server. El conjunto de recopilación también proporciona una vista global de la utilización de recursos del sistema que permite determinar si los problemas de rendimiento están relacionados con actividades que se encuentran fuera del ámbito de SQL Server.

Este conjunto de recopilación recoge muestras de datos de las vistas de administración dinámica siguientes:

  • sys.dm_os_wait_stats

  • sys.dm_os_latch_stats

  • sys.dm_os_schedulers

  • sys.dm_exec_sessions, sys.dm_exec_requests y sys.dm_os_waiting_tasks (usando una consulta combinada)

  • sys.dm_os_process_memory

  • sys.dm_os_memory_nodes

Además, se recogen ejemplos de datos de varios contadores de rendimiento de SQL Server y del sistema.

El conjunto de recopilación Actividad del servidor ofrece una vista global del sistema en lo referente a la utilización de recursos y cuellos de botella de recursos. El seguimiento del uso de recursos se realiza en cuatro áreas generales: CPU, E/S del disco, memoria y red. El muestreo de sys.dm_exec_sessions, sys.dm_exec_requests y sys.dm_os_waiting_tasks habilita la correlación de la actividad del sistema con los cuellos de botella de recursos y con los problemas de bloqueo.

Este conjunto de recopilación, que se ejecuta por sí solo, permite asociar los cuellos de botella de recursos a una sesión bloqueada y mostrar las cadenas de bloqueo en el nivel de sesión. Aunque no se recopilan los textos de las consultas, se puede utilizar la información de sql_handle y plan_handle recogida por el conjunto de recopilación Estadísticas de consultas para aumentar el detalle por debajo del nivel de sesión.

Las tablas siguientes proporcionan información detallada sobre el conjunto de recopilación Actividad del servidor y sus elementos de recopilación.

Nombre del conjunto de recopilación

Actividad del servidor

Modo de recopilación

Almacenado en memoria caché

Frecuencia de la programación de carga

Cada 15 minutos

Retención de datos

14 días

Elementos de recopilación

Actividad del servidor - Instantáneas de DMV

Actividad del servidor - Contadores de rendimiento

Nombre del elemento de recopilación

Actividad del servidor - Instantáneas de DMV

Tipo de recopilador

Consultas T-SQL genérico

Frecuencia de recopilación

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

Resultado de la 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

Resultado de la 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

Resultado de la 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

Resultado de la 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

Resultado de la 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

Resultado de la 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]

Resultado de la consulta 7

snapshots.io_virtual_file_stats

Nombre del elemento de recopilación

Actividad del servidor - Contadores de rendimiento

Tipo de recopilador

Contadores de rendimiento

Frecuencia de recopilación

60 segundos

Contadores de rendimiento utilizados

Contadores de "memoria"="% de bytes en uso confirmados"

Contadores de "memoria"="Bytes disponibles"

Contadores de "memoria"="Bytes en caché"

Contadores de "memoria"="Errores de caché/s"

Contadores de "memoria"="Bytes confirmados"

Contadores de "memoria"="Bytes de lista de páginas libres y cero"

Contadores de "memoria"="Bytes de lista de páginas modificadas"

Contadores de "memoria"="Páginas/s"

Contadores de "memoria"="Lecturas de página/s"

Contadores de "memoria"="Escrituras de página/s"

Contadores de "memoria"="Errores de página/s"

Contadores de "memoria"="Bytes de bloque no paginado"

Contadores de "memoria"="Bytes de bloque paginado"

Contadores de "memoria"="Bytes principales de caché en modo de espera"

Contadores de "memoria"="Bytes de prioridad normal de caché en modo de espera"

Contadores de "memoria"="Bytes de reserva de caché en modo de espera"

Contadores de “memoria"="Bytes de bloque paginado"

Contadores de "memoria"="Copias de escritura/s"

Contadores de "proceso"="*" Instancias="_Total"

Contadores de "proceso="*" Instancias="$(TARGETPROCESS)"

Contadores de "proceso"="Recuento de subprocesos" Instancias="*"

Contadores de "proceso"="% de tiempo de procesador" Instancias="*"

Contadores de "proceso"="Bytes de lectura de ES/s" Instancias="*"

Contadores de "proceso"="Bytes de escritura de ES/s" Instancias="*"

Contadores de "proceso"="Bytes privados" Instancias="*"

Contadores de "proceso"="Espacio de trabajo" Instancias="*"

Contadores de "procesador"="% de tiempo de procesador" Instancias="*"

Contadores de "procesador"="% de tiempo de usuario" Instancias="*"

Contadores de "procesador"="% de tiempo con privilegios" Instancias="*"

Contadores de "colas de trabajo de servidor"="Longitud de cola" Instancias="*"

Contadores de "LogicalDisk"="% de tiempo de disco" Instancias="*"

Contadores "LogicalDisk"="Avg. Disk Queue Length" Instances="*"

Contadores "LogicalDisk"="Avg. Disk Read Queue Length" Instances="*"

Contadores "LogicalDisk"="Avg. Disk Write Queue Length" Instances="*"

Contadores "LogicalDisk"="Avg. Disk sec/Read" Instances="*"

Contadores "LogicalDisk"="Avg. Disk sec/Write" Instances="*"

Contadores "LogicalDisk"="Avg. Disk sec/Transfer" Instances="*"

Contadores de "LogicalDisk"="Lecturas de disco/s" Instancias="*"

Contadores de "LogicalDisk"="Bytes de disco/s" Instancias="*"

Contadores de "LogicalDisk"="Escrituras en disco/s" Instancias="*"

Contadores de "LogicalDisk"="ES divididas/s" Instancias="*"

Contadores de "sistema"="Longitud de cola del procesador"

Contadores de "sistema"="Operaciones de lectura de archivo/s"

Contadores de "sistema"="Operaciones de escritura de archivo/s"

Contadores de "sistema"="Operaciones de control de archivo/s"

Contadores de "sistema"="Bytes de lectura de archivo/s"

Contadores de "sistema"="Bytes de escritura de archivo/s"

Contadores de "sistema"="Bytes de control de archivo/s"

Contadores de "interfaz de red"="Total de bytes/s" Instancias= "*"

Contadores de "interfaz de red"="Longitud de la cola de salida" Instancias="*"

Contadores de "SQLServer:Buffer Manager"="Páginas descartadas"

Contadores de "SQLServer:Buffer Manager"="Duración prevista de la página"

Contadores de "SQLServer:Memory Manager"="Concesiones de memoria otorgadas"

Contadores de "SQLServer:Memory Manager"="Concesiones de memoria pendientes"

Contadores de "SQLServer:Databases"="Transacciones/s" Instancias="_Total"

Contadores de "SQLServer:Databases"="Transacciones/s" Instancias="tempdb"

Contadores de "SQLServer:Databases"="Transacciones activas" Instancias="*"

Contadores de "SQLServer:General Statistics"="Inicios de sesión/s"

Contadores de "SQLServer:General Statistics"="Cierres de sesión/s"

Contadores de "SQLServer:General Statistics"="Inicios de sesión/s"

Contadores de "SQLServer:General Statistics"="Conexiones lógicas"

Contadores de "SQLServer:General Statistics"="Transacciones"

Contadores de "SQLServer:General Statistics"="Procesos bloqueados"

Contadores de "SQLServer:General Statistics"="Tablas temporales activas"

Contadores de " SQLServer:SQL Statistics "="Solicitudes de lotes/s"

Contadores de "SQLServer:SQL Statistics"="Compilaciones SQL/s"

Contadores de "SQLServer:SQL Statistics"="Recompilaciones SQL/s"

Contadores de "SQLServer:SQL Statistics"="Velocidad de atención de SQL"

Contadores de "SQLServer:SQL Statistics"="Intentos de parametrización automática/s"

Contadores de "SQLServer:SQL Statistics"="Parametrizaciones automáticas con error/s"

Contadores de "SQLServer:Plan Cache"="Frecuencia de aciertos de caché" Instancias="_Total"

Contadores de "SQLServer:Plan Cache"="Frecuencia de aciertos de caché" Instancias="Planes de objetos"

Contadores de "SQLServer:Plan Cache"="Frecuencia de aciertos de caché" Instancias="Planes SQL"

Contadores de "SQLServer:Plan Cache"="Frecuencia de aciertos de caché" Instancias="Tablas temporales y Variables de tabla"

Contadores de "SQLServer:Transactions"="Espacio disponible en tempdb (KB)"

Contadores de "SQLServer:Workload Group Stats"="Solicitudes activas" Instancias="*"

Contadores de "SQLServer:Workload Group Stats"="Tareas bloqueadas" Instancias="*"

Contadores de "SQLServer:Workload Group Stats"="Uso de la CPU (%)" Instancias="*"

Conjunto de recopilación Estadísticas de consultas

El conjunto de recopilación Estadísticas de consultas recoge datos sobre estadísticas de consultas, textos de consultas individuales, planes de consulta y consultas específicas. Estos datos, cuando se vinculan a estadísticas y actividades de nivel de sistema, permiten aumentar el detalle por debajo del nivel de sesión hasta una consulta individual.

Este conjunto de recopilación recoge datos de los orígenes siguientes:

  • sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats y otras vistas de administración dinámica relacionadas.

  • El texto de los lotes y las consultas seleccionados.

  • El plan de los lotes y las consultas seleccionados.

  • El texto normalizado de los lotes seleccionados.

El conjunto de recopilación Estadísticas de consultas usa el tipo de recopilador Actividad de consultas. El tipo de recopilador Actividad de consultas recoge datos usando el paquete QueryActivityCollect.dtsx de SSIS y carga datos usando el paquete QueryActivityUpload.dtsx de SSIS. Para obtener más información sobre las fases de recopilación y carga del tipo de recopilador Actividad de consultas, incluidas las consultas que se usan, vea Tipo de recopilador Actividad de consulta.

Las tablas siguientes proporcionan información sobre el conjunto de recopilación Estadísticas de consultas y su elemento de recopilación.

Nombre del conjunto de recopilación

Estadísticas de consultas

Modo de recopilación

Almacenado en memoria caché

Frecuencia de la programación de carga

Cada 15 minutos

Retención de datos

14 días

Elemento de recopilación

Estadísticas de consultas - Actividad de consultas