• Article

Jeux d'éléments de collecte de données système

Le collecteur de données installe trois jeux d'éléments de collecte de données système au cours du processus d'installation de SQL Server 2008. Ces jeux d'éléments de collecte peuvent être configurés pour satisfaire à vos spécifications d'analyse mais ne peuvent pas être supprimés. Les jeux d'éléments de collecte de données système se composent des éléments suivants :

  • Utilisation du disque. Collecte des données sur l'utilisation du disque et du journal pour toutes les bases de données installées sur le système.

  • Activité du serveur. Collecte des statistiques d'utilisation des ressources et des données de performances à partir du serveur et de SQL Server.

  • Statistiques de requête. Collecte des statistiques sur les requêtes, le texte des requêtes individuelles, des plans de requête et des requêtes spécifiques.

Jeu d'éléments de collection Utilisation du disque

Le jeu d'éléments de collection Utilisation du disque effectue le suivi de la croissance des fichiers de base de données et des fichiers journaux et fournit des statistiques liées aux fichiers, telles que la croissance moyenne (en mégaoctets) par jour.

Ce jeu d'éléments de collecte comprend deux éléments de collecte : Utilisation du disque - Fichiers de données et Utilisation du disque - Fichiers journaux. Tous deux utilisent le type de collecteur Requête T-SQL générique. Ce jeu d'éléments de collecte rassemble les données suivantes :

  • Captures instantanées des tailles des fichiers de données obtenues à partir des vues sys.partitions et sys.allocation_units.

  • Captures instantanées des tailles des fichiers journaux obtenues à partir de la commande DBCC SQLPERF (LOGSPACE).

  • Captures instantanées des statistiques d'E/S issues de la fonction sys.dm_io_virtual_file_stats.

Les tableaux ci-dessous fournissent des informations détaillées sur le jeu d'éléments de collecte Utilisation du disque et ses éléments de collecte.

Nom du jeu d'éléments de collecte

Utilisation du disque

Mode de collecte

Aucune mise en cache

Fréquence de planification des téléchargements

Toutes les 6 heures

Rétention des données

730 jours

Éléments de collecte

Utilisation du disque - Fichiers de données

Utilisation du disque - Fichiers journaux

Nom de l'élément de collecte

Utilisation du disque - Fichiers de données

Type de collecteur

Requête T-SQL générique

Requête 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''

Sortie de Requête 1

disk_usage

Nom de l'élément de collecte

Utilisation du disque - Fichiers journaux

Type de collecteur

Requête T-SQL générique

Requête 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

Sortie de Requête 1

log_usage

Jeu d'éléments de collecte Activité du serveur

Le jeu d'éléments de collecte Activité du serveur fournit une vue d'ensemble de l'activité de SQL Server, de l'utilisation des ressources SQL Server et des conflits de ressources SQL Server. Le jeu d'éléments de collecte fournit également une vue encapsulée de l'utilisation globale des ressources système qui vous permet de déterminer si les problèmes de performances sont liés à des activités situées au-delà de l'étendue de SQL Server.

Ce jeu d'éléments de collecte recueille des échantillons de données provenant des vues de gestion dynamique suivantes :

  • 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 (à l'aide d'une requête jointe)

  • sys.dm_os_process_memory

  • sys.dm_os_memory_nodes

En outre, les échantillons de données sont recueillis à partir de plusieurs compteurs de performance SQL Server et système.

Le jeu d'éléments de collecte Activité du serveur vous donne une vue globale du système en termes d'utilisation des ressources et de goulots d'étranglement des ressources. L'utilisation des ressources est suivie dans quatre domaines généraux : l'UC, les E/S sur le disque, la mémoire et le réseau. L'échantillonnage de sys.dm_exec_sessions, sys.dm_exec_requests et sys.dm_os_waiting_tasks permet la corrélation d'une activité système avec des goulots d'étranglement de ressources et avec des problèmes de blocage.

Exécuté de manière autonome, ce jeu d'éléments de collecte vous permet d'associer des goulots d'étranglement de ressources à une session bloquée et d'afficher les chaînes de blocage au niveau de la session. Bien que les textes des requêtes ne soient pas collectés, vous pouvez utiliser les informations sql_handle et plan_handle collectées par le jeu d'éléments de collecte Statistiques sur les requêtes pour descendre dans la hiérarchie sous le niveau session.

Les tableaux ci-dessous fournissent des informations détaillées sur le jeu d'éléments de collecte Activité du serveur et ses éléments de collecte.

