Database Management

Querying Hourly CPU Utilization from the DPA Repository

This article explains how to retrieve hourly CPU utilization for a specific SQL Server monitored instance directly from the DPA repository database. It also explains how to discover other metrics available for data mining by using the monitor-specific CON_METRICS_NAMES_<DBID> table.

First published date

9/23/2026 9:11 PM

Last published date

9/23/2026 9:11 PM

Overview

This procedure applies to a DPA repository hosted on SQL Server and a monitored SQL Server instance.

DPA stores monitored-instance registration data in COND. The monitored-instance ID is stored in COND.ID, and the SQL Server instance name is stored in COND.INSTANCE_NAME. DPA creates monitor-specific metric tables by appending that ID to the table name, such as CON_METRICS_HOUR_123.

Product section

Database Performance Analyzer

Resolution

Note the following for each query below:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

Important considerations

Run the queries against the DPA repository database, not the monitored SQL Server instance.
The account needs read access to the DPA repository tables.
Replace the sample instance name and time range before execution.
The time values must use the time convention of the repository database.
DPA uses dynamic table names containing the monitor ID, so the queries use dynamic SQL.
The hourly metric table stores V_SUM, V_AVG, V_MIN, and V_MAX. For hourly CPU utilization, use V_AVG.
The examples use the dbo schema. If the repository tables are under another schema, replace dbo consistently in the queries.

Example Query: Hourly CPU utilization for one monitored instance

DECLARE @InstanceName nvarchar(100) = N'SERVER\INSTANCE';
DECLARE @StartTime    datetime = DATEADD(DAY, -7, GETDATE());
DECLARE @EndTime      datetime = GETDATE();

DECLARE @DbId smallint;
DECLARE @MatchCount int;

SELECT
    @DbId = MIN(ID),
    @MatchCount = COUNT(*)
FROM dbo.COND
WHERE F = 'Y'
  AND DB_TYPE = 'SQL Server'
  AND INSTANCE_NAME = @InstanceName;

IF @MatchCount = 0
    THROW 50000, 'No enabled SQL Server monitor matched @InstanceName.', 1;

IF @MatchCount > 1
    THROW 50001, 'Multiple enabled monitors matched @InstanceName.', 1;

DECLARE @MetricsTable sysname =
    N'CON_METRICS_' + CONVERT(varchar(10), @DbId);

DECLARE @MetricNamesTable sysname =
    N'CON_METRICS_NAMES_' + CONVERT(varchar(10), @DbId);

DECLARE @HourlyTable sysname =
    N'CON_METRICS_HOUR_' + CONVERT(varchar(10), @DbId);

IF OBJECT_ID(N'dbo.' + @HourlyTable, N'U') IS NULL
    THROW 50002, 'The hourly metrics table does not exist for this monitor.', 1;

DECLARE @Sql nvarchar(max) = N'
SELECT
    c.NAME          AS MonitoredInstance,
    c.INSTANCE_NAME AS InstanceName,
    c.ID            AS DpaDatabaseId,
    h.D             AS HourStart,
    CONVERT(decimal(10,2), h.V_AVG) AS CpuUtilizationPercent
FROM dbo.COND AS c
INNER JOIN dbo.' + QUOTENAME(@MetricNamesTable) + N' AS mn
    ON mn.NAME = N''Instance CPU Utilization''
INNER JOIN dbo.' + QUOTENAME(@MetricsTable) + N' AS m
    ON m.METRIC_NAME_ID = mn.ID
INNER JOIN dbo.' + QUOTENAME(@HourlyTable) + N' AS h
    ON h.METRICS_ID = m.ID
WHERE c.ID = @DbId
  AND h.D >= @StartTime
  AND h.D <  @EndTime
ORDER BY h.D;';

EXEC sys.sp_executesql
    @Sql,
    N'@DbId smallint, @StartTime datetime, @EndTime datetime',
    @DbId = @DbId,
    @StartTime = @StartTime,
    @EndTime = @EndTime;

