MS SQL Server

Plugin: windows.plugin Module: PerflibMSSQL

Overview

This collector monitors Microsoft SQL Server statistics.

It queries different SQL objects per instance from Perflib in order to gather the metrics.

This collector is only supported on the following platforms:

• windows

This collector only supports collecting metrics from a single instance of this integration.

Default Behavior

Auto-Detection

The collector automatically discovers and monitors standard SQL Server metrics without additional setup. However, for transaction-level metrics, the size of all the data files, and the wait stats in the database you must:

• Complete the “Configure SQL Server for Monitoring” steps in the Setup -> Prerequisites section.
• Configure a database connection (see Setup → Configuration → Examples).

Limits

The default configuration for this integration does not impose any limits on data collection.

Performance Impact

The default configuration for this integration is not expected to impose a significant performance impact on the system.

Setup

Prerequisites

Configure SQL Server for Monitoring

For each SQL Server instance you want to monitor, complete the following steps:

1. Create Monitoring User

   Create an SQL Server user with the necessary permissions to collect monitoring data:

   USE master;
   CREATE LOGIN netdata_user WITH PASSWORD = '1ReallyStrongPasswordShouldBeInsertedHere';
   CREATE USER netdata_user FOR LOGIN netdata_user;
   GRANT CONNECT SQL TO netdata_user;
   GRANT VIEW SERVER STATE TO netdata_user;
   GO
   

2. Enable Query Store

   Enable the Query Store and grant access to the monitoring user on all relevant databases:

   DECLARE @dbname NVARCHAR(max)
   DECLARE nd_user_cursor CURSOR FOR SELECT name
                       FROM master.dbo.sysdatabases
                       WHERE name NOT IN ('master')
   
   OPEN nd_user_cursor
   FETCH NEXT FROM nd_user_cursor INTO @dbname
   WHILE @@FETCH_STATUS = 0
   BEGIN
     EXECUTE ("USE "+ @dbname+"; CREATE USER netdata_user FOR LOGIN netdata_user; ALTER DATABASE "+@dbname+" SET QUERY_STORE = ON ( QUERY_CAPTURE_MODE = ALL, DATA_FLUSH_INTERVAL_SECONDS               =    900 )");
     FETCH next FROM nd_user_cursor INTO @dbname;
   END
   CLOSE nd_user_cursor
   DEALLOCATE nd_user_cursor
   GO
   

3. Enable Query Job status

   Connect to your database and grant SELECT permission to netdata_user:

USE msdb;
GO
GRANT SELECT ON SCHEMA::[dbo] TO netdata_user;
GO

4. Enable Query replication stored procedure

   Connect to your database and add netdata_user to replmonitor:

USE distribution;
GO
EXEC sp_addrolemember 'replmonitor', 'netdata_user';
GO

5. Configure SQL Server Network Settings

   Enable SQL Server to accept TCP connections:

• Open SQL Server Configuration Manager
• Expand SQL Server Network Configuration
• Select Protocols for <instance name> in the console panel
• Double-click the TCP protocol in the details panel and set Enabled to Yes
• Go to the IP Address tab and locate the IPAII section:
  • Clear any value from the TCP Dynamic Ports field
  • Enter a port number in the TCP Port field (default is 1433)
• Select SQL Server Services and restart your SQL Server instance

6. Configure SQL Server Authentication (Optional)

   If you’re using SQL Server authentication (rather than Windows authentication):

• Open SQL Server Management Studio
• Right-click your server and select Properties
• Select Security in the left panel
• Choose SQL Server and Windows Authentication mode under Server authentication
• Click OK
• Right-click your server and select Restart

Configuration

Options

These options allow the collector to connect to your MSSQL instance and collect transaction data from it.

via File

The configuration file name for this integration is netdata.conf. Configuration for this specific integration is located in the [plugin:windows:PerflibMSSQL] section within that file.

The file format is a modified INI syntax. The general structure is:

[section1]
    option1 = some value
    option2 = some other value

[section2]
    option3 = some third value

You can edit the configuration file using the edit-config script from the Netdata config directory.

cd /etc/netdata 2>/dev/null || cd /opt/netdata/etc/netdata
sudo ./edit-config netdata.conf

Examples

Single Instance

An example configuration with one instance.

[plugin:windows:PerflibMSSQL]
   driver = SQL Server
   instance = Dev
   server = 127.0.0.1\\Dev, 1433
   uid = netdata_user
   pwd = 1ReallyStrongPasswordShouldBeInsertedHere
   express = no

Multiple Instances

An example configuration with two instances.

[plugin:windows:PerflibMSSQL]
  driver = SQL Server
  server = 127.0.0.1\\Dev, 1433
  instance = Dev
  uid = netdata_user
  pwd = 1ReallyStrongPasswordShouldBeInsertedHere
  additional instances = 1
  express = no
[plugin:windows:PerflibMSSQL1]
  driver = SQL Server
  server = 127.0.0.1\\Production, 1434
  instance = Production
  uid = netdata_user
  pwd = AnotherReallyStrongPasswordShouldBeInsertedHere2
  express = no

Metrics

Metrics grouped by scope.

The scope defines the instance that the metric belongs to. An instance is uniquely identified by a set of labels.

Per MSSQL instance

These metrics refer to the Microsoft SQL Servers instances defined on host.

Labels:

Metrics:

Per MSSQL Jobs

These metrics refer to the Microsoft SQL Servers jobs on host.

Labels:

Metrics:

Per MSSQL Resource Locks

Monitors SQL Server resource locks by type. SQL Server uses locks to manage concurrent access to database resources during transactions, preventing conflicts when multiple users access the same data simultaneously. This metric tracks locks on different resource types like rows, pages, tables, and databases.

Labels:

Metrics:

Per MSSQL Waits

These metrics refer to the Microsoft SQL Server instances defined on the host and their associated wait events.

Labels:

Metrics:

Per Database

These metrics refer to Microsoft SQL Server databases.

Labels:

Metrics:

Per MSSQL Replication

These metrics refer to the Microsoft SQL Servers replication on host.

Labels:

Metrics:

Alerts

There are no alerts configured by default for this integration.