• Article

Utility Information Data Collection Set

The Utility Information data collection set is installed and automatically started on each instance of SQL Server 2008 R2 that is managed by a utility control point (UCP). The utility management data warehouse (UMDW) database where the data is stored is created on the UCP server during UCP creation.

The collected data is used by the UCP to display performance and configuration information, and to evaluate resource health policies. For more information, see Overview of SQL Server Utility.

Important

Because of its tight integration with SQL Server Utility Explorer, you should not modify this data collection set. For example, you should not change the Retain data for setting from its default value of 1 day. Data from the collection set is uploaded to the UMDW, where data from the 15 minutes uploads is then aggregated into one hour, and then daily aggregations. You must set the data retention interval for the SQL Server Utility through SQL Server Utility Explorer. This setting, available when you click Utility Administration, and then click the Data Warehouse tab, controls the retention interval for the aggregated data. For more information, see Utility Administration (SQL Server Utility).

The Utility Information data collection set has the following collection items:

  • Utility Information - DAC Information

  • Utility Information - SMO Information

  • Utility Information - Utility Allocated CPU Info

  • Utility Information - Utility CPU-Memory Related Info

  • Utility Information - Utility DatabaseFilesInfo

  • Utility Information - Utility Performance Counters Items

  • Utility Information - Utility Performance Counters Items1

  • Utility Information - Utility Volumes Information

The following tables provide detailed information about the Utility Information data collection set and its collection items.

Collection Set

Collection set name

Utility Information

Collection mode

Non-cached

Upload schedule frequency

Every 15 minutes

Data retention

Data retention for the Utility Information collection set is controlled through SQL Server Utility Explorer. For more information, see the "Important" note earlier in this topic.

Collection items

Utility Information - DAC Information

Utility Information - SMO Information

Utility Information - Utility Allocated CPU Info

Utility Information - Utility CPU-Memory Related Info

Utility Information - Utility DatabaseFilesInfo

Utility Information - Utility Performance Counters Items

Utility Information - Utility Performance Counters Items1

Utility Information - Utility Volumes Information

Collection Items

Collection item name

Utility Information - DAC Information

Collector type

Generic T-SQL Query

Query

 
SELECT
    machineName as machine_name,
    instance_name,
    dac_db,
    dac_deploy_date,
    dac_description,
    dac_name,
    dac_cpu,
    last_collection_time AS start_time
FROM [msdb].[dbo].[fn_sysutility_get_cpu_time_information]()

Query output

sysutility_dac_info_internal

Collection item name

Utility Information - SMO Information

Collector type

Generic T-SQL Query

Query

 
SELECT 
    [computer_name],
    [instance_name],
    [object_type],
    [urn],
    [property_name],
    [property_value]
FROM [msdb].[dbo].[sysutility_smo_properties_internal]

Query output

sysutility_smo_properties_internal

Collection item name

Utility Information - Utility Allocated CPU Info

Collector type

Generic T-SQL Query

Query

 
SELECT
    cpu_allocation_value,
    number_of_affinitized_cpus,
    instance_name
FROM [msdb].[dbo].[fn_sysutility_get_cpu_affinity_value]()

Query output

sysutility_cpu_affinity_value_internal

Collection item name

Utility Information - Utility CPU-Memory Related Info

Collector type

Generic T-SQL Query

Query

 
SELECT
    instance_name,
    is_clustered_server,
    physical_server_name,
    num_processors,
    cpu_caption,
    cpu_family,
    cpu_architecture,
    cpu_clock_speed,
    l2_cache_size,
    l3_cache_size
FROM [msdb].[dbo].[fn_sysutility_get_cpu_memory_related_info]()

Query output

sysutility_cpu_memory_related_info_internal

Collection item name

Utility Information - Utility DatabaseFilesInfo

Collector type

Generic T-SQL Query

Query

 
SELECT
    DB_ID() AS database_id,
    [files].file_id AS file_id,
    [files].name AS database_filename, 
    0 AS file_type,
    [files].physical_name AS full_path,
    CASE WHEN [files].type = 1 THEN 'Not Applicable' ELSE FILEGROUP_NAME([files].data_space_id) 
      END AS file_group_name, 
    [files].size AS file_size,
    [files].growth AS file_growth,
    [files].max_size AS file_max_size,
    [files].is_percent_growth AS file_is_percent_growth, 
    CONVERT(REAL,FILEPROPERTY([files].name, 'SpaceUsed')) as utilized_space,
    CONVERT(BIT, [files].growth) AS auto_grow, 
    filestats.NumberReads AS number_of_reads, 
    filestats.BytesRead AS bytes_read,
    filestats.NumberWrites AS number_of_writes,
    filestats.BytesWritten AS bytes_written,
    filestats.IoStallMS AS iostallms,
 
SELECT name FROM sys.servers WHERE server_id = 0) AS server_instance_name, 
    900 AS seconds_interval
FROM 
 [sys].[database_files] AS [files] LEFT OUTER JOIN [sys].[filegroups] as filegroups ON files.data_space_id = filegroups.data_space_id 
 INNER JOIN fn_virtualfilestats(DB_ID(),NULL) as filestats ON files.file_id = filestats.FileId

Query output

sysutility_database_file_info_internal

Collection item name

Utility Information - Utility Performance Counters Items

Collector type

Performance Counters

Performance counters used

  • \Processor \% Processor Time

  • \Process(sqlservr) \% Processor Time

  • \Memory \Available MBytes

Collection item name

Utility Information - Utility Performance Counters Items1

Collector type

Performance Counters

Performance counters used

  • \LogicalDisk \Avg. Disk sec/Transfer

  • \LogicalDisk \Disk Transfers/sec

Collection item name

Utility Information - Utility Volumes Information

Collector type

Generic T-SQL Query

Query

 
SELECT
    volume_name,
    dummy_name,
    volume_type,
    total_space_available,
    free_space,
    max_io_per_sec,
    server_name
FROM [msdb].[dbo].[fn_sysutility_volumes_details]()

Query output

sysutility_volumes_info_internal

See Also

Concepts

Overview of SQL Server Utility

Other Resources

Data Collection