Microsoft SQL Server monitoring with NRDOT

Preview

We're still working on this feature, but we'd love for you to try it out!

This feature is currently provided as part of a preview pursuant to our pre-release policies.

New Relic now empowers your team to monitor your Microsoft SQL Server database performance using New Relic Distribution of OpenTelemetry (NRDOT) with New Relic's database monitoring capabilities. This integration provides comprehensive insights into database metrics, query performance, and system health using the NRDOT collector.

This NRDOT-based approach complements our existing On-Host Integration (OHI) by leveraging OpenTelemetry standards for database monitoring, making it easier to integrate with your existing observability stack.

Prerequisites

Before you begin, ensure you have the following:

Valid New Relic license key
Supported database versions: SQL Server 2017 or later
SQL Server database setup:
- Administrative access to your SQL Server instance (sysadmin role or equivalent)
- Microsoft SQL Server that supports on-premises managed instances
- Network connectivity between the NRDOT collector and your SQL Server
- SQL Server Management Studio (SSMS) or sqlcmd utility
- Server hostname or IP address
- Port number (usually 1433) or custom port
- Windows domain or SQL Server authentication
- SSL preference
System requirements:
- Windows or Linux system to run the NRDOT collector
- Network connectivity to New Relic's OTLP endpoint

Set-up the NRDOT Collector for Microsoft SQL Server monitoring

You can use the NRDOT collector to monitor your Microsoft SQL Server databases. The NRDOT collector is a pre-configured distribution that includes New Relic-specific components.

To install & configure the NRDOT Collector, follow these steps:

Setup the NRDOT Collector

Install the collector based on your operating system and architecture.

Importante

It is recommended to install the NRDOT Collector on the same host as your SQL Server instance to view infra-level metrics for query performance monitoring.

Download and run the installer by pasting this URL into your browser to download the installer for New Relic's NRDOT Collector integration.
```
https://download.newrelic.com/database-monitoring/nrdot-collector/nrdot-collector-host-windows-amd64-latest.msi
```
Locate your downloaded file and double-click it to install the NRDOT Collector.

For AMD64 architecture:
- For Debian/Ubuntu-based distributions:
  bash
```
$curl -L -o nrdot-collector-host-linux-amd64.deb https://download.newrelic.com/database-monitoring/nrdot-collector/nrdot-collector-host-linux-amd64-latest.deb
$sudo dpkg -i nrdot-collector-host-linux-amd64.deb
```
- For RHEL/CentOS-based distributions:
  bash
```
$curl -L -o nrdot-collector-host-linux-amd64.rpm https://download.newrelic.com/database-monitoring/nrdot-collector/nrdot-collector-host-linux-amd64-latest.rpm 
$sudo rpm -ivh nrdot-collector-host-linux-amd64.rpm
```

For ARM64 architecture:

For Debian/Ubuntu-based distributions:

bash

$curl -L -o nrdot-collector-host-linux-arm64.deb https://download.newrelic.com/database-monitoring/nrdot-collector/nrdot-collector-host-linux-arm64-latest.deb
$sudo dpkg -i nrdot-collector-host-linux-arm64.deb

For RHEL/CentOS-based distributions:

bash

$curl -L -o nrdot-collector-host-linux-arm64.rpm https://download.newrelic.com/database-monitoring/nrdot-collector/nrdot-collector-host-linux-arm64-latest.rpm
$sudo rpm -ivh nrdot-collector-host-linux-arm64.rpm

Configure database user

Run the following script as a root user/sysadmin to create the newrelic monitoring user and grant global read permissions.

To create a newrelic monitoring user, run the following script using SQL Server Management Studio (SSMS):

USE [master];
GO
CREATE LOGIN [newrelic] WITH PASSWORD = '<PASSWORD>';
GO
-- Instance-level permissions
GRANT VIEW SERVER STATE TO [newrelic];
GRANT VIEW ANY DEFINITION TO [newrelic];
GRANT VIEW ANY DATABASE TO [newrelic];
GO
-- Grant read access privileges to all user databases
DECLARE @name SYSNAME;
DECLARE db_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
SELECT [name]
FROM [master].[sys].[databases]
WHERE [name] NOT IN ('master', 'msdb', 'tempdb', 'model', 'rdsadmin', 'distribution')
AND [state] = 0; -- Only online databases
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    PRINT 'Processing database: ' + @name;
    EXEC('USE [' + @name + '];
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''newrelic'')
    BEGIN
      CREATE USER [newrelic] FOR LOGIN [newrelic];
    END;
    GRANT VIEW DATABASE STATE TO [newrelic];
    GRANT VIEW DEFINITION TO [newrelic];
    ALTER ROLE db_datareader ADD MEMBER [newrelic];');
    PRINT 'Success: ' + @name;
  END TRY
  BEGIN CATCH
    PRINT 'Error on ' + @name + ': ' + ERROR_MESSAGE();
  END CATCH
  FETCH NEXT FROM db_cursor INTO @name;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

To create a newrelic monitoring user in Linux, create a file named nr-grant-permission.sql on your Linux instance with the following content:

