Configure using OpenTelemetry Collector Contribution for Microsoft SQL Server
You can use the OpenTelemetry Collector Contribution for Microsoft SQL Server to monitor your instance. The OpenTelemetry Collector Contribution is a community-supported project that provides a way to collect telemetry data from Microsoft SQL Server and send it to New Relic.
By default, the OpenTelemetry Collector Contribution collects metrics from Microsoft SQL Server and sends them to New Relic. However, these are limited metrics. In order to send query performance metrics, wait-time metrics, session metrics, and other advanced metrics, you will need to configure the OpenTelemetry Collector Contribution. This guide provides instructions for configuring the OpenTelemetry Collector Contribution to collect advanced metrics from Microsoft SQL Server and send them to New Relic.
Prerequisites
OpenTelemetry Collector Contrib (otelcol-contrib) installed & configured on your system. You can download the latest release from the OpenTelemetry Collector releases page.
Enable UI metrics
You can enrich your New Relic platform experience by adding and enabling the various metrics in your otelcol-contrib configuration file. Using the configuration provided in this section, you can populate data for following pages in the New Relic platform:
Overview
Query performance
Wait events
Sessions
Clients
The Overview page displays a high-level view of your Microsoft SQL Server instance, including key metrics such as database count, I/O, latency, operations, tempdb space, deadlock rate, and more.
To populate data for the Overview page in the New Relic platform, add the following configuration to your otelcol-contrib configuration file:
receivers:
sqlserver:
# Enable comprehensive database metrics
metrics:
sqlserver.database.count:
enabled:true
sqlserver.database.io:
enabled:true
sqlserver.database.latency:
enabled:true
sqlserver.database.operations:
enabled:true
sqlserver.database.tempdb.space:
enabled:true
sqlserver.database.tempdb.version_store.size:
enabled:true
sqlserver.deadlock.rate:
enabled:true
sqlserver.os.wait.duration:
enabled:true
sqlserver.processes.blocked:
enabled:true
sqlserver.memory.grants.pending.count:
enabled:true
sqlserver.memory.area:
enabled:true
You can populate data for the following pages in the New Relic platform:
Query performance: Displays detailed information about the queries running on your Microsoft SQL Server instance in following tabs:
Normalized queries: Shows aggregated stats per query including executions, avg duration, avg CPU time, and logical reads. Click a query to view detailed information for the selected query, including aggregate performance stats, observed query plans, and recent individual samples with wait-type breakdown.
Query samples: Shows individual query executions including start time, duration, wait type, wait time, database, DB user, APM correlation, and client host.
Wait events: Displays information about the wait events occurring on your Microsoft SQL Server instance. The Query samples view shows queries currently waiting along with their wait type and wait time.
Sessions: Displays information about the sessions connected to your Microsoft SQL Server instance, including session counts, session states, avg active session duration, blocked sessions, and individual session details.
Clients: Displays information about the clients connected to your Microsoft SQL Server instance, including active sessions, blocked sessions, connected DB users, and client host details.
To populate data in these pages, add the following configuration to your otelcol-contrib configuration file:
receivers:
sqlserver:
events:
db.server.query_sample:
enabled:true
db.server.top_query:
enabled:true
top_query_collection:
lookback_time: 45s
max_query_sample_count:100
top_query_count:30
collection_interval: 30s
query_sample_collection:
max_rows_per_query:100
The All waits view shows instance-level wait time broken down by wait category and individual wait type.
To enable All waits in the New Relic platform Wait event tab, add the following configuration to your otelcol-contrib configuration file:
receivers:
sqlserver:
metrics:
sqlserver.os.wait.duration:
enabled:true
Restart the OpenTelemetry Collector
Restart the OpenTelemetry Collector to apply the configuration changes. You can do this by running the following command: