Condividi tramite

  • Articolo

Insiemi di raccolta dati di sistema

Durante il processo di installazione di SQL Server 2008 l'agente di raccolta dati installa tre insiemi di raccolta dati di sistema. Questi insiemi di raccolta possono essere configurati in base ai requisiti di monitoraggio, ma non possono essere eliminati. Gli insiemi di raccolta dati di sistema sono costituiti dagli elementi seguenti:

  • Utilizzo disco. Consente di raccogliere dati sull'utilizzo del disco e del log per tutti i database installati sul sistema.

  • Attività server. Consente di raccogliere le statistiche sull'utilizzo delle risorse e i dati relativi alle prestazioni del server e del SQL Server.

  • Statistiche query. Consente di raccogliere le statistiche delle query, il testo delle query singole, i piani delle query e query specifiche.

Insieme di raccolta Utilizzo disco

L'insieme di raccolta Utilizzo disco consente di registrare la crescita dei file di database e log e fornisce le statistiche correlate al file, ad esempio la crescita media (in megabyte) al giorno.

L'insieme di raccolta include due elementi di raccolta, ovvero Utilizzo disco - File di dati e Utilizzo disco - File di log. Entrambi prevedono l'utilizzo del tipo di agente di raccolta query T-SQL generico. L'insieme di raccolta consente di raccogliere i dati seguenti:

  • Snapshot delle dimensioni dei file di dati ottenuti dalle viste sys.partitions e sys.allocation_units.

  • Snapshot delle dimensioni dei file di log ottenuti dal comando DBCC SQLPERF (LOGSPACE).

  • Snapshot delle statistiche di I/O ottenuti dalla funzione sys.dm_io_virtual_file_stats.

Nelle tabelle seguenti sono fornite informazioni dettagliate sull'insieme di raccolta Utilizzo disco sui relativi elementi della raccolta.

Nome dell'insieme di raccolta

Utilizzo disco

Modalità di raccolta

Non in cache

Frequenza della pianificazione di caricamento

Ogni 6 ore

Mantenimento dei dati

730 giorni

Elementi della raccolta

Utilizzo disco - File di dati

Utilizzo disco - File di log

Nome dell'elemento della raccolta

Utilizzo disco - File di dati

Tipo di agente di raccolta

Query T-SQL generico

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

Output query 1

disk_usage

Nome dell'elemento della raccolta

Utilizzo disco - File di log

Tipo di agente di raccolta

Query T-SQL generico

Query 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

Output query 1

log_usage

Insieme di raccolta Attività server

L'insieme di raccolta Attività del server offre una panoramica dell'attività di SQL Server, dell'utilizzo delle risorse di SQL Server e della contesa tra risorse di SQL Server. Fornisce inoltre una visualizzazione incapsulata dell'utilizzo complessivo delle risorse di sistema, che consente di determinare se i problemi di prestazioni sono relativi ad attività esterne all'ambito di SQL Server.

Questo insieme di raccolta raccoglie dati di esempio dalle viste a gestione dinamica seguenti:

  • 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 (tramite una query unita in join)

  • sys.dm_os_process_memory

  • sys.dm_os_memory_nodes

Inoltre, i dati di esempio vengono raccolti da diversi contatori delle prestazioni di sistema e di SQL Server.

L'insieme di raccolta Attività server fornisce una panoramica del sistema in termini di utilizzo delle risorse e di colli di bottiglia delle stesse. L'utilizzo delle risorse viene registrato in quattro aree generali: CPU, I/O su disco, memoria e rete. Il campionamento di sys.dm_exec_sessions, sys.dm_exec_requests e sys.dm_os_waiting_tasks consente la correlazione dell'attività del sistema con i colli di bottiglia delle risorse e i problemi di blocco.

Se eseguito da solo, questo insieme di raccolta consente di associare i colli di bottiglia delle risorse a una sessione bloccata e di mostrare le catene di blocco a livello di sessione. Anche se i testi della query non vengono raccolti, è possibile utilizzare le informazioni relative a sql_handle e plan_handle raccolte dall'insieme di raccolta Statistiche query per eseguire il drill-down sotto il livello di sessione.

Nelle tabelle seguenti sono fornite informazioni dettagliate sull'insieme di raccolta Attività server e sui relativi elementi della raccolta.

Nome dell'insieme di raccolta

Attività server

Modalità di raccolta

In cache

Frequenza della pianificazione di caricamento

Ogni 15 minuti

Mantenimento dei dati

14 giorni

