Microsoft SQL monitoring integration
Our Microsoft SQL Server integration collects and sends metric and inventory data from your Microsoft SQL Server environment to our platform to equip you to monitor the health of your Microsoft SQL Server environment. We collect both database and instance-level metrics so you can troubleshoot and resolve performance problems.

Dashboard installed through the New Relic Microsoft SQL Server monitoring integration.
Configure the Microsoft SQL Server integration
You can edit the mssql-config.yml
file to include the necessary login credentials and configure data collection according to your setup and preferences. This configuration file contains common settings applicable to all integrations, such as interval
, timeout
, and inventory_source
.
For more information on these common settings, refer to configuration format standards.
If you are using our legacy configuration and definition files, refer to this document.
mssql-config.yml sample files
This is the basic configuration used to collect metrics and inventory from your localhost
. It uses default connection on port 1433
:
integrations:- name: nri-mssql env: HOSTNAME: localhost PORT: 1433 USERNAME: USERNAME PASSWORD: PASSWORD ENABLE_SSL: true TRUST_SERVER_CERTIFICATE: true # Enable collection of detailed query-level metrics. ENABLE_QUERY_MONITORING: false # Threshold in milliseconds for query response time. If response time exceeds this threshold, the query will be considered slow. QUERY_MONITORING_RESPONSE_TIME_THRESHOLD: 500 # Maximum number of queries returned in query analysis results. QUERY_MONITORING_COUNT_THRESHOLD: 20 # Interval in seconds for fetching grouped slow queries; Should always be same as mysql-config interval. QUERY_MONITORING_FETCH_INTERVAL: 15 interval: 15s labels: environment: production inventory_source: config/mssql
This configuration collects metrics and inventory from your localhost
using the default connection on port 1433
, with database performance monitoring enabled::
integrations:- name: nri-mssql env: HOSTNAME: localhost PORT: 1433 USERNAME: USERNAME PASSWORD: PASSWORD ENABLE_SSL: true TRUST_SERVER_CERTIFICATE: true # Enable collection of detailed query-level metrics. ENABLE_QUERY_MONITORING: true # Threshold in milliseconds for query response time. If response time exceeds this threshold, the query will be considered slow. QUERY_MONITORING_RESPONSE_TIME_THRESHOLD: 500 # Maximum number of queries returned in query analysis results. QUERY_MONITORING_COUNT_THRESHOLD: 20 # Interval in seconds for fetching grouped slow queries; Should always be same as mysql-config interval. QUERY_MONITORING_FETCH_INTERVAL: 15 interval: 15s labels: environment: production inventory_source: config/mssql
This is the basic configuration used to collect metrics and inventory from your localhost. In this case we use a domain user for authentication:
integrations: - name: nri-mssql env: HOSTNAME: localhost PORT: 1433 USERNAME: domain_name\domain_user PASSWORD: domain_user_password interval: 15s labels: environment: production inventory_source: config/mssql
This is the basic configuration used to collect metrics and inventory from your localhost. In this case we use a gMSA for authentication, which requires a blank username and password:
integrations: - name: nri-mssql env: HOSTNAME: localhost PORT: 1433 USERNAME: PASSWORD: interval: 15s labels: environment: production inventory_source: config/mssql
This is the basic configuration used to collect metrics and inventory from your localhost. In this case we use a user from Microsoft Entra ID service principal for authentication:
integrations: - name: nri-mssql env: HOSTNAME: localhost PORT: 1433 CLIENT_ID: client_id TENANT_ID: tenant_id CLIENT_SECRET: client_secret # Enable collection of detailed query-level metrics. # ENABLE_QUERY_MONITORING: false interval: 15s labels: environment: production inventory_source: config/mssql
This configuration collects metrics every 15 seconds and inventory every 60 seconds:
integrations: - name: nri-mssql env: METRICS: true HOSTNAME: localhost PORT: 1433 USERNAME: mssql_user PASSWORD: mssql_password interval: 15s labels: environment: production
- name: nri-mssql env: INVENTORY: true HOSTNAME: localhost PORT: 1433 USERNAME: mssql_user PASSWORD: mssql_password interval: 60s labels: environment: production inventory_source: config/mssql
This configuration monitors the mssql_instance1
and mssql_instance2
instances on the same SQL Server. When connecting directly to an instance you'll need to remove the PORT
setting. The SQL Browser service also needs to be running on the server:
integrations: - name: nri-mssql env: HOSTNAME: localhost USERNAME: mssql_user PASSWORD: mssql_password INSTANCE: mssql_instance1 interval: 15s labels: environment: production inventory_source: config/mssql
- name: nri-mssql env: HOSTNAME: localhost USERNAME: mssql_user PASSWORD: mssql_password INSTANCE: mssql_instance2 interval: 15s labels: environment: production inventory_source: config/mssql
Use to connect to Microsoft SQL Server using SSL without validation of the certificate:
integrations: - name: nri-mssql env: METRICS: true HOSTNAME: localhost PORT: 1433 USERNAME: mssql_user PASSWORD: mssql_password ENABLE_SSL: true TRUST_SERVER_CERTIFICATE: true interval: 15s labels: environment: production
If the integration causes some overhead on your SQL Server, disabling the Buffer Pool and Database Partition Reserve metrics can help to optimize the integration's performance. In this example, we also increased the sampling interval to 30 seconds to reduce the sampling frequency:
integrations: - name: nri-mssql env: HOSTNAME: localhost PORT: 1433 USERNAME: mssql_user PASSWORD: mssql_password ENABLE_BUFFER_METRICS: false ENABLE_DATABASE_RESERVE_METRICS: false ENABLE_DISK_METRICS_IN_BYTES: false interval: 30s labels: environment: production inventory_source: config/mssql
Keep in mind the following:
- If you enable
ENABLE_BUFFER_METRICS
, a query starts running involving thesys.sysdatabases
andsys.dm_os_buffer_descriptors
internal tables to obtain the buffer's pool size for each database. This query could cause overhead on some SQL Servers. If you disableENABLE_BUFFER_METRICS
, the metricbufferpool.sizePerDatabaseInBytes
won't be reported in MssqlDatabaseSample andbufferpool.sizeInBytes
won't be reported inMssqlInstanceSample
. - If you enable
ENABLE_DATABASE_RESERVE_METRICS
, the reserved size is queried for each database and may cause some load on your server, depending on its size and usage. When it's disabled, bothpageFileTotal
andpageFileAvailable
metrics stop being reported in MssqlDatabaseSample. - If you enable
ENABLE_DISK_METRICS_IN_BYTES
, a query that fetches the volume stats for each database will run, and this query can be slow. If you disableENABLE_DISK_METRICS_IN_BYTES
, the metricinstance.diskInBytes
won't be reported inMssqlDatabaseSample
.
You can use a custom query to collect additional metrics. Custom metrics will be added to the MssqlCustomQuerySample
event sample.
Tip
If you need to use multiple custom queries, use the CUSTOM_METRICS_CONFIG
example.
integrations: - name: nri-mssql env: METRICS: true HOSTNAME: mssql_host1 PORT: 1433 USERNAME: mssql1_user PASSWORD: mssql1_password CUSTOM_METRICS_QUERY: >- SELECT 'instance_buffer_pool_size' AS metric_name, Count_big(*) * (8*1024) AS metric_value, 'gauge' as metric_type, database_id FROM sys.dm_os_buffer_descriptors WITH (nolock) GROUP BY database_id interval: 15s labels: environment: production
If you need multiple custom SQL queries, add them to mssql-custom-query.yml
, and reference that file on your configuration. For more examples of custom queries, check our sample file on github.
Tip
CUSTOM_METRICS_CONFIG
is only enabled if CUSTOM_METRICS_QUERY
is not present.
integrations: - name: nri-mssql env: METRICS: true HOSTNAME: mssql_host1 PORT: 1433 USERNAME: mssql1_user PASSWORD: mssql1_password CUSTOM_METRICS_CONFIG: 'C:\path\to\mssql-custom-query.yml' interval: 15s labels: environment: production
- Here's an example
mssql-custom-query.yml
.
queries: # Example for metric_name / metric_type specified in this config - query: SELECT count(*) AS 'metric_value' FROM sys.databases metric_name: dbCount metric_type: gauge
# Example for metric_name from query, metric_type auto-detected, additional attribute 'category_type' - query: SELECT CONCAT('category_', category_id) AS metric_name, name AS metric_value, category_type FROM syscategories database: msdb
# Example for stored procedure 'exec dbo.sp_server_info @attribute_id = 2' - query: dbo.sp_server_info @attribute_id = 2
Tip
Enhance your SQL Server tags: The labels
section shown in these examples is key to enriching your Microsoft SQL Server entities with custom tags. For a comprehensive understanding of how to leverage these tags, including how label.
prefixed custom attributes from the infrastructure agent can also become tags, and how these tags behave in the UI, refer to our detailed guide on Enhanced tags for New Relic database entities.
Metrics collected by the integration
The Microsoft SQL Server integration collects the following metric data attributes. Some metric name are prefixed with a category indicator and a period, such as asserts.
or flush.
.
These attributes can be found by querying the MssqlDatabaseSample
event.
Metric | Description |
---|---|
| The size of the buffer pool per database. It is reported when |
| Maximum database size in bytes. It is reported when |
| Wait time of stall since last restart, in milliseconds. |
| Total number of times the transaction log for the database has been expanded since the last restart. |
| Available page file size, in bytes. It is reported when |
| Total page file size, in bytes. It is reported when |
| The available physical memory, in bytes. Applies to: Azure SQL Database. |
| The total physical memory, in bytes. Applies to: Azure SQL Database. |
| The percentage of memory utilization. Applies to: Azure SQL Database. |
The Microsoft SQL Server integration collects the following instance metrics. These attributes can be found by querying the MssqlInstanceSample
event.
Metric | Description |
---|---|
| The number of page splits per second. |
| The number of active connections. |
| The number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed. |
| The number of batch requests per second on the buffer pool. |
| The life expectancy of a page in the buffer pool, in milliseconds. |
| The size of the buffer pool, in bytes. It is reported when |
| The number of background processes on the instance. |
| The number of blocked processes on the instance. |
| The amount of disk space on the instance, in bytes. It is reported
when |
| The number of dormant processes on the instance. |
| The number of forced parameterizations per second on the instance. |
| The number of preconnect processes on the instance. |
| The number of runnable processes on the instance. |
| The number of runnable tasks on the instance. |
| The number of running processes on the instance. |
| The number of sleeping processes on the instance. |
| The number of suspended processes on the instance. |
| The number of transactions per second on the instance. |
| The available physical memory, in bytes. This metric is not reported in case of Azure SQL Database. |
| The total physical memory, in bytes. This metric is not reported in case of Azure SQL Database. |
| The percentage of memory utilization. This metric is not reported in case of Azure SQL Database. |
| The number of user connections. |
| The number of lock requests per second that resulted in a deadlock since the last restart. |
| The number of kill connection errors per second since the last restart. |
| The number of times per second that Microsoft SQL Server is unable to retain a lock right away for a resource. |
| The number of Microsoft SQL Server compilations per second. |
| The number of Microsoft SQL Server re-compilations per second. |
| The number of user errors per second since the last restart. |
| The percentage of buffer pools hits on the instance. |
| The number of milliseconds per second spent waiting across the instance. |
These attributes can be found by querying the MssqlWaitSample
event.
Metric | Description |
---|---|
| Total wait time for this wait type, in milliseconds. This time is inclusive of |
| The number of waits on this wait type, in milliseconds. This counter is incremented at the start of each wait. |
Microsoft SQL Server instance settings
The Microsoft SQL Server integration collects both metrics and inventory information. In the table, use the Applies to column for the settings available to each collection:
Setting | Description | Default | Applies to |
---|---|---|---|
| Hostname or IP where Microsoft SQL Server is running. |
| M/I |
| Port on which Microsoft SQL Server is listening.
|
| M/I |
| The Microsoft SQL Server instance to connect to.
| N/A | M/I |
| Username for accessing the Microsoft SQL Server.
| N/A | M/I |
| Password for the given SQL or Domain user. | N/A | M/I |
| Specify extra connection parameters as attr1=val1&attr2=val2. | N/A | M/I |
| Use SSL to connect to the Microsoft SQL Server. |
| M/I |
| if set to |
| M/I |
| Location of the SSL Certificate. | N/A | M/I |
| Timeout for queries, in seconds. Set |
| M/I |
| Enable collection of buffer pool metrics. These can be resource intensive for large systems. |
| M |
| Enable collection of database partition reserve space. These can be resource intensive for large systems. |
| M |
| Enable collection of the volume stats for each MSSQL instance. |
| M |
| A SQL query to collect custom metrics. See the custom query example. | N/A | M |
| YAML configuration with one or more SQL queries to collect custom metrics. See the multiple custom queries example. |
| M |
| Set to |
| |
| Set to |
|
The values for these settings can be defined in several ways:
Adding the value directly in the config file. This is the most common way.
Replacing the values from environment variables using the
{{ }}
notation. Read more about using environment variable passthroughs with on-host integrations or see the example for environment variables replacement.Important
This requires infrastructure agent v1.14.0+.Using secrets management. Use this to protect sensitive information, such as passwords that would be exposed in plain text on the configuration file. For more information, see secrets management.
Metrics collected by query performance monitoring
These attributes can be found by querying the MSSQLBlockingSessionQueries
event.
Metric | Description |
---|---|
blocking_spid | The ID of the blocking session. |
blocking_status | The status of the blocking session. |
blocked_spid | The ID of the blocked session. |
blocked_status | The status of the blocked session. |
wait_type | The type of wait experienced by the blocked session. |
wait_time_in_seconds | The time the session has been waiting in seconds. |
command_type | The type of command being performed. |
database_name | The name of the database where the blocking is occurring. |
blocking_query_text | The text of the query causing the block. |
blocked_query_text | The text of the query being blocked. |
blocked_query_start_time | The start time of the blocked query. |
These attributes can be found by querying the MSSQLQueryExecutionPlans
event.
Metric | Description |
---|---|
| The SQL statement text. |
| The unique identifier for the query. |
| The unique identifier for the query plan. |
| The ID of the node in the execution plan. |
| The physical operation performed by this node. |
| The logical operation represented by this node. |
| The estimated number of rows this operation will produce. |
| The estimated I/O cost of this operation. |
| The estimated CPU cost of this operation. |
| The average size of the rows processed by this node. |
| The estimated total cost for running this subtree. |
| The cost estimate of the node's operation. |
| The estimated execution mode of this operation. |
| The amount of memory granted for this query in kilobytes. |
| A boolean indicator of whether a spill occurred. |
| A boolean indicator of whether there is a join predicate. |
| The total worker time in milliseconds. |
| The total elapsed time in milliseconds. |
| The total number of logical reads performed. |
| The total number of logical writes performed. |
| The number of times the query was executed. |
| The plan handle used for retrieving the plan. |
| The average elapsed time in milliseconds. |
These attributes can be found by querying the MSSQLWaitTimeAnalysis
event.
Metric | Description |
---|---|
| The unique identifier for the query. |
| The name of the database. |
| The text of the query. |
| The category of wait experienced by the query. |
| The total amount of wait time in milliseconds. |
| The average amount of wait time in milliseconds. |
| The count of wait events that occurred. |
| The time of the last execution of the query. |
| The timestamp of when the data was collected. |
These attributes can be found by querying the MSSQLTopSlowQueries
event.
Metrics | Description |
---|---|
| The unique identifier for the query. |
| The text of the query. |
| The name of the database. |
| The name of the schema. |
| The timestamp of the last execution of the query. |
| The number of times the query was executed. |
| The average CPU time in milliseconds. |
| The average elapsed time in milliseconds. |
| The average number of disk reads. |
| The average number of disk writes. |
| The type of SQL statement. |
| The timestamp of when the data was collected. |
Inventory data
The Microsoft SQL Server integration captures the configuration parameters and current settings from your SQL Server environment. It collects the results of the sp_configure
stored procedure, as well as current running configuration settings from the sys.configurations
table. The data is available on the Inventory page, under the config/mssql
source.
For more about inventory data, see Understand integration data.