sqlserverreceiver

README

Microsoft SQL Server Receiver

Status
Stability	development: logs
	beta: metrics
Distributions	contrib
Issues
Code coverage
Code Owners	@sincejune, @crobert-1 | Seeking more code owners!
Emeritus	@StefanKurek

The sqlserver receiver grabs metrics/logs about a Microsoft SQL Server instance. The receiver works by either using the Windows Performance Counters, or by directly connecting to the instance and querying it. Windows Performance Counters are only available when running on Windows.

Make sure to run the collector as administrator in order to collect all performance counters for metrics.

Configuration

The following is a generic configuration that can be used for the default logs and metrics scraped by the SQL Server receiver. A basic explanation on some of the fields has also been provided. For more information, please reference the following section.

sqlserver:
  collection_interval: 10s                     # interval for overall collection
  instance_name: CustomInstance
  username: myusername
  password: mypassword
  server: sqlserver.address
  port: 1433
  events:
    db.server.query_sample:
      enabled: true
    db.server.top_query:
      enabled: true
  top_query_collection:                        # this collection exports the most expensive queries as logs
    lookback_time: 60                          # which time window should we look for the top queries
    max_query_sample_count: 1000               # maximum number query we store in cache for top queries.
    top_query_count: 200                       # The maximum number of active queries to report in a single run.
    collection_interval: 60s                   # collection interval for top query collection specifically
  query_sample_collection:                     # this collection exports the currently (relate to the query time) executing queries as logs
    max_rows_per_query: 100                    # the maximum number of samples to return for one single query.

The following settings are optional:

• collection_interval (default = 10s): The interval at which metrics should be emitted by this receiver.
• instance_name (optional): The instance name identifies the specific SQL Server instance being monitored. If unspecified, metrics will be scraped from all instances. If configured, the computer_name must also be set when running on Windows.

Direct connection options (optional, but all must be specified to enable):

• username: The username used to connect to the SQL Server instance.
• password: The password used to connect to the SQL Server instance.
• server: IP Address or hostname of SQL Server instance to connect to.
• port: Port of the SQL Server instance to connect to.

For finer control over the direct connection use the datasource, a.k.a. the "connection string", instead. Note: it can't be used in conjunction with the username, password, server and port options.

Windows-specific options:

• computer_name (optional): The computer name identifies the SQL Server name or IP address of the computer being monitored. If specified, instance_name is also required to be defined. This option is ignored in non-Windows environments.

Top-Query collection specific options (only useful when top-query collection are enabled):

• lookback_time (optional, example = 60, default = 2 * collection_interval): The time window (in second) in which to query for top queries.
  • Queries that were finished execution outside the lookback window are not included in the collection. Increasing the lookback window (in seconds) will be useful for capturing long-running queries.
• max_query_sample_count (optional, example = 5000, default = 1000): The maximum number of records to fetch in a single run.
• top_query_count: (optional, example = 100, default = 200): The maximum number of active queries to report (to the next consumer) in a single run.
• collection_interval: (optional, default = 60s): The interval at which top queries should be emitted by this receiver.
  • This value can only guarantee that the top queries are collected at most once in this interval.
    • For instance, you have global collection_interval as 10s and top_query_collection.collection_interval as 60s.
      • In this case, the default receiver scraper will still try to run in every 10 seconds.
      • However, the top queries collection will only run after 60 seconds have passed since the last collection.
    • For instance, you have global collection_interval as 10s and top_query_collection.collection_interval as 5s.
      • In this case, top_query_collection.collection_internal will make no effects to the collection

Query sample collection related options (only useful when query sample is enabled)

• max_rows_per_query: (optional, default = 100) use this to limit rows returned by the sampling query. Example:

    receivers:
      sqlserver:
        collection_interval: 10s
      sqlserver/1:
        collection_interval: 5s
        username: sa
        password: securepassword
        server: 0.0.0.0
        port: 1433

When a named instance is used on Windows, a computer name and an instance name must be specified. Example with named instance:

    receivers:
      sqlserver:
        collection_interval: 10s
        computer_name: CustomServer
        instance_name: CustomInstance
        resource_attributes:
          sqlserver.computer.name:
            enabled: true
          sqlserver.instance.name:
            enabled: true