USE [master];
GO
CREATE LOGIN [newrelic] WITH PASSWORD = '<PASSWORD>';
GO
-- Instance-level permissions
GRANT VIEW SERVER STATE TO [newrelic];
GRANT VIEW ANY DEFINITION TO [newrelic];
GRANT VIEW ANY DATABASE TO [newrelic];
GO
-- Grant read access privileges to all user databases
DECLARE @name SYSNAME;
DECLARE db_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
SELECT [name]
FROM [master].[sys].[databases]
WHERE [name] NOT IN ('master', 'msdb', 'tempdb', 'model', 'rdsadmin', 'distribution')
AND [state] = 0; -- Only online databases
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    PRINT 'Processing database: ' + @name;
    EXEC('USE [' + @name + '];
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''newrelic'')
    BEGIN
      CREATE USER [newrelic] FOR LOGIN [newrelic];
    END;
    GRANT VIEW DATABASE STATE TO [newrelic];
    GRANT VIEW DEFINITION TO [newrelic];
    ALTER ROLE db_datareader ADD MEMBER [newrelic];');
    PRINT 'Success: ' + @name;
  END TRY
  BEGIN CATCH
    PRINT 'Error on ' + @name + ': ' + ERROR_MESSAGE();
  END CATCH
  FETCH NEXT FROM db_cursor INTO @name;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

To execute the script, run the following command. Replace <Your_SA_Password> with your SQL Server administrator password:
bash
```
$sqlcmd -S localhost -U sa -P '<Your_SA_Password>' -C -i nr-grant-permission.sql
```

(Optional) To verify that the user was created successfully and has the correct permissions, run the following command. Replace <Your_SA_Password> with your SQL Server administrator password:

bash

$sqlcmd -S localhost -U sa -P '<Your_SA_Password>' -C -Q "SELECT sp.name AS [User], p.permission_name AS [Permission_Granted] FROM sys.server_permissions p JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id WHERE sp.name = 'newrelic';"

Expected Output:

User Permission_Granted
---------------------------------------------------------------------------------------------
<your_username> CONNECT SQL
<your_username> VIEW ANY DEFINITION
<your_username> VIEW ANY DATABASE
<your_username> VIEW SERVER STATE

This output confirms that your user has been created successfully with the required server-level permissions.

Configure NRDOT Collector

Replace your config.yaml with the below content:

Windows: C:\Program Files (x86)\NRDOT Collector Host\config.yaml
Linux: /etc/nrdot-collector-host/config.yaml
Importante
If you're an existing NRDOT Collector user, ensure to merge the above configuration with your existing config.yaml file. This includes adding the new receivers, processors, and exporters for MSSQL Database monitoring while retaining your current configurations.

# NRDOT Collector Configuration for New Relic SQL Server Integration
# SQL Server On-Premises/VM Configuration

extensions:
  health_check:

receivers:
  otlp:
    protocols:
      grpc:
      http:

  hostmetrics:
    # Default collection interval is 60s. Lower if you need finer granularity.
    collection_interval: 60s
    scrapers:
      cpu:
        metrics:
          system.cpu.time:
            enabled: false
          system.cpu.utilization:
            enabled: true
      load:
      memory:
        metrics:
          system.memory.utilization:
            enabled: true
      paging:
        metrics:
          system.paging.utilization:
            enabled: false
          system.paging.faults:
            enabled: false
      filesystem:
        metrics:
          system.filesystem.utilization:
            enabled: true
      disk:
        metrics:
          system.disk.merged:
            enabled: false
          system.disk.pending_operations:
            enabled: false
          system.disk.weighted_io_time:
            enabled: false
      network:
        metrics:
          system.network.connections:
            enabled: false
     # Uncomment to enable process metrics, which can be noisy but valuable.
     # processes:
     # process:
     #  metrics:
     #    process.cpu.utilization:
     #      enabled: true
     #    process.cpu.time:
     #      enabled: false

  filelog:
    include:
      - /var/log/alternatives.log
      - /var/log/cloud-init.log
      - /var/log/auth.log
      - /var/log/dpkg.log
      - /var/log/syslog
      - /var/log/messages
      - /var/log/secure
      - /var/log/yum.log

  newrelicsqlserver:
    hostname: "<YOUR_DB_HOST>"
    port: "<YOUR_DB_PORT>"
    username: "<USERNAME>"
    password: "<PASSWORD>"
    monitored_databases: []
    # timeout: 30s
    # collection_interval: 30s  
    # query_monitoring_fetch_interval: 15
    # query_monitoring_response_time_threshold: 100
    # query_monitoring_count_threshold: 30
    # interval_calculator_cache_ttl_minutes: 10

    # Core Metric Category Toggles - Enable/disable entire categories of metrics
    # enable_instance_metrics: true
    # enable_database_metrics: true
    # enable_user_connection_metrics: true
    # enable_wait_time_metrics: true
    # enable_failover_cluster_metrics: true
    # enable_database_principals_metrics: true
    # enable_database_role_membership_metrics: true
    # enable_security_metrics: true
    # enable_lock_metrics: true
    # enable_thread_pool_metrics: true
    # enable_tempdb_metrics: true
    # enable_database_buffer_metrics: true

processors:
  # group system.cpu metrics by cpu
  metricstransform:
    transforms:
      - include: system.cpu.utilization
        action: update
        operations:
          - action: aggregate_labels
            label_set: [ state ]
            aggregation_type: mean
      - include: system.paging.operations
        action: update
        operations:
          - action: aggregate_labels
            label_set: [ direction ]
            aggregation_type: sum

  # remove system.cpu metrics for states
  filter/exclude_cpu_utilization:
    metrics:
      datapoint:
        - 'metric.name == "system.cpu.utilization" and attributes["state"] == "interrupt"'
        - 'metric.name == "system.cpu.utilization" and attributes["state"] == "nice"'
        - 'metric.name == "system.cpu.utilization" and attributes["state"] == "softirq"'

  filter/exclude_memory_utilization:
    metrics:
      datapoint:
        - 'metric.name == "system.memory.utilization" and attributes["state"] == "slab_unreclaimable"'
        - 'metric.name == "system.memory.utilization" and attributes["state"] == "inactive"'
        - 'metric.name == "system.memory.utilization" and attributes["state"] == "cached"'
        - 'metric.name == "system.memory.utilization" and attributes["state"] == "buffered"'
        - 'metric.name == "system.memory.utilization" and attributes["state"] == "slab_reclaimable"'

  filter/exclude_memory_usage:
    metrics:
      datapoint:
        - 'metric.name == "system.memory.usage" and attributes["state"] == "slab_unreclaimable"'
        - 'metric.name == "system.memory.usage" and attributes["state"] == "inactive"'

  filter/exclude_filesystem_utilization:
    metrics:
      datapoint:
        - 'metric.name == "system.filesystem.utilization" and attributes["type"] == "squashfs"'

  filter/exclude_filesystem_usage:
    metrics:
      datapoint:
        - 'metric.name == "system.filesystem.usage" and attributes["type"] == "squashfs"'
        - 'metric.name == "system.filesystem.usage" and attributes["state"] == "reserved"'

  filter/exclude_filesystem_inodes_usage:
    metrics:
      datapoint:
        - 'metric.name == "system.filesystem.inodes.usage" and attributes["type"] == "squashfs"'
        - 'metric.name == "system.filesystem.inodes.usage" and attributes["state"] == "reserved"'

  filter/exclude_system_disk:
    metrics:
      datapoint:
        - 'metric.name == "system.disk.operations" and IsMatch(attributes["device"], "^loop.*") == true'
        - 'metric.name == "system.disk.merged" and IsMatch(attributes["device"], "^loop.*") == true'
        - 'metric.name == "system.disk.io" and IsMatch(attributes["device"], "^loop.*") == true'
        - 'metric.name == "system.disk.io_time" and IsMatch(attributes["device"], "^loop.*") == true'
        - 'metric.name == "system.disk.operation_time" and IsMatch(attributes["device"], "^loop.*") == true'

  filter/exclude_system_paging:
    metrics:
      datapoint:
        - 'metric.name == "system.paging.usage" and attributes["state"] == "cached"'
        - 'metric.name == "system.paging.operations" and attributes["type"] == "cached"'

  filter/exclude_network:
    metrics:
      datapoint:
        - 'IsMatch(metric.name, "^system.network.*") == true and attributes["device"] == "lo"'

  attributes/exclude_system_paging:
    include:
      match_type: strict
      metric_names:
        - system.paging.operations
    actions:
      - key: type
        action: delete

  # Transform to clear description and unit fields
  transform/clear_metadata:
    metric_statements:
      - context: metric
        statements:
          - set(metric.description, "")
          - set(metric.unit, "")

  # Filter to only include execution plan and event metrics (for logs conversion)
  filter/exec_plan_include:
    metrics:
      include:
        match_type: strict
        metric_names:
          - sqlserver.slowquery.query_details
          - sqlserver.execution.plan
          - sqlserver.blocking_query.details
          - sqlserver.activequery.query_details

  # Filter to exclude execution plan and event metrics (from main metrics pipeline)
  filter/exec_plan_exclude:
    metrics:
      exclude:
        match_type: strict
        metric_names:
          - sqlserver.slowquery.query_details
          - sqlserver.execution.plan
          - sqlserver.blocking_query.details
          - sqlserver.activequery.query_details

  cumulativetodelta:
    max_staleness: 5m
    include:
      match_type: strict
      metrics:
        # Wait stats (sys.dm_os_wait_stats)
        - sqlserver.wait_stats.latch.wait_time_ms
        - sqlserver.wait_stats.latch.waiting_tasks_count
        - sqlserver.wait_stats.wait_time_ms
        - sqlserver.wait_stats.waiting_tasks_count
        # Instance stats (sys.dm_os_performance_counters)
        - sqlserver.stats.sql_compilations_per_sec
        - sqlserver.stats.sql_recompilations_per_sec
        - sqlserver.stats.lock_waits_per_sec
        - sqlserver.stats.deadlocks_per_sec
        - sqlserver.stats.user_errors_per_sec
        - sqlserver.stats.kill_connection_errors_per_sec
        - sqlserver.access.page_splits_per_sec
        - sqlserver.buffer.checkpoint_pages_per_sec
        - sqlserver.bufferpool.batch_requests_per_sec
        - sqlserver.instance.transactions_per_sec
        - sqlserver.instance.forced_parameterizations_per_sec
        - sqlserver.instance.full_scans_rate
        - sqlserver.instance.lock_timeouts_rate
        # Database log (sys.dm_os_performance_counters)
        - sqlserver.database.log.flushes_per_sec
        - sqlserver.database.log.bytes_flushed_per_sec
        - sqlserver.database.log.flush_waits_per_sec
        # Failover cluster
        - sqlserver.failover_cluster.log_bytes_received_per_sec
        # User connections
        - sqlserver.user_connections.authentication.logins_per_sec

  deltatorate:
    metrics:
      - sqlserver.wait_stats.latch.wait_time_ms
      - sqlserver.wait_stats.latch.waiting_tasks_count
      - sqlserver.wait_stats.wait_time_ms
      - sqlserver.wait_stats.waiting_tasks_count
      - sqlserver.stats.sql_compilations_per_sec
      - sqlserver.stats.sql_recompilations_per_sec
      - sqlserver.stats.lock_waits_per_sec
      - sqlserver.stats.deadlocks_per_sec
      - sqlserver.stats.user_errors_per_sec
      - sqlserver.stats.kill_connection_errors_per_sec
      - sqlserver.access.page_splits_per_sec
      - sqlserver.buffer.checkpoint_pages_per_sec
      - sqlserver.bufferpool.batch_requests_per_sec
      - sqlserver.instance.transactions_per_sec
      - sqlserver.instance.forced_parameterizations_per_sec
      - sqlserver.instance.full_scans_rate
      - sqlserver.instance.lock_timeouts_rate
      - sqlserver.database.log.flushes_per_sec
      - sqlserver.database.log.bytes_flushed_per_sec
      - sqlserver.database.log.flush_waits_per_sec
      - sqlserver.failover_cluster.log_bytes_received_per_sec
      - sqlserver.user_connections.authentication.logins_per_sec

  transform/host:
    metric_statements:
      - context: metric
        statements:
          - set(metric.description, "")
          - set(metric.unit, "")

  transform:
    trace_statements:
      - context: span
        statements:
          - truncate_all(span.attributes, 4095)
          - truncate_all(resource.attributes, 4095)
    log_statements:
      - context: log
        statements:
          - truncate_all(log.attributes, 4095)
          - truncate_all(resource.attributes, 4095)

  # used to prevent out of memory situations on the collector
  memory_limiter:
    check_interval: 1s
    limit_mib: ${env:NEW_RELIC_MEMORY_LIMIT_MIB:-100}

  batch:

  resourcedetection:
    detectors: ["system"]
    system:
      hostname_sources: ["os"]
      resource_attributes:
        host.id:
          enabled: true

  resourcedetection/db_safe:
    detectors: ["system"]
    override: false
    system:
      hostname_sources: ["os"]
      resource_attributes:
        host.id:
          enabled: true

  resourcedetection/cloud:
    detectors: ["gcp", "ec2", "azure"]
    timeout: 2s
    override: true

  resourcedetection/env:
    detectors: ["env"]
    timeout: 2s
    override: true