How the query works

It finds the enabled monitor in COND using INSTANCE_NAME and DB_TYPE.
It obtains the DPA monitor ID from COND.ID.
It constructs the corresponding monitor-specific table names.
It resolves Instance CPU Utilization through CON_METRICS_NAMES_<DBID>.
It joins the metric definition to CON_METRICS_HOUR_<DBID>.
It returns one row per stored hour using V_AVG.

Finding the DPA monitor ID

If the instance name is not known, use this query to list enabled SQL Server monitors:

SELECT
    ID,
    NAME AS DisplayName,
    INSTANCE_NAME,
    CONN_HOST,
    CONN_PORT,
    DB_TYPE,
    STATUS
FROM dbo.COND
WHERE F = 'Y'
  AND DB_TYPE = 'SQL Server'
ORDER BY NAME;

The ID returned by this query is the suffix used by that monitor's metric tables. For example, monitor ID 123 uses:

CON_METRICS_NAMES_123
CON_METRICS_123
CON_METRICS_DETAIL_123
CON_METRICS_TEN_MINUTE_123
CON_METRICS_HOUR_123
CON_METRICS_DAY_123

Discovering available metrics

List all metric names for a monitor

Use the monitor ID obtained from COND.ID:

DECLARE @DbId smallint = 123;
DECLARE @Sql nvarchar(max);

SET @Sql = N'
SELECT
    ID AS MetricNameId,
    NAME AS MetricName
FROM dbo.' + QUOTENAME(N'CON_METRICS_NAMES_' + CONVERT(varchar(10), @DbId)) + N'
ORDER BY NAME;';

EXEC sys.sp_executesql @Sql;

This is the primary lookup for identifying metric names that can be data mined for that monitored instance.

Search for CPU, memory, disk, or other metrics

DECLARE @DbId smallint = 123;
DECLARE @SearchText nvarchar(100) = N'%CPU%';
DECLARE @Sql nvarchar(max);

SET @Sql = N'
SELECT
    ID AS MetricNameId,
    NAME AS MetricName
FROM dbo.' + QUOTENAME(N'CON_METRICS_NAMES_' + CONVERT(varchar(10), @DbId)) + N'
WHERE NAME LIKE @SearchText
ORDER BY NAME;';

EXEC sys.sp_executesql
    @Sql,
    N'@SearchText nvarchar(100)',
    @SearchText = @SearchText;

Example search patterns:

%CPU%
%Memory%
%Disk%
%IO%
%Wait%
%Lock%
%Connection%

Metric availability varies by database type, DPA version, monitor configuration, and whether the metric has been collected for the selected retention period. Use the results returned for the specific monitor rather than assuming that a metric is available.

Inspecting the metric series behind a metric name

CON_METRICS_NAMES_<DBID> provides the human-readable metric name. CON_METRICS_<DBID> maps that name to the stored metric series. The QUERY_ID and BRANCH_NAME_ID columns help distinguish multiple series when a metric name returns more than one row.

DECLARE @DbId smallint = 123;
DECLARE @MetricSearch nvarchar(200) = N'%CPU%';
DECLARE @Sql nvarchar(max);

SET @Sql = N'
SELECT
    m.ID AS MetricsId,
    n.ID AS MetricNameId,
    n.NAME AS MetricName,
    m.QUERY_ID AS QueryId,
    m.BRANCH_NAME_ID AS BranchNameId
FROM dbo.' + QUOTENAME(N'CON_METRICS_' + CONVERT(varchar(10), @DbId)) + N' AS m
INNER JOIN dbo.' + QUOTENAME(N'CON_METRICS_NAMES_' + CONVERT(varchar(10), @DbId)) + N' AS n
    ON n.ID = m.METRIC_NAME_ID
WHERE n.NAME LIKE @MetricSearch
ORDER BY n.NAME, m.QUERY_ID, m.BRANCH_NAME_ID;';