The full list of settings exposed for this receiver are documented in config.go with detailed sample configurations in testdata/config.yaml.

Top query collection enabled:

    receivers:
      sqlserver:
        collection_interval: 5s
        username: sa
        password: securepassword
        server: 0.0.0.0
        port: 1433
        top_query_collection:
          lookback_time: 60
          max_query_sample_count: 1000
          top_query_count: 200
        query_sample_collection:
          max_rows_per_query: 1450

Feature Gate

A new feature gate was added in v0.129.0 for removing the server.address and server.port resource attributes, as they are not identified as resources attributes in the semantic conventions. To enable it, pass the following argument to the Collector:

--feature-gates=receiver.sqlserver.RemoveServerResourceAttribute

Metrics

Details about the metrics produced by this receiver can be found in documentation.md

Logs

Details about the logs produced by this receiver can be found in logs-documentation.md

Known issues

SQL Server docker users may run into an issue that the collector fails to parse certificate from server due to x509: negative serial number. That's because we adopted Go 1.23 starting from contrib v0.121.0:

Before Go 1.23, ParseCertificate accepted certificates with negative serial numbers. This behavior can be restored by including "x509negativeserial=1" in the GODEBUG environment variable. references:

1. https://pkg.go.dev/crypto/x509#ParseCertificate
2. https://github.com/microsoft/mssql-docker/issues/895

Troubleshooting

service.instance.id is unknown:1433

In a rare case, the service.instance.id resource attribute is set to unknown:1433. This is because the receiver is unable to parse and compute the service.instance.id resource attribute.

You can file an issue that includes your configuration to help us investigate the issue.

Documentation

Index

• func NewFactory() receiver.Factory
• type Config
  • func (cfg *Config) Validate() error
• type QuerySample
• type TopQueryCollection

Functions

func NewFactory

func NewFactory() receiver.Factory

NewFactory creates a factory for SQL Server receiver.

Types

type Config

type Config struct {
	scraperhelper.ControllerConfig `mapstructure:",squash"`
	metadata.MetricsBuilderConfig  `mapstructure:",squash"`
	metadata.LogsBuilderConfig     `mapstructure:",squash"`
	// EnableTopQueryCollection enables the collection of the top queries by the execution time.
	// It will collect the top N queries based on totalElapsedTimeDiffs during the last collection interval.
	// The query statement will also be reported, hence, it is not ideal to send it as a metric. Hence
	// we are reporting them as logs.
	// The `N` is configured via `TopQueryCount`
	TopQueryCollection `mapstructure:"top_query_collection"`

	QuerySample `mapstructure:"query_sample_collection"`

	InstanceName string `mapstructure:"instance_name"`
	ComputerName string `mapstructure:"computer_name"`

	DataSource string `mapstructure:"datasource"`

	Password configopaque.String `mapstructure:"password"`
	Port     uint                `mapstructure:"port"`
	Server   string              `mapstructure:"server"`
	Username string              `mapstructure:"username"`
	// contains filtered or unexported fields
}

Config defines configuration for a sqlserver receiver.

func (*Config) Validate

func (cfg *Config) Validate() error

type QuerySample

type QuerySample struct {
	MaxRowsPerQuery uint64 `mapstructure:"max_rows_per_query"`
	// contains filtered or unexported fields
}

type TopQueryCollection

type TopQueryCollection struct {
	// Enabled enables the collection of the top queries by the execution time.
	// It will collect the top N queries based on totalElapsedTimeDiffs during the last collection interval.
	// The query statement will also be reported, hence, it is not ideal to send it as a metric. Hence
	// we are reporting them as logs.
	// The `N` is configured via `TopQueryCount`
	LookbackTime        uint          `mapstructure:"lookback_time"`
	MaxQuerySampleCount uint          `mapstructure:"max_query_sample_count"`
	TopQueryCount       uint          `mapstructure:"top_query_count"`
	CollectionInterval  time.Duration `mapstructure:"collection_interval"`
}