connectors:
  metricsaslogs:
    include_resource_attributes: true
    include_scope_info: true

exporters:
  otlphttp:
    endpoint: "<YOUR_NEWRELIC_OTLP_ENDPOINT>"
    headers:
      api-key: "<YOUR_NEWRELIC_API_KEY>"
    compression: gzip

service:
  pipelines:
    metrics/host:
      receivers: [hostmetrics]
      processors:
        - memory_limiter
        - metricstransform
        - filter/exclude_cpu_utilization
        - filter/exclude_memory_utilization
        - filter/exclude_memory_usage
        - filter/exclude_filesystem_utilization
        - filter/exclude_filesystem_usage
        - filter/exclude_filesystem_inodes_usage
        - filter/exclude_system_disk
        - filter/exclude_network
        - attributes/exclude_system_paging
        - transform/host
        - resourcedetection
        - resourcedetection/cloud
        - resourcedetection/db_safe
        - resourcedetection/env
        - cumulativetodelta
        - deltatorate
        - batch
      exporters: [otlphttp]
    traces:
      receivers: [otlp]
      processors: [memory_limiter, transform, resourcedetection, resourcedetection/cloud, resourcedetection/env, batch]
      exporters: [otlphttp]
    metrics:
      receivers: [newrelicsqlserver, otlp]
      processors: [memory_limiter, transform, resourcedetection, resourcedetection/cloud, resourcedetection/env, filter/exec_plan_exclude, batch]
      exporters: [otlphttp]
    logs:
      receivers: [metricsaslogs, otlp]
      processors: [memory_limiter, transform, resourcedetection, resourcedetection/cloud, resourcedetection/env, batch]
      exporters: [otlphttp]
    metrics/exec_plan_to_logs:
      receivers: [newrelicsqlserver, otlp]
      processors: [memory_limiter, transform, resourcedetection, resourcedetection/cloud, resourcedetection/env, filter/exec_plan_include, batch]
      exporters: [metricsaslogs]
  extensions: [health_check]