EXEC sys.sp_executesql
    @Sql,
    N'@MetricSearch nvarchar(200)',
    @MetricSearch = @MetricSearch;

Use MetricsId when querying a specific series from the detail or summarized metric tables.

Generic hourly query for another metric

After selecting a metric name from CON_METRICS_NAMES_<DBID>, substitute it for @MetricName below:

DECLARE @DbId smallint = 123;
DECLARE @MetricName nvarchar(200) = N'Memory Utilization';
DECLARE @StartTime datetime = DATEADD(DAY, -7, GETDATE());
DECLARE @EndTime datetime = GETDATE();
DECLARE @Sql nvarchar(max);

SET @Sql = N'
SELECT
    n.NAME AS MetricName,
    h.D AS HourStart,
    CONVERT(decimal(19,4), h.V_AVG) AS MetricValue,
    h.V_MIN AS MinimumValue,
    h.V_MAX AS MaximumValue
FROM dbo.' + QUOTENAME(N'CON_METRICS_' + CONVERT(varchar(10), @DbId)) + N' AS m
INNER JOIN dbo.' + QUOTENAME(N'CON_METRICS_NAMES_' + CONVERT(varchar(10), @DbId)) + N' AS n
    ON n.ID = m.METRIC_NAME_ID
INNER JOIN dbo.' + QUOTENAME(N'CON_METRICS_HOUR_' + CONVERT(varchar(10), @DbId)) + N' AS h
    ON h.METRICS_ID = m.ID
WHERE n.NAME = @MetricName
  AND h.D >= @StartTime
  AND h.D <  @EndTime
ORDER BY h.D;';

EXEC sys.sp_executesql
    @Sql,
    N'@MetricName nvarchar(200), @StartTime datetime, @EndTime datetime',
    @MetricName = @MetricName,
    @StartTime = @StartTime,
    @EndTime = @EndTime;

For example, Transaction Rate can be used as @MetricName to retrieve the hourly transactions-per-second series when that metric is present for the selected monitor.

Choosing the appropriate resolution

DPA stores metric data at multiple resolutions:

Table pattern	Typical use
`CON_METRICS_DETAIL_<DBID>`	Detailed collection intervals
`CON_METRICS_TEN_MINUTE_<DBID>`	Ten-minute trend analysis
`CON_METRICS_HOUR_<DBID>`	Hourly reporting and data mining
`CON_METRICS_DAY_<DBID>`	Long-term daily reporting

For hourly CPU utilization, use CON_METRICS_HOUR_<DBID> and V_AVG. For short-lived spikes, use the detail or ten-minute table when that data is available.

Troubleshooting

No monitor matched

Verify the exact value in COND.INSTANCE_NAME. The display name in COND.NAME may not be the same as the SQL Server instance name.

The hourly table does not exist

Confirm that the monitor ID is correct and that the monitor has completed registration. Also verify that the query is being run in the DPA repository database.

No rows returned for a valid metric

Possible causes include:

The metric was not enabled or collected for that monitor.
The requested dates are outside the retained data window.
The selected metric name exists in CON_METRICS_NAMES_<DBID> but has no corresponding rows in the hourly table for the requested period.
The requested time range does not match the repository timestamp convention.

Multiple rows per hour

Use the metric-series inspection query to review MetricsId, QueryId, and BranchNameId. If multiple series exist for one display name, select the intended MetricsId explicitly rather than relying only on the metric name.

Related repository objects

COND — monitored database registration and connection metadata.
CON_METRICS_NAMES_<DBID> — human-readable metric names for one monitor.
CON_METRICS_<DBID> — metric-series mapping and query/branch identifiers.
CON_METRICS_DETAIL_<DBID> — detailed metric values.
CON_METRICS_TEN_MINUTE_<DBID> — ten-minute summaries.
CON_METRICS_HOUR_<DBID> — hourly summaries.
CON_METRICS_DAY_<DBID> — daily summaries.

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment. You elect to use third-party content at your own risk, and you will be solely responsible for the incorporation of the same if any.