Elementi della raccolta

Attività server - Snapshot DMV

Attività server - Contatori delle prestazioni

Nome dell'elemento della raccolta

Attività server - Snapshot DMV

Tipo di agente di raccolta

Query T-SQL generico

Frequenza di raccolta

60 secondi

Query 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

Output query 1

snapshots.os_wait_stats

Query 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

Output query 2

snapshots.os_latch_stats

Query 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

Output query 3

snapshots.sql_process_and_system_memory

Query 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

Output query 4

snapshots.os_memory_nodes

Query 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

Output query 5

snapshots.os_memory_clerks

Query 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

Output query 6

snapshots.os_schedulers

Query 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'' --> ''\\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'' --> ''C:'') 
        WHEN CHARINDEX (''\'', LTRIM(f.physical_name), 3) > 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]

Output query 7

snapshots.io_virtual_file_stats

Nome dell'elemento della raccolta

Attività server - Contatori delle prestazioni

Tipo di agente di raccolta

Contatori delle prestazioni

Frequenza di raccolta

60 secondi

Contatori delle prestazioni utilizzati

Contatori "Memoria"="% di byte di cui è stato eseguito il commit in uso"

Contatori "Memoria"="Byte disponibili"

Contatori "Memoria"="Cache Bytes"

Contatori "Memoria"="Errori cache/sec"

Contatori "Memoria" ="Byte di cui è stato eseguito il commit"

Contatori "Memoria" ="Byte di pagine libere e azzerate"

Contatori "Memoria" ="Byte di pagine modificate"

Contatori "Memoria"="Pagine/sec"

Contatori "Memoria"="Letture di pagina/sec"

Contatori "Memoria"="Scrittura di pagina/sec"

Contatori "Memoria" ="Errori di pagina/sec"

Contatori "Memoria" ="Byte del pool non di paging"

Contatori "Memoria"="Byte del pool di paging"

Contatori "Memoria"="Byte di base cache di standby"

Contatori "Memoria" ="Byte a priorità normale cache di standby"

Contatori "Memoria"="Byte di riserva cache di standby"

Contatori "Memoria"="Byte del pool di paging"

Contatori "Memoria"="Scritture in copia/sec"

Contatori "Processo="*" Istanze="_Total"

Contatori "Processo="*" Istanze="$(TARGETPROCESS)"

Contatori "Processo"="Conteggio thread" Istanze="*"

Contatori "Processo" ="% tempo processore" Istanze="*"

Contatori "Processo"="Byte letti IO/sec" Istanze="*"

Contatori "Processo"=" Byte scritti IO/sec " Istanze="*"

Contatori "Processo"="Byte privati" Istanze="*"

Contatori "Processo"="Working set" Istanze="*"

Contatori "Processore"="% tempo processore" Istanze="*"

Contatori "Processore"="% tempo utente" Istanze="*"

Contatori "Processore"="% tempo privilegiato" Istanze="*"

Contatori "Code di lavoro del server"="Lunghezza coda" Istanze="*"

Contatori "Disco logico"="% tempo disco" Istanze="*"

Contatori "Disco logico" = "Lunghezza media coda del disco" Istanze="*"

Contatori "Disco logico" = "Lunghezza media lettura coda del disco" Istanze="*"

Contatori "Disco logico" = "Lunghezza media scrittura coda del disco" Istanze="*"

Contatori "Disco logico" = "Media letture disco/sec" Istanze= "*"

Contatori "Disco logico" = "Media scritture disco/sec" Istanze= "*"

Contatori "Disco logico" = "Media secondi/trasf. disco" Istanze= "*"

Contatori "Disco logico"="Letture disco/sec" Istanze="*"

Contatori "Disco logico"="Byte disco/sec" Istanze="*"

Contatori "Disco logico"="Scritture disco/sec" Istanze="*"

Contatori "Disco logico"="Suddivisione IO/sec" Istanze="*"

Contatori "Sistema"="Lunghezza coda processore"

Contatori "Sistema"="Operazioni lettura file/sec"

Contatori "Sistema"="Operazioni scrittura file/sec"

Contatoti "Sistema"="Operazioni controllo file/sec"

Contatori "Sistema"="Byte di file letti/sec"

Contatori "Sistema"="Byte di file scritti/sec"

Contatori "Sistema"=" Byte di controllo file/sec"

Contatori "Interfaccia di rete"="Totale byte/sec" Istanze="*"

Contatori "Interfaccia di rete"="Lunghezza coda di output" Istanze="*"

