Microsoft SQL Server

The Microsoft SQL Server integration collects transaction, connection, and other metrics from your SQL Server instances.

For more information about SQL Server, see the SQL Server documentation.

Before you begin

The Ops Agent version 2.15.0 introduces support for a new set of SQL Server metrics using the workload.googleapis.com/ prefix. You must configure the agent to collect these metrics, as described in Example configuration.

The Ops Agent has always supported a limited set of SQL Servier metrics; these metrics use the agent.googleapis.com/mssql/ prefix. No additional configuration is needed to collect these metrics on Windows VMs.

This document refers to the agent-prefixed metrics as the v1 metrics and to the workload-prefixed metrics as the v2 metrics.

  • The v1 metrics include transaction-rate and connection-count information.
  • The v2 metrics include batch, lock, page, and user connection-count information at the instance level, and transaction and transaction-log information at the database level.

For tables of these metrics, see What is monitored.

Starting with the Ops Agent version 2.15.0, you can configure the agent to collect v1 metrics, v2 metrics, or both.

Prerequisites

To collect SQL Server telemetry, you must install the Ops Agent:

  • For metrics v1, install version 1.0.0 or higher.
  • For metrics v2, install version 2.15.0 or higher.

This integration supports SQL Server versions 11.x, 12.x, 13.x, 14.x, and 15.x.

Configure the Ops Agent for SQL Server

When using the Ops Agent on a Microsoft Windows VM, the agent automatically collects SQL Server v1 metrics. No additional configuration is required.

Following the guide to configure the Ops Agent, add the required elements to collect telemetry from SQL Server instances, and restart the agent.

After setting up a new SQL Server instance, you might not immediately see metrics display in Metrics Explorer. The Ops Agent begins collecting SQL Server metrics only after there is database activity to report.

Example configuration

The following command creates the configuration to collect and ingest telemetry for SQL Server and restarts the Ops Agent.

# This config gets merged with the built-in Ops Agent config, which already
# includes the v1 receiver in the default pipeline.
# Because the v2 receiver here uses a unique ID, it does not overwrite
# the default receiver and so both receivers will be active simultaneously
# during the test.

# Create a back up of the existing file so existing configurations are not lost.
Copy-Item -Path 'C:\Program Files\Google\Cloud Operations\Ops Agent\config\config.yaml' -Destination 'C:\Program Files\Google\Cloud Operations\Ops Agent\config\config.yaml.bak'

# Configure the Ops Agent.
Add-Content 'C:\Program Files\Google\Cloud Operations\Ops Agent\config\config.yaml' "
metrics:
  receivers:
    mssql_v2:
      type: mssql
      receiver_version: 2
  service:
    pipelines:
      mssql_v2:
        receivers:
        - mssql_v2
"

Restart-Service google-cloud-ops-agent -Force

Configure v1 metrics collection

The Ops Agent on a Microsoft Windows VM automatically collects SQL Server v1 metrics. No additional configuration is required. For information about collecting only v2 metrics, see Configure v2 metrics collection.

After you set up a new SQL Server instance, SQL Server might not immediately report any metrics. The Ops Agent begins collecting SQL Server metrics after there is database activity to report.

Configure v2 metrics collection

To ingest v2 metrics from SQL Server, you must create receivers for the metrics that SQL Server produces and then create a pipeline for the new receivers.

To configure a receiver for your mssql metrics, specify the following fields:

Field Default Description
collection_interval 60s A time.Duration value, such as 30s or 5m.
receiver_version Version of the metrics collected; use 2 to collect v2 metrics.
type This value must be mssql.

By default, the v1 metrics are collected automatically. You can either augment the v1 metrics by additionally collecting v2 metrics, or you can override the v1 metrics to collect only v2 metrics.

  • To collect only v2 metrics, do the following:

    • Name your receiver mssql.
    • Set the type field to mssql.
    • Set the receiver_version field to 2.
    • Use this receiver in your default_pipeline pipeline. This configuration overrides the built-in configuration of the mssql receiver.
  • To collect both v1 and v2 metrics, do the following:

    • Name your receiver something other than mssql, like mssql_v2.
    • Set the type field to mssql.
    • Set the receiver_version to 2.
    • Use this receiver in your new pipeline. This configuration adds a v2 receiver rather than overriding the built-in receiver. The script in Example configuration uses this approach.

What is monitored

Starting with version 2.15.0, the Ops Agent can collect two different sets of metrics:

  • The v1 metrics: agent.googleapis.com/mssql/.
  • The v2 metrics: workload.googleapis.com/.
