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.
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 a receiver for the metrics that SQL Server produces and then create a pipeline for the new receiver.
This receiver does not support the use of multiple instances in the configuration, for example, to monitor multiple endpoints. All such instances write to the same time series, and Cloud Monitoring has no way to distinguish among them. The Ops Agent doesn't support SQL Server named instances.
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 tomssql
. - Set the
receiver_version
field to2
. - Use this receiver in your
default_pipeline
pipeline. This configuration overrides the built-in configuration of themssql
receiver.
- Name your receiver
To collect both v1 and v2 metrics, do the following:
- Name your receiver something other than
mssql
, likemssql_v2
. - Set the
type
field tomssql
. - Set the
receiver_version
to2
. - 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.
- Name your receiver something other than
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.
When querying a label, use the metric.labels.
prefix; for
example, metric.labels.LABEL="VALUE"
.
Metric type Launch stage Display name |
|
---|---|
Kind, Type, Unit Monitored resources |
Description Labels |
connections/user
GA
SQL Server open connections |
|
GAUGE , DOUBLE , 1
aws_ec2_instance gce_instance |
Currently open connections to SQL server. Sampled every 60 seconds. |
transaction_rate
GA
SQL Server transaction rate |
|
GAUGE , DOUBLE , 1/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 |
|
GAUGE , DOUBLE , 1/s
aws_ec2_instance gce_instance |
SQL server write transactions per second. Sampled every 60 seconds. |
Table generated at 2024-09-10 02:16:59 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
|
|
GAUGE , DOUBLE gce_instance |
|
workload.googleapis.com/sqlserver.batch.sql_compilation.rate
|
|
GAUGE , DOUBLE gce_instance |
|
workload.googleapis.com/sqlserver.batch.sql_recompilation.rate
|
|
GAUGE , DOUBLE gce_instance |
|
workload.googleapis.com/sqlserver.lock.wait.rate
|
|
GAUGE , DOUBLE gce_instance |
|
workload.googleapis.com/sqlserver.lock.wait_time.avg
|
|
GAUGE , DOUBLE gce_instance |
|
workload.googleapis.com/sqlserver.page.buffer_cache.hit_ratio
|
|
GAUGE , DOUBLE gce_instance |
|
workload.googleapis.com/sqlserver.page.checkpoint.flush.rate
|
|
GAUGE , DOUBLE gce_instance |
|
workload.googleapis.com/sqlserver.page.lazy_write.rate
|
|
GAUGE , DOUBLE gce_instance |
|
workload.googleapis.com/sqlserver.page.life_expectancy
|
|
GAUGE , INT64 gce_instance |
|
workload.googleapis.com/sqlserver.page.operation.rate
|
|
GAUGE , DOUBLE gce_instance |
type
|
workload.googleapis.com/sqlserver.page.split.rate
|
|
GAUGE , DOUBLE gce_instance |
|
workload.googleapis.com/sqlserver.transaction.rate
|
|
GAUGE , DOUBLE gce_instance |
database
|
workload.googleapis.com/sqlserver.transaction.write.rate
|
|
GAUGE , DOUBLE gce_instance |
database
|
workload.googleapis.com/sqlserver.transaction_log.flush.data.rate
|
|
GAUGE , DOUBLE gce_instance |
database
|
workload.googleapis.com/sqlserver.transaction_log.flush.rate
|
|
GAUGE , DOUBLE gce_instance |
database
|
workload.googleapis.com/sqlserver.transaction_log.flush.wait.rate
|
|
GAUGE , DOUBLE gce_instance |
database
|
workload.googleapis.com/sqlserver.transaction_log.growth.count
|
|
CUMULATIVE , INT64 gce_instance |
database
|
workload.googleapis.com/sqlserver.transaction_log.percent_used
|
|
GAUGE , INT64 gce_instance |
database
|
workload.googleapis.com/sqlserver.transaction_log.shrink.count
|
|
CUMULATIVE , INT64 gce_instance |
database
|
workload.googleapis.com/sqlserver.user.connection.count
|
|
GAUGE , INT64 gce_instance |
|
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.
To verify that SQL Server metrics are being sent to Cloud Monitoring, do the following:
-
In the Google Cloud console, go to the leaderboard Metrics explorer page:
If you use the search bar to find this page, then select the result whose subheading is Monitoring.
- In the toolbar of the query-builder pane, select the button whose name is either code MQL or code PromQL.
- Verify that MQL is selected in the Language toggle. The language toggle is in the same toolbar that lets you format your query.
- For v1 metrics, enter the following query in the editor, and then
click Run query:
fetch gce_instance | metric 'agent.googleapis.com/mssql/connections/user' | every 1m
- For v2 metrics, enter the following query in the editor, and then
click Run:
fetch gce_instance | metric 'workload.googleapis.com/sqlserver.user.connection.count' | every 1m
View dashboard
To view your SQL Server metrics, you must have a chart or dashboard configured. The SQL Server integration includes one or more dashboards for you. Any dashboards are automatically installed after you configure the integration and the Ops Agent has begun collecting metric data.
You can also view static previews of dashboards without installing the integration.
To view an installed dashboard, do the following:
-
In the Google Cloud console, go to the
Dashboards page:
If you use the search bar to find this page, then select the result whose subheading is Monitoring.
- Select the Dashboard List tab, and then choose the Integrations category.
- Click the name of the dashboard you want to view.
If you have configured an integration but the dashboard has not been installed, then check that the Ops Agent is running. When there is no metric data for a chart in the dashboard, installation of the dashboard fails. After the Ops Agent begins collecting metrics, the dashboard is installed for you.
To view a static preview of the dashboard, do the following:
-
In the Google Cloud console, go to the
Integrations page:
If you use the search bar to find this page, then select the result whose subheading is Monitoring.
- Click the Compute Engine deployment-platform filter.
- Locate the entry for SQL Server and click View Details.
- Select the Dashboards tab to see a static preview. If the dashboard is installed, then you can navigate to it by clicking View dashboard.
For more information about dashboards in Cloud Monitoring, see Dashboards and charts.
For more information about using the Integrations page, see Manage integrations.
Install alerting policies
Alerting policies instruct Cloud Monitoring to notify you when specified conditions occur. The SQL Server integration includes one or more alerting policies for you to use. You can view and install these alerting policies from the Integrations page in Monitoring.
To view the descriptions of available alerting policies and install them, do the following:
-
In the Google Cloud console, go to the
Integrations page:
If you use the search bar to find this page, then select the result whose subheading is Monitoring.
- Locate the entry for SQL Server and click View Details.
- Select the Alerts tab. This tab provides descriptions of available alerting policies and provides an interface for installing them.
- Install alerting policies. Alerting policies need
to know where to send notifications that the alert has been
triggered, so they require information from you for installation.
To install alerting policies, do the following:
- From the list of available alerting policies, select those that you want to install.
In the Configure notifications section, select one or more notification channels. You have the option to disable the use of notification channels, but if you do, then your alerting policies fire silently. You can check their status in Monitoring, but you receive no notifications.
For more information about notification channels, see Manage notification channels.
- Click Create Policies.
For more information about alerting policies in Cloud Monitoring, see Introduction to alerting.
For more information about using the Integrations page, see Manage integrations.
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.