Configuration parameters

The following table describes the key configuration parameters for the newrelicsqlserver receiver:

Parameter	Description
`<YOUR_DB_HOST>`	Enter your SQL Server hostname or IP address
`<YOUR_DB_PORT>`	Enter your SQL Server port number. The default value is set to 1433.
`<USERNAME>`	Enter your database username for authentication
`<PASSWORD>`	Enter your database password for authentication
`<YOUR_NEWRELIC_OTLP_ENDPOINT>`	Enter the New Relic OTLP endpoint. For more information, refer to New Relic OTLP endpoints documentation.
`<YOUR_NEWRELIC_API_KEY>`	Enter your New Relic API key.
`query_monitoring_response_time_threshold`	Minimum response time in milliseconds to consider a query as slow. The default value is set to `100`ms.
`query_monitoring_count_threshold`	Minimum execution count to include a query in monitoring. The default value is set to `30`.
`query_monitoring_fetch_interval`	Interval in seconds to fetch query monitoring data. The default value is set to `15` seconds.
`collection_interval`	Interval in seconds to collect metrics. The default value is set to `30s`.

Importante

If you enable core metric category toggles and notice data gaps, increase both the timeout and collection_interval to give scrapers more time to complete. Always keep the timeout value less than the collection_interval to prevent overlapping scrape cycles.

collection_interval: 60s
timeout: 45s  # must be < collection_interval
query_monitoring_fetch_interval: 30

Restart NRDOT collector

After updating your configuration, restart the NRDOT collector service:

For Windows, to restart the NRDOT Collector service, run:
bash
```
$net stop nrdot-collector-host
$net start nrdot-collector-host
```
For Linux, to restart the NRDOT Collector service, run:
bash
```
$sudo systemctl restart nrdot-collector-host
```

Dica

Always restart the NRDOT collector service after making configuration changes to ensure the new settings take effect.

(Optional) Set up APM-database correlation

To correlate your application performance with database operations, you can set up database service identification. This feature allows you to see exactly which applications are generating specific database workloads. For more information, refer to set up database service identification to get APM-database correlation in New Relic.

Importante

To view database performance data in APM, both entities must be in the same New Relic account. If the entities are in different New Relic accounts, you must have access to both accounts to view the data.

(Optional) Configure secret management

The secret management feature allows you to securely manage sensitive information, such as database credentials. This helps to enhance the security of your monitoring setup by avoiding hardcoding sensitive data in configuration files. For more information, refer to secret management.

Find and use your data

Once your data is being collected, you can access comprehensive SQL Server database monitoring through New Relic's UI.

To find your SQL Server database entity in New Relic:

Go to https://one.newrelic.com > All Capabilities > Databases.
Set the search criteria as instrumentation.provider = opentelemetry.
Select your SQL Server database from the list of entities.

NRDOT collector supported commands

To manage the NRDOT Collector service, view logs, or check the status of the service, use the following available commands based on your operating system.

To restart NRDOT Collector:

bash

$net stop nrdot-collector-host
$net start nrdot-collector-host

To check NRDOT Collector status:
bash
```
$sc query nrdot-collector-host
```
To view NRDOT Collector logs:
1. Type Event Viewer in the search bar and select it.
2. On the left-hand sidebar, expand the folder named Windows Logs.
3. Click on Application to load the application-specific events into the center pane.
4. Look for events with source type = nrdot-collector-host
To delete NRDOT Collector service, uninstall via Add/Remove Programs.

To validate installation status:
- For Debian/Ubuntu-based distributions:
  bash
```
$dpkg -l | grep nrdot-collector-host
```
- For RHEL/CentOS-based distributions:
  bash
```
$rpm -q nrdot-collector-host || ls /etc/nrdot-collector-host
```

To restart NRDOT Collector:

bash

$sudo systemctl restart nrdot-collector-host

To check NRDOT Collector status:

bash

$sudo systemctl status nrdot-collector-host

To view NRDOT Collector logs:

bash

$sudo journalctl -u nrdot-collector-host -f

To delete NRDOT Collector service:

For Debian/Ubuntu-based distributions:
bash
```
$sudo apt-get purge -y nrdot-collector-host
```

For RHEL/CentOS-based distributions:

bash

$sudo dnf remove -y nrdot-collector-host&& sudo rm -rf /etc/nrdot-collector-host

Troubleshooting

If you encounter any network connectivity issues with the NRDOT Collector, run the following commands to test connectivity to your SQL Server instance on port 1433:

For Windows, run:

bash

$Test-NetConnection -ComputerName <YOUR_IP_ADDRESS> -Port <YOUR_PORT_NUMBER>

For Linux, run:

bash

$nc -zv <YOUR_IP_ADDRESS> <YOUR_PORT_NUMBER>

Available metrics

Monitor your SQL Server database performance with metrics organized by configuration flags. Default metrics are collected automatically for New Relic UI functionality. You can also use provided additional metrics to gain advanced and deeper insights when you enable specific scrapers.

Default metrics

These metrics are essential for New Relic's SQL Server monitoring features and are collected automatically regardless of your configuration.

Metric name	Description
`sqlserver.stats.connections`	Current user connections
`sqlserver.instance.blocked_processes_count`	Number of blocked processes
`sqlserver.instance.buffer_pool_hit_percent`	Buffer pool hit percentage
`sqlserver.instance.buffer_pool_size`	Buffer pool size
`sqlserver.instance.compilations_per_batch`	SQL compilations per batch request
`sqlserver.instance.connections_active`	Number of active connections
`sqlserver.instance.forced_parameterizations_per_sec`	Forced parameterizations per second
`sqlserver.instance.full_scans_rate`	Full table/index scans per second
`sqlserver.instance.lock_timeouts_rate`	Number of lock timeouts per second
`sqlserver.instance.memory_available`	Available physical memory on the system
`sqlserver.instance.memory_utilization_percent`	Percentage of memory utilization
`sqlserver.instance.page_splits_per_batch`	Page splits per batch request
`sqlserver.instance.target_memory_kb`	Target server memory in KB
`sqlserver.instance.transactions_per_sec`	Transactions per second
`sqlserver.buffer.cache_hit_ratio`	Buffer cache hit ratio percentage
`sqlserver.buffer.checkpoint_pages_per_sec`	Checkpoint pages per second
`sqlserver.buffer.page_life_expectancy`	Page life expectancy in seconds
`sqlserver.bufferpool.page_life_expectancy_ms`	Page life expectancy in milliseconds
`sqlserver.database.io.stall_ms`	Total IO stall time for the SQL Server database
`sqlserver.database.log.flushes_per_sec`	Number of log flush operations per second
`sqlserver.database.log.transaction_growth`	Number of log growth events for the SQL Server database
`sqlserver.database.page_file_available_bytes`	Available page file space for the SQL Server database
`sqlserver.database.page_file_total_bytes`	Total page file space for the SQL Server database
`sqlserver.database.size.data_mb`	Total data file size excluding log files
`sqlserver.database.size.total_mb`	Total database size including data and log files
`sqlserver.stats.deadlocks_per_sec`	Deadlocks per second
`sqlserver.stats.kill_connection_errors_per_sec`	Kill connection errors per second
`sqlserver.stats.lock_waits_per_sec`	Lock waits per second
`sqlserver.stats.sql_compilations_per_sec`	SQL compilations per second
`sqlserver.stats.sql_recompilations_per_sec`	SQL recompilations per second
`sqlserver.stats.user_errors_per_sec`	User errors per second
`sqlserver.wait_stats.wait_time_ms`	Total wait time in milliseconds
`sqlserver.access.page_splits_per_sec`	Page splits per second
`sqlserver.execution.plan`	SQL Server execution plan operator with detailed cost estimates
`sqlserver.plan.avg_elapsed_time_ms`	Average elapsed time per execution of this plan (historical)
`sqlserver.slowquery.avg_elapsed_time_ms`	Average elapsed time per execution in milliseconds
`sqlserver.slowquery.historical_avg_elapsed_time_ms`	Historical average elapsed time in milliseconds
`sqlserver.slowquery.historical_execution_count`	Historical execution count
`sqlserver.slowquery.historical_logical_reads`	Historical total logical reads
`sqlserver.slowquery.historical_physical_reads`	Historical total physical reads
`sqlserver.slowquery.historical_rows`	Historical total rows returned
`sqlserver.slowquery.historical_wait_time_ms`	Historical total wait time in milliseconds
`sqlserver.slowquery.historical_worker_time_ms`	Historical total worker (CPU) time in milliseconds
`sqlserver.slowquery.interval_avg_elapsed_time_ms`	Interval average elapsed time in milliseconds
`sqlserver.slowquery.interval_avg_logical_reads`	Interval average logical reads per execution
`sqlserver.slowquery.interval_avg_physical_reads`	Interval average physical reads per execution
`sqlserver.slowquery.interval_avg_rows`	Interval average rows returned per execution
`sqlserver.slowquery.interval_avg_wait_time_ms`	Interval average wait time per execution in milliseconds
`sqlserver.slowquery.interval_avg_worker_time_ms`	Interval average worker (CPU) time per execution in milliseconds
`sqlserver.slowquery.interval_elapsed_time_ms`	Interval total elapsed time in milliseconds
`sqlserver.slowquery.interval_execution_count`	Interval execution count
`sqlserver.slowquery.interval_logical_reads`	Interval logical reads
`sqlserver.slowquery.interval_physical_reads`	Interval physical reads
`sqlserver.slowquery.interval_rows`	Interval rows returned
`sqlserver.slowquery.interval_wait_time_ms`	Interval wait time in milliseconds
`sqlserver.slowquery.interval_worker_time_ms`	Interval worker (CPU) time in milliseconds
`sqlserver.slowquery.query_details`	Query details including text and timestamps for slow queries
`sqlserver.activequery.wait_time_seconds`	Wait time for currently executing query
`sqlserver.blocking_query.details`	Blocking query details for correlation with active queries

Additional metrics

Enable these additional metrics as required for deeper insights into your SQL Server database performance and health.

Enable enable_instance_metrics: true to collect these metrics:

Metric name	Description
`sqlserver.instance.background_processes_count`	Number of background processes
`sqlserver.instance.disk_in_bytes`	Total disk space in bytes
`sqlserver.instance.dormant_processes_count`	Number of dormant processes
`sqlserver.instance.memory_total`	Total physical memory on the system
`sqlserver.instance.preconnect_processes_count`	Number of preconnect processes
`sqlserver.instance.runnable_processes_count`	Number of runnable processes
`sqlserver.instance.runnable_tasks`	Number of runnable tasks
`sqlserver.instance.running_processes_count`	Number of running processes
`sqlserver.instance.sleeping_processes_count`	Number of sleeping processes
`sqlserver.instance.suspended_processes_count`	Number of suspended processes

Metric name	Description
`sqlserver.bufferpool.batch_requests_per_sec`	Batch requests per second

Enable enable_database_metrics: true to collect these metrics:

Metric name	Description
`sqlserver.database.log.bytes_flushed_per_sec`	Number of log bytes flushed per second
`sqlserver.database.log.flush_waits_per_sec`	Number of flush wait operations per second
`sqlserver.database.log.used_space_mb`	Used log space in megabytes
`sqlserver.database.principals.applicationRoles`	Number of application roles
`sqlserver.database.principals.old`	Number of old principals (older than 365 days)
`sqlserver.database.principals.orphanedUsers`	Number of orphaned users without server login
`sqlserver.database.principals.recentlyCreated`	Number of recently created principals (last 30 days)
`sqlserver.database.principals.roles`	Number of database roles
`sqlserver.database.principals.sqlUsers`	Number of SQL authentication users
`sqlserver.database.principals.total`	Total number of database principals
`sqlserver.database.principals.users`	Number of database users
`sqlserver.database.principals.windowsUsers`	Number of Windows authentication users
`sqlserver.database.role.members.applicationRoles`	Number of application role members
`sqlserver.database.role.members.crossRole`	Number of cross-role members
`sqlserver.database.role.members.highPrivilege`	Number of high privilege role members
`sqlserver.database.role.members.unique`	Number of unique members in roles
`sqlserver.database.role.memberships.active`	Number of active role memberships
`sqlserver.database.role.memberships.custom`	Number of custom role memberships
`sqlserver.database.role.memberships.nested`	Number of nested role memberships
`sqlserver.database.role.memberships.total`	Total number of role memberships
`sqlserver.database.role.memberships.users`	Number of user role memberships
`sqlserver.database.role.permission.memberCount`	Number of members in role
`sqlserver.database.role.permission.riskLevel`	Role risk level
`sqlserver.database.role.roles.empty`	Number of empty roles
`sqlserver.database.role.roles.withMembers`	Number of unique roles with members
`sqlserver.database.transactions.active`	Number of active transactions

Enable enable_failover_cluster_metrics: true to collect these metrics:

Metric name	Description
`sqlserver.failover_cluster.ag_cluster_type`	Cluster type for the availability group
`sqlserver.failover_cluster.ag_failure_condition_level`	Automatic failover condition level for the availability group
`sqlserver.failover_cluster.ag_health_check_timeout`	Health check timeout for the availability group in milliseconds
`sqlserver.failover_cluster.ag_replica_role`	Current role of the replica within the Availability Group
`sqlserver.failover_cluster.ag_required_sync_secondaries`	Number of synchronous secondary replicas required to commit transactions
`sqlserver.failover_cluster.ag_synchronization_health`	Health of data synchronization between primary and secondary replica
`sqlserver.failover_cluster.flow_control_time_ms`	Time spent in flow control by log records from primary replica in milliseconds per second
`sqlserver.failover_cluster.log_bytes_received_per_sec`	Rate of log records received by secondary replica from primary replica in bytes per second
`sqlserver.failover_cluster.log_send_queue_kb`	Amount of log records in the log send queue waiting to be sent to the secondary replica in kilobytes
`sqlserver.failover_cluster.redo_queue_kb`	Amount of log records in the redo queue waiting to be redone on the secondary replica in kilobytes
`sqlserver.failover_cluster.redo_rate_kb_sec`	Rate at which log records are being redone on the secondary replica in kilobytes per second
`sqlserver.failover_cluster.transaction_delay_ms`	Average delay for transactions on the secondary replica in milliseconds

Enable enable_lock_metrics: true to collect these metrics:

Metric name	Description
`sqlserver.lock.mode.bulk_update`	Number of bulk update locks
`sqlserver.lock.mode.exclusive`	Number of exclusive locks
`sqlserver.lock.mode.intent`	Number of intent locks
`sqlserver.lock.mode.schema`	Number of schema locks
`sqlserver.lock.mode.shared`	Number of shared locks
`sqlserver.lock.mode.shared_intent_exclusive`	Number of shared intent exclusive locks
`sqlserver.lock.mode.total`	Total active locks by mode
`sqlserver.lock.mode.update`	Number of update locks
`sqlserver.lock.resource.allocation_unit`	Number of allocation unit locks
`sqlserver.lock.resource.application`	Number of application locks
`sqlserver.lock.resource.database_level`	Number of database-level locks
`sqlserver.lock.resource.extent`	Number of extent-level locks
`sqlserver.lock.resource.file`	Number of file-level locks
`sqlserver.lock.resource.hobt`	Number of heap or B-tree locks
`sqlserver.lock.resource.key`	Number of key-level locks
`sqlserver.lock.resource.metadata`	Number of metadata locks
`sqlserver.lock.resource.page`	Number of page-level locks
`sqlserver.lock.resource.row`	Number of row-level locks
`sqlserver.lock.resource.table`	Number of table-level locks
`sqlserver.lock.resource.total`	Total active locks in the database

Enable enable_security_metrics: true to collect these metrics:

Metric name	Description
`sqlserver.security.server_principals_count`	Total number of server principals (logins)
`sqlserver.security.server_role_members_count`	Total number of server role members

Enable enable_tempdb_metrics: true to collect these metrics:

Metric name	Description
`sqlserver.tempdb.allocation_waits_ms`	Total allocation-related wait time
`sqlserver.tempdb.current_waiters`	Number of tasks currently waiting on TempDB page latches
`sqlserver.tempdb.data_file_count`	Number of TempDB data files
`sqlserver.tempdb.pagelatch_waits_ms`	Total page latch wait time since server start
`sqlserver.tempdb.total_size_mb`	Total size of TempDB data files

Enable enable_thread_pool_metrics: true to collect these metrics:

Metric name	Description
`sqlserver.threadpool.current_tasks`	Total tasks currently assigned to schedulers
`sqlserver.threadpool.max_workers`	Maximum configured worker threads
`sqlserver.threadpool.runnable_tasks`	Tasks ready to run but waiting for CPU time
`sqlserver.threadpool.running_workers`	Number of worker threads currently running
`sqlserver.threadpool.utilization_percent`	Percentage of worker threads currently in use
`sqlserver.threadpool.waiting_tasks`	Number of tasks currently waiting for threadpool worker threads
`sqlserver.threadpool.work_queue_count`	Total number of tasks in scheduler work queues

Enable enable_user_connection_metrics: true to collect these metrics:

Metric name	Description
`sqlserver.user_connections.authentication.churn_rate`	Connection churn rate (logout/login ratio as percentage)
`sqlserver.user_connections.authentication.logins_per_sec`	Current login rate per second
`sqlserver.user_connections.authentication.recent_failed_logins`	Count of failed logins in the last hour
`sqlserver.user_connections.authentication.total_failed_logins`	Total count of failed login attempts in current error log
`sqlserver.user_connections.authentication.unique_failed_sources`	Count of distinct source IPs with failed logins
`sqlserver.user_connections.authentication.unique_failed_users`	Count of distinct usernames with failed logins
`sqlserver.user_connections.client.count`	Number of connections grouped by client host and program
`sqlserver.user_connections.client.unique_hosts`	Count of unique client hosts with active connections
`sqlserver.user_connections.client.unique_programs`	Count of unique programs with active connections
`sqlserver.user_connections.runnable`	Number of runnable user connections
`sqlserver.user_connections.running`	Number of running user connections
`sqlserver.user_connections.sleeping`	Number of sleeping user connections
`sqlserver.user_connections.suspended`	Number of suspended user connections
`sqlserver.user_connections.total`	Total number of user connections across all statuses
`sqlserver.user_connections.utilization.active_ratio`	Ratio of active connections to total connections
`sqlserver.user_connections.utilization.idle_ratio`	Ratio of idle connections to total connections

Enable enable_wait_time_metrics: true to collect these metrics:

Metric name	Description
`sqlserver.wait_stats.latch.wait_time_ms`	Latch wait time in milliseconds
`sqlserver.wait_stats.latch.waiting_tasks_count`	Number of tasks waiting on latches
`sqlserver.wait_stats.waiting_tasks_count`	Number of tasks currently waiting

Enable enable_database_buffer_metrics: true to collect these metrics:

Metric name	Description
`sqlserver.database.bufferpool.size_per_database_bytes`	Size of the SQL Server buffer pool allocated for the database

What's next

After setting up SQL Server database monitoring with NRDOT:

Create custom dashboards to visualize your database metrics
Set up alerts for critical database performance thresholds
Explore your data using New Relic's query capabilities

Microsoft SQL Server monitoring with NRDOT

Preview

Prerequisites .css-21sua1{background:none;border:none;width:0;padding:0;}

Set-up the NRDOT Collector for Microsoft SQL Server monitoring

Setup the NRDOT Collector

Importante

For Linux

Configure database user

For Windows (SSMS)

For Linux (sqlcmd)

Configure NRDOT Collector

Importante

Configuration parameters

Importante

Restart NRDOT collector

Dica

(Optional) Set up APM-database correlation

Importante

(Optional) Configure secret management

Find and use your data

NRDOT collector supported commands

Windows service management

Linux service management

Troubleshooting

Available metrics

Default metrics

Default metrics collected by NRDOT Collector

Additional metrics

Instance metrics

Buffer pool metrics

Database metrics

Failover cluster metrics

Lock metrics

Security metrics

TempDB metrics

Thread pool metrics

User connections metrics

Wait stats metrics

Database buffer metrics

What's next

Prerequisites