V1 metrics

The following table provides the list of v1 metrics that the Ops Agent collects from the SQL Server instance.

The "metric type" strings in this table must be prefixed with agent.googleapis.com/mssql/. That prefix has been omitted from the entries in the table.

Metric type Launch stage
Display name
Kind, Type, Unit
Monitored resources
Description
Labels
connections/user GA
SQL Server open connections
GAUGEDOUBLE1
aws_ec2_instance, gce_instance
Currently open connections to SQL server. Sampled every 60 seconds.
transaction_rate GA
SQL Server transaction rate
GAUGEDOUBLE1/s
aws_ec2_instance, gce_instance
SQL server total transactions per second. Sampled every 60 seconds.
write_transaction_rate GA
SQL Server write transaction rate
GAUGEDOUBLE1/s
aws_ec2_instance, gce_instance
SQL server write transactions per second. Sampled every 60 seconds.

Table generated at 2022-08-11 16:30:36 UTC.

V2 metrics

The following table provides the list of v2 metrics that the Ops Agent collects from the SQL Server instance.

Metric type 
Kind, Type
Monitored resources
Labels
workload.googleapis.com/sqlserver.batch.request.rate
GAUGEDOUBLE
gce_instance
 
workload.googleapis.com/sqlserver.batch.sql_compilation.rate
GAUGEDOUBLE
gce_instance
 
workload.googleapis.com/sqlserver.batch.sql_recompilation.rate
GAUGEDOUBLE
gce_instance
 
workload.googleapis.com/sqlserver.lock.wait.rate
GAUGEDOUBLE
gce_instance
 
workload.googleapis.com/sqlserver.lock.wait_time.avg
GAUGEDOUBLE
gce_instance
 
workload.googleapis.com/sqlserver.page.buffer_cache.hit_ratio
GAUGEDOUBLE
gce_instance
 
workload.googleapis.com/sqlserver.page.checkpoint.flush.rate
GAUGEDOUBLE
gce_instance
 
workload.googleapis.com/sqlserver.page.lazy_write.rate
GAUGEDOUBLE
gce_instance
 
workload.googleapis.com/sqlserver.page.life_expectancy
GAUGEINT64
gce_instance
 
workload.googleapis.com/sqlserver.page.operation.rate
GAUGEDOUBLE
gce_instance
type
workload.googleapis.com/sqlserver.page.split.rate
GAUGEDOUBLE
gce_instance
 
workload.googleapis.com/sqlserver.transaction.rate
GAUGEDOUBLE
gce_instance
database
workload.googleapis.com/sqlserver.transaction.write.rate
GAUGEDOUBLE
gce_instance
database
workload.googleapis.com/sqlserver.transaction_log.flush.data.rate
GAUGEDOUBLE
gce_instance
database
workload.googleapis.com/sqlserver.transaction_log.flush.rate
GAUGEDOUBLE
gce_instance
database
workload.googleapis.com/sqlserver.transaction_log.flush.wait.rate
GAUGEDOUBLE
gce_instance
database
workload.googleapis.com/sqlserver.transaction_log.growth.count
CUMULATIVEINT64
gce_instance
database
workload.googleapis.com/sqlserver.transaction_log.percent_used
GAUGEINT64
gce_instance
database
workload.googleapis.com/sqlserver.transaction_log.shrink.count
CUMULATIVEINT64
gce_instance
database
workload.googleapis.com/sqlserver.user.connection.count
GAUGEINT64
gce_instance
 

Sample dashboard

To view your SQL Server metrics, you must have a chart or dashboard configured. Cloud Monitoring provides a library of sample dashboards for integrations, which contain preconfigured charts. For information about installing these dashboards, see Installing sample dashboards.

Verify the configuration

This section describes how to verify that you correctly configured the SQL Server receiver. It might take one or two minutes for the Ops Agent to begin collecting telemetry after there is database activity to report.

To verify that the metrics are ingested, go to Metrics Explorer and run one of the following queries in the MQL tab.

  • For v1 metrics:

    fetch gce_instance
    | metric 'agent.googleapis.com/mssql/connections/user'
    | every 1m
    
  • For v2 metrics:

    fetch gce_instance
    | metric 'workload.googleapis.com/sqlserver.user.connection.count'
    | every 1m
    

What's next

For a walkthrough on how to use Ansible to install the Ops Agent, configure a third-party application, and install a sample dashboard, see the Install the Ops Agent to troubleshoot third-party applications video.