Nom du jeu d'éléments de collecte

Activité du serveur

Mode de collecte

Mise en cache

Fréquence de planification des téléchargements

Toutes les 15 minutes

Rétention des données

14 jours

Éléments de collecte

Activité du serveur - Captures instantanées DMV

Activité du serveur - Compteurs de performance

Nom de l'élément de collecte

Activité du serveur - Captures instantanées DMV

Type de collecteur

Requête T-SQL générique

Fréquence de collecte

60 secondes

Requête 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

Sortie de Requête 1

snapshots.os_wait_stats

Requête 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

Sortie de Requête 2

snapshots.os_latch_stats

Requête 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

Sortie de Requête 3

snapshots.sql_process_and_system_memory

Requête 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

Sortie de Requête 4

snapshots.os_memory_nodes

Requête 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

Sortie de Requête 5

snapshots.os_memory_clerks

Requête 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

Sortie de Requête 6

snapshots.os_schedulers

Requête 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]

Sortie de Requête 7

snapshots.io_virtual_file_stats

Nom de l'élément de collecte

Activité du serveur - Compteurs de performance

Type de collecteur

Compteurs de performance

Fréquence de collecte

60 secondes

Compteurs de performance utilisés

"Mémoire" Counters="% octets validés en cours d'utilisation"

"Mémoire" Counters="Octets disponibles"

"Mémoire" Counters="Octets du cache"

"Mémoire" Counters="Défauts de cache/s"

"Mémoire" Counters="Octets validés"

"Mémoire" Counters="Octets des listes de pages vides et pages de zéros"

"Mémoire" Counters="Octets de la liste des pages modifiées"

"Mémoire" Counters="Pages/s"

"Mémoire" Counters="Lectures de pages/s"

"Mémoire" Counters="Écritures de pages/s"

"Mémoire" Counters="Défauts de page/s"

"Mémoire" Counters="Octets de réserve non paginée"

"Mémoire" Counters="Octets de réserve paginée"

"Mémoire" Counters="Octets de base du cache en attente"

"Mémoire" Counters="Octets du cache en attente de priorité normale"

"Mémoire" Counters="Octets de réserve du cache en attente"

"Mémoire" Counters="Octets de réserve paginée"

"Mémoire" Counters="Copies pour écriture/s"

"Processus" Counters="*" Instances="_Total"

"Processus" Counters="*" Instances="$(TARGETPROCESS)"

"Processus" Counters="Nombre de threads" Instances="*"

"Processus" Counters="% Temps processeur" Instances="*"

"Processus" Counters="Nombre d'octets lus/s" Instances="*"

"Processus" Counters="Nombre d'octets écrits/s" Instances="*"

"Processus" Counters="Octets privés" Instances="*"

"Processus" Counters="Plage de travail" Instances="*"

"Processeur" Counters="% temps processeur" Instances="*"

"Processeur" Counters="% temps utilisateur" Instances="*"

"Processeur" Counters="% temps privilégié" Instances="*"

"Files de travail du serveur" Counters="Longueur de la file" Instances="*"

"Disque logique" Counters="Pourcentage du temps disque" Instances="*"

"Disque logique" Counters="Longueur moyenne de file d'attente du disque" Instances="*"

"Disque logique" Counters="Longueur moyenne de file d'attente lecture disque" Instances="*"

"Disque logique" Counters="Longueur moyenne de file d'attente écriture disque" Instances="*"

"Disque logique" Counters="Moyenne disque s/lecture" Instances="*"

"Disque logique" Counters="Moyenne disque s/écriture" Instances="*"

"Disque logique" Counters="Moyenne disque s/transfert" Instances="*"

"Disque logique" Counters="Lectures disque/s" Instances="*"

"Disque logique" Counters="Octets disque/s" Instances="*"

"Disque logique" Counters="Écritures disque/s" Instances="*"

"Disque logique" Counters="E/S partagées/s" Instances="*"

"Système" Counters="Longueur de la file d'attente du processeur"

"Système" Counters="Opérations de lecture de fichier/s"

"Système" Counters="Opérations d'écriture de fichier/s"

"Système" Counters="Opérations de contrôle de fichier/s"

"Système" Counters="Octets lus sur fichier/s"

"Système" Counters="Octets écrits sur fichier/s"

"Système" Counters="Octets de contrôle de fichier/s"

"Interface réseau" Counters="Total des octets/s" Instances="*"

"Interface réseau" Counters="Longueur de la file d'attente de sortie" Instances="*"

"SQLServer : Gestionnaire de tampons" Counters="Pages occultées"

"SQLServer : Gestionnaire de tampons" Counters="Espérance de vie d'une page"

"SQLServer : Gestionnaire de mémoire" Counters="Demandes de mémoire satisfaites"

"SQLServer : Gestionnaire de mémoire" Counters="Demandes de mémoire en attente"

"SQLServer : Bases de données" Counters="Transactions/s" Instances="_Total"

"SQLServer : Bases de données" Counters="Transactions/s" Instances="tempdb"

"SQLServer : Bases de données" Counters="Transactions actives" Instances="*"

"SQLServer : Statistiques générales" Counters="Connexions/s"

"SQLServer : Statistiques générales" Counters="Déconnexions/s"

"SQLServer : Statistiques générales" Counters="Connexions utilisateur"

"SQLServer : Statistiques générales" Counters="Connexions logiques"

"SQLServer : Statistiques générales" Counters="Transactions"

"SQLServer : Statistiques générales" Counters="Processus bloqués"

"SQLServer : Statistiques générales" Counters="Tables temporaires actives"

"SQLServer : Statistiques SQL" Counters="Nombre de requêtes de lots/s"

"SQLServer : Statistiques SQL" Counters="Compilations SQL/s"

"SQLServer : Statistiques SQL" Counters="Recompilations SQL/s"

"SQLServer : Statistiques SQL" Counters="Taux d'avertissements SQL"

"SQLServer : Statistiques SQL" Counters="Nombre de tentatives d'autoparamétrage/s"

"SQLServer : Statistiques SQL" Counters="Nombre d'échecs d'autoparamétrage/s"

"SQLServer : Cache du plan" Counters="Taux d'accès au cache" Instances="_Total"

"SQLServer : Cache du plan" Counters="Taux d'accès au cache" Instances="Plans d'objets"

"SQLServer : Cache du plan" Counters="Taux d'accès au cache" Instances="Plans SQL"

"SQLServer : Cache du plan" Counters="Taux d'accès au cache" Instances="Tables temporaires & variables de table"

"SQLServer :Transactions" Counters="Espace disponible dans tempdb (Ko)"

"SQLServer : Statistiques des groupes de charges de travail" Counters="Requêtes actives" Instances="*"

"SQLServer : Statistiques des groupes de charges de travail" Counters="Tâches bloquées" Instances="*"

"SQLServer : Statistiques des groupes de charges de travail" Counters="% d'utilisation de l'UC" Instances="*"

Jeu d'éléments de collecte Statistiques sur les requêtes

Le jeu d'éléments de collecte Statistiques sur les requêtes rassemble des données sur des statistiques de requête et le texte des requêtes individuelles, des plans de requête et des requêtes spécifiques. Ces données, lorsqu'elles sont liées à des statistiques et des activités de niveau système, vous permettent de descendre dans la hiérarchie sous le niveau session jusqu'à une requête individuelle.

Ce jeu d'éléments de collecte regroupe des données provenant des sources suivantes :

  • sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats et d'autres vues de gestion dynamique associées ;

  • texte des lots et requêtes sélectionnés ;

  • plan des lots et requêtes sélectionnés ;

  • texte normalisé des lots sélectionnés.

Le jeu d'éléments de collecte Statistiques sur les requêtes utilise le type de collecteur Activité des requêtes. Le type de collecteur Activité des requêtes recueille les données à l'aide du package SSIS QueryActivityCollect.dtsx et les télécharge à l'aide du package SSIS QueryActivityUpload.dtsx. Pour plus d'informations sur les phases de collecte et de téléchargement du type de collecte Activité des requêtes, et notamment sur les requêtes utilisées, consultez Activité des requêtes, type de collecteur.

Les tableaux ci-dessous fournissent des informations sur le jeu d'éléments de collecte Statistiques sur les requêtes et son élément de collecte.

Nom du jeu d'éléments de collecte

Statistiques sur les requêtes

Mode de collecte

Mise en cache

Fréquence de planification des téléchargements

Toutes les 15 minutes

Rétention des données

14 jours

Élément de collecte

Statistiques sur les requêtes - Activité des requêtes

Historique des modifications

Mise à jour du contenu

Mise à jour de tous les tableaux qui présentent les détails des jeux d'éléments de collecte et des éléments de collecte avec les informations et le code appropriés.

Déplacement des informations sur les requêtes utilisées pour le jeu d'éléments de collecte Statistiques sur les requêtes vers la rubrique Activité des requêtes, type de collecteur.