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

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:

Label	Description
mssql_instance	The instance name.

Metrics:

Metric	Dimensions	Unit
mssql.instance_user_connections	user	connections
mssql.instance_sqlstats_batch_requests	batch	requests/s
mssql.instance_sql_errors	errors	errors/s
mssql.instance_sqlstats_sql_compilations	compilations	compilations/s
mssql.instance_sqlstats_sql_recompilations	recompiles	recompiles/s
mssql.instance_sqlstats_auto_parameterization_attempts	failed	attempts/s
mssql.instance_sqlstats_safe_auto_parameterization_attempts	safe	attempts/s
mssql.instance_accessmethods_page_splits	page	splits/s
mssql.instance_cache_hit_ratio	hit_ratio	percentage
mssql.instance_bufman_iops	read, written	pages/s
mssql.instance_bufman_checkpoint_pages	log	pages/s
mssql.instance_bufman_page_life_expectancy	life_expectancy	seconds
mssql.instance_memmgr_server_memory	memory	bytes
mssql.instance_memmgr_connection_memory_bytes	memory	bytes
mssql.instance_memmgr_pending_memory_grants	pending	processes
mssql.instance_memmgr_external_benefit_of_memory	benefit	bytes
mssql.instance_resource_deadlocks	alloc_unit, application, database, extent, file, hobt, key, metadata, oib, object, page, rid, row_group, xact	deadlocks/s
mssql.instance_resource_lock_wait	alloc_unit, application, database, extent, file, hobt, key, metadata, oib, object, page, rid, row_group, xact	locks/s
mssql.instance_blocked_processes	blocked	processes

Per Database

These metrics refer to Microsoft SQL Server databases.

Labels:

Label	Description
mssql_instance	The instance name.
database	The database name.

Metrics:

Metric	Dimensions	Unit
mssql.database_active_transactions	active	transactions
mssql.database_transactions	transactions	transactions/s
mssql.database_write_transactions	write	transactions/s
mssql.database_lockwait	lock	locks/s
mssql.database_deadlocks	deadlocks	deadlocks/s
mssql.database_lock_timeouts	timeouts	timeouts/s
mssql.database_lock_requests	requests	requests/s
mssql.database_backup_restore_operations	backup	operations/s
mssql.database_log_flushes	log	flushes/s
mssql.database_log_flushed	flushed	bytes/s
mssql.database_data_files_size	size	bytes

Alerts

There are no alerts configured by default for this integration.

Setup

Prerequisites

Configure SQL Server for Monitoring

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', 'tempdb')
   
   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

Configuration

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

Options

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

Name	Description	Default	Required
driver	ODBC driver used to connect to the SQL Server.	SQL Server	no
server	Server address or instance name.	empty	yes
address	Alternative to server; supports named pipes if the server supports them.	empty	yes
uid	SQL Server user identifier.	empty	yes
pwd	Password for the specified user.	empty	yes
additional instances	Number of additional SQL Server instances to monitor.	0	no
windows authentication	Set to yes to use Windows credentials instead of SQL Server authentication.	no	no

Examples

Single Instance

An example configuration with one instance.

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

Multiple Instances

An example configuration with two instances.

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