Contatori "SQLServer:Gestione buffer"="Pagine prelevate"

Contatori "SQLServer:Gestione buffer"="Permanenza presunta delle pagine"

Contatori "SQLServer:Gestione memoria"="Concessioni di memoria in attesa"

Contatori "SQLServer:Gestione memoria"="Concessioni di memoria in sospeso"

Contatori "SQLServer:Database"="Transazioni/sec" Istanze="_Total"

Contatori "SQLServer:Database"="Transazioni/sec" Istanze="tempdb"

Contatori "SQLServer:Database"="Transazioni attive" Istanze="*"

Contatori "SQLServer:Statistiche generali"="Accessi/sec"

Contatori "SQLServer:Statistiche generali"="Disconnessioni/sec"

Contatori "SQLServer:Statistiche generali"="Connessioni utente"

Contatori "SQLServer:Statistiche generali"="Connessioni logiche"

Contatori "SQLServer:Statistiche generali"="Transazioni"

Contatori "SQLServer:Statistiche generali"="Processi bloccati"

Contatori "SQLServer:Statistiche generali"="Tabelle temporanee attive"

Contatori "SQLServer:Statistiche SQL"="Richieste batch/sec"

Contatori "SQLServer:Statistiche SQL"="Compilazioni SQL/sec"

Contatori "SQLServer:Statistiche SQL"="Ricompilazioni SQL/sec "

Contatori "SQLServer:Statistiche SQL"="Frequenza situazioni di attenzione SQL"

Contatori "SQLServer:Statistiche SQL"="Tentativi parametrizzazioni automatiche/sec"

Contatori "SQLServer:Statistiche SQL"="Parametrizzazioni automatiche non riuscite/sec"

Contatori "SQLServer:Plan Cache"="Percentuali riscontri cache" Istanze="_Total"

Contatori "SQLServer:Plan Cache"="Percentuali riscontri cache" Istanze="Piani per gli oggetti"

Contatori "SQLServer:Plan Cache"="Percentuali riscontri cache" Istanze="Piani SQL"

Contatori "SQLServer:Plan Cache"="Percentuali riscontri cache" Istanze="Tabelle temporanee e variabili di tabella"

Contatori "SQLServer:Transazioni" = "Spazio disponibile in tempdb (KB)"

Contatori "SQLServer:Statistiche gruppi del carico di lavoro"="Richieste attive" Istanze="*"

Contatori "SQLServer:Statistiche gruppi del carico di lavoro"="Attività bloccate" Istanze="*"

Contatori "SQLServer:Statistiche gruppi del carico di lavoro"="% di utilizzo CPU" Istanze="*"

Insieme di raccolta Statistiche query

L'insieme di raccolta Statistiche query consente di raccogliere dati relativi alle statistiche delle query, nonché al testo di singole query, piani di query e query specifiche. Questi dati, una volta collegati alle statistiche e alle attività a livello di sistema, consentono di eseguire il drill-down sotto il livello di sessione fino a una query singola.

Questo insieme di raccolta raccoglie dati dalle fonti seguenti:

  • sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats e altre viste a gestione dinamica correlate.

  • Testo di batch e query selezionati.

  • Piano di batch e query selezionate.

  • Testo normalizzato di batch selezionati.

L'insieme di raccolta Statistiche query utilizza il tipo di agente di raccolta Attività query, che accoglie i dati tramite il pacchetto SSIS QueryActivityCollect.dtsx e li carica tramite il pacchetto SSIS QueryActivityUpload.dtsx. Per ulteriori informazioni sulle fasi di raccolta e caricamento del tipo di raccolta Attività query, nonché sulle query utilizzate, vedere Tipo di agente di raccolta Attività query.

Nelle tabelle seguenti vengono fornite informazioni dettagliate sull'insieme di raccolta Statistiche query e sul relativo elemento della raccolta.

Nome dell'insieme di raccolta

Statistiche query

Modalità di raccolta

In cache

Frequenza della pianificazione di caricamento

Ogni 15 minuti

Mantenimento dei dati

14 giorni

Elemento della raccolta

Statistiche query - Attività query

Cronologia modifiche

Aggiornamento del contenuto

Aggiornamento di tutte le tabelle contenenti i dettagli su insiemi di raccolta ed elementi della raccolta con informazioni e codice corretti.

Spostamento dei dettagli sulle query utilizzate per l'insieme di raccolta Statistiche query nell'argomento Tipo di agente di raccolta Attività query.