SQLServer

·

SQL Server Collector collects SQL Server waitstats, database_io and other related metrics.

Configuration¶

SQL Server version >= 2008, tested version:

• 2017
• 2019
• 2022

Prerequisites¶

• SQL Server version >= 2019

• Create a user:

Linux、Windows:

USE master;
GO
CREATE LOGIN [datakit] WITH PASSWORD = N'yourpassword';
GO
GRANT VIEW SERVER STATE TO [datakit];
GO
GRANT VIEW ANY DEFINITION TO [datakit];
GO

Aliyun RDS SQL Server:

USE master;
GO
CREATE LOGIN [datakit] WITH PASSWORD = N'yourpassword';
GO

Collector Configuration¶

[[inputs.sqlserver]]
  ## your sqlserver host ,example ip:port
  host = ""

  ## your sqlserver user,password
  user = ""
  password = ""

  ## Instance name. If not specified, a connection to the default instance is made.
  instance_name = ""

  ## Database name to query. Default is master.
  database = "master"

  ## by default, support TLS 1.2 and above.
  ## set to true if server side uses TLS 1.0 or TLS 1.1
  allow_tls10 = false

  ## connection timeout default: 30s
  connect_timeout = "30s"

  ## Metric name in metric_exclude_list will not be collected.
  metric_exclude_list = [""]

  ## parameters to be added to the connection string
  ## Examples:
  ##   "encrypt=disable"
  ##   "certificate=/path/to/cert.pem"
  ## reference: https://github.com/microsoft/go-mssqldb?tab=readme-ov-file#connection-parameters-and-dsn 
  #
  # connection_parameters = "encrypt=disable"

  ## (optional) collection interval, default is 10s
  interval = "10s"

  ## Set true to enable election
  election = true

  ## configure db_filter to filter out metrics from certain databases according to their database_name tag.
  ## If leave blank, no metric from any database is filtered out.
  # db_filter = ["some_db_instance_name", "other_db_instance_name"]

  ## collect object
  [inputs.sqlserver.object]
    # Set true to enable collecting objects
    enabled = true

    # interval to collect sqlserver object which will be greater than collection interval
    interval = "600s"

  ## Run a custom SQL query and collect corresponding metrics.
  #
  # [[inputs.sqlserver.custom_queries]]
  #   sql = '''
  #     select counter_name,cntr_type,cntr_value
  #     from sys.dm_os_performance_counters
  #   '''
  #   metric = "sqlserver_custom_stat"
  #   tags = ["counter_name","cntr_type"]
  #   interval = "10s"
  #   fields = ["cntr_value"]

  # [inputs.sqlserver.log]
  # files = []
  # #grok pipeline script path
  # pipeline = "sqlserver.p"

  [inputs.sqlserver.tags]
  # some_tag = "some_value"
  # more_tag = "some_other_value"

Log Collector Configuration¶

To collect SQL Server logs, enable files in sqlserver.conf and write to the absolute path of the SQL Server log file. For example:

[[inputs.sqlserver]]
    ...
    [inputs.sqlserver.log]
        files = ["/var/opt/mssql/log/error.log"]

When log collection is turned on, a log with a log (aka source) ofsqlserver is collected.

Metrics¶

For all of the following data collections, the global election tags will be added automatically, we can add extra tags in [inputs.sqlserver.tags] if needed:

 [inputs.sqlserver.tags]
  # some_tag = "some_value"
  # more_tag = "some_other_value"
  # ...

sqlserver¶

sqlserver_performance¶

sqlserver_waitstats¶

sqlserver_database_io¶

sqlserver_schedulers¶

sqlserver_volumespace¶

sqlserver_database_size¶

sqlserver_database_backup¶

sqlserver_database_files¶

collector¶

Object¶

database¶

SQLServer object metrics( Version-1.78.0)

message Metric Field Structure¶

The basic structure of the message field is as follows:

{
  "setting": [ # settings information
    {
      "name": "recovery interval (min)",
      "value": "123",
      "value_in_use": "0",
      "maximum": "10",
      "minimum": "0",
      "is_dynamic": true,
      "is_advanced": true
    }
    ...
  ],

  "databases": [ # databases information
    {
      "name": "db1",
      "owner_name": "dbo",
      "collation": "SQL_Latin1_General_CP1_CI_AS",
      "schemas": [
        {
          "name": "schema1",
          "owner_name": "dbo",
          "tables": [
            {
              "name": "table1",
              "columns": [], # columns information
              "indexes": [], # indexes information
              "foreign_keys": [], # foreign keys information
              "partitions": { # partitions information
                "partition_count": 1
              } 
            }
            ...
          ]
        }
      ]
    }
    ...
  ]
}

setting¶

The data in the setting field is derived from the sys.configurations system view, which contains information about the global variables of the SQL Server instance. For detailed fields, you can refer to the SQL Server documentation.

databases¶

The databases field stores information about all databases on the SQL Server instance. The information for each database is as follows:

schemas¶

The schemas field stores information about all schemas in the database. The information for each schema is as follows:

The tables field stores information about all tables included in the schema. The information for each table is as follows:

The tables.columns field stores information about all columns included in the table. The information for each column is as follows:

The tables.indexes field stores information about all indexes included in the table. The information for each index is as follows:

The tables.foreign_keys field stores information about all foreign keys included in the table. The information for each foreign key is as follows:

The tables.partitions field stores the number of all partitions included in the table. The specific field description is as follows:

Logging¶

Following measurements are collected as logs with the level of info.

sqlserver_lock_row¶

sqlserver_lock_table¶

sqlserver_lock_dead¶

sqlserver_logical_io¶

sqlserver_worker_time¶

Pipeline for SQLServer logging¶

• SQL Server Common Log Pipeline

Example of common log text:

2021-05-28 10:46:07.78 spid10s     0 transactions rolled back in database 'msdb' (4:0). This is an informational message only. No user action is required

The list of extracted fields are as follows: