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 so you can 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.

Image of the dashboard available through the Microsoft SQL Server quickstart

Dashboard installed through the New Relic Microsoft SQL Server monitoring integration.

Sample configuration 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: mssql_user
        PASSWORD: mssql_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 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 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 MS SQL 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 the sys.sysdatabases and sys.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 disable ENABLE_BUFFER_METRICS, the metric bufferpool.sizePerDatabaseInBytes won't be reported in MssqlDatabaseSample and buferpool.sizeInBytes won't be reported in MssqlInstanceSample.
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, both pageFileTotal and pageFileAvailable metrics stop being reported in MssqlDatabaseSample.
If you enable ENABLE_DISK_METRICS_IN_BYTES, runs a query which fetchs the volume stats for each database and this query can be slow. If you disable ENABLE_DISK_METRICS_IN_BYTES, the metric instance.diskInBytes won't be reposted in MssqlDatabaseSample.

You can use custom a custom query to collect additional metrics. Custom metrics will be added to the MssqlCustomQuerySample event sample.

Sugerencia

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.

Sugerencia

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

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
`bufferpool.sizePerDatabaseInBytes`	The size of the buffer pool per database. It is reported when `ENABLE_BUFFER_METRICS` is set to true.
`io.stallInMilliseconds`	Wait time of stall since last restart, in milliseconds.
`log.transactionGrowth`	Total number of times the transaction log for the database has been expanded since the last restart.
`pageFileAvailable`	Available page file size, in bytes. It is reported when `ENABLE_DATABASE_RESERVE_METRICS` is set to true.
`pageFileTotal`	Total page file size, in bytes. It is reported when `ENABLE_DATABASE_RESERVE_METRICS` is set to true.

The Microsoft SQL Server integration collects the following instance metrics. These attributes can be found by querying the MssqlInstanceSample event.

Metric	Description
`access.pageSplitsPerSecond`	The number of page splits per second.
`activeConnections`	The number of active connections.
`buffer.checkpointPagesPerSecond`	The number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.
`bufferpool.batchRequestsPerSecond`	The number of batch requests per second on the buffer pool.
`bufferpool.pageLifeExptancyInMilliseconds`	The life expectancy of a page in the buffer pool, in milliseconds.
`bufferpool.sizeInBytes`	The size of the buffer pool, in bytes. It is reported when `ENABLE_BUFFER_METRICS` is set to true.
`instance.backgroundProcessesCount`	The number of background processes on the instance.
`instance.blockedProcessesCount`	The number of blocked processes on the instance.
`instance.diskInBytes`	The amount of disk space on the instance, in bytes. It is reported when `ENABLE_DISK_METRICS_IN_BYTES` is set to true.
`instance.dormantProcessesCount`	The number of dormant processes on the instance.
`instance.forcedParameterizationsPerSecond`	The number of forced parameterizations per second on the instance.
`instance.preconnectProcessesCount`	The number of preconnect processes on the instance.
`instance.runnableProcessesCount`	The number of runnable processes on the instance.
`instance.runnableTasks`	The number of runnable tasks on the instance.
`instance.runningProcessesCount`	The number of running processes on the instance.
`instance.sleepingProcessesCount`	The number of sleeping processes on the instance.
`instance.suspendedProcessesCount`	The number of suspended processes on the instance.
`instance.transactionsPerSecond`	The number of transactions per second on the instance.
`memoryAvailable`	The available physical memory, in bytes.
`memoryTotal`	The total physical memory, in bytes.
`memoryUtilization`	The percentage of memory utilization.
`stats.connections`	The number of user connections.
`stats.deadlocksPerSecond`	The number of lock requests per second that resulted in a deadlock since the last restart.
`stats.killConnectionErrorsPerSecond`	The number of kill connection errors per second since the last restart.
`stats.lockWaitsPerSecond`	The number of times per second that MS SQL Server is unable to retain a lock right away for a resource.
`stats.sqlCompilations`	The number of MS SQL compilations per second.
`stats.sqlRecompilationsPerSecond`	The number of MS SQL re-compilations per second.
`stats.userErrorsPerSecond`	The number of user errors per second since the last restart.
`system.bufferPoolHitPercent`	The percentage of buffer pools hits on the instance.
`system.waitTimeInMillisecondsPerSecond`	The number of milliseconds per second spent waiting across the instance.

These attributes can be found by querying the MssqlWaitSample event.

Metric	Description
`system.waitTimeCount`	Total wait time for this wait type, in milliseconds. This time is inclusive of `signal_wait_time_ms`.
`system.waitTimeInMillisecondsPerSecond`	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`	Hostname or IP where MS SQL server is running.	`127.0.0.1`	M/I
`PORT`	Port on which MS SQL server is listening. Note: Port is only required when `INSTANCE` is not specified.	`1433`	M/I
`INSTANCE`	The MS SQL Server Instance to connect to. Note: Only required when `PORT` is not specified. Do not use both.	N/A	M/I
`USERNAME`	Username for accessing the MS SQL server. If using a domain user use the syntax `domain\user`	N/A	M/I
`PASSWORD`	Password for the given SQL or Domain user.	N/A	M/I
`EXTRA_CONNECTION_URL_ARGS`	Specify extra connection parameters as attr1=val1&attr2=val2.	N/A	M/I
`ENABLE_SSL`	Use SSL to connect to the MS SQL Server.	`false`	M/I
`TRUST_SERVER_CERTIFICATE`	if set to `true`, server certificate is NOT verified for SSL.	`false`	M/I
`CERTIFICATE_LOCATION`	Location of the SSL Certificate.	N/A	M/I
`TIMEOUT`	Timeout for queries, in seconds. Set `0` for no timeout.	`30`	M/I
`ENABLE_BUFFER_METRICS`	Enable collection of buffer pool metrics. These can be resource intensive for large systems.	`true`	M
`ENABLE_DATABASE_RESERVE_METRICS`	Enable collection of database partition reserve space. These can be resource intensive for large systems.	`true`	M
`ENABLE_DISK_METRICS_IN_BYTES`	Enable collection of the volume stats for each MSSQL instance.	`true`	M
`CUSTOM_METRICS_QUERY`	A SQL query to collect custom metrics. See the custom query example.	N/A	M
`CUSTOM_METRICS_CONFIG`	YAML configuration with one or more SQL queries to collect custom metrics. See the multiple custom queries example.	`false`	M
`METRICS`	Set to `true` to enable metrics-only collection.	`false`
`INVENTORY`	Set to `true` to enable inventory-only collection.	`false`

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.
Importante
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.

Inventory data

The Microsoft SQL Server integration captures the configuration parameters and current settings of the Microsoft 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.

Microsoft SQL monitoring integration

Sample configuration files

Basic configuration

Basic configuration with Windows domain authentication

Basic configuration with gMSA user

Basic configuration with different metric/inventory intervals

Multi Instance connection

Metrics-only connecting over SSL

Buffer Pool and Partition Reserve metrics optimization

Custom Query

Sugerencia

Multiple Custom Queries

Sugerencia

Metrics collected by the integration

Database metrics

Instance metrics

Wait metrics

Microsoft SQL Server instance settings

Importante

Inventory data

Microsoft SQL monitoring integration

Sample configuration files.css-21sua1{background:none;border:none;width:0;padding:0;}

Basic configuration with Windows domain authentication

Basic configuration with gMSA user

Basic configuration with different metric/inventory intervals

Multi Instance connection

Metrics-only connecting over SSL

Buffer Pool and Partition Reserve metrics optimization

Custom Query

Multiple Custom Queries

Metrics collected by the integration

Database metrics

Instance metrics

Wait metrics

Microsoft SQL Server instance settings

Importante

Inventory data

Sample configuration files