How to use Dynatrace for SQL Query Monitoring

Dynatrace offers an enhanced way to extend monitoring capabilities by allowing custom SQL queries to gather specific database metrics. This content will walk you through the steps to utilize Extensions 2.0 for SQL extensions, replacing custom queries with the new extension format.

Introduction

Extensions 2.0 in Dynatrace provides a robust and flexible framework for adding custom monitoring capabilities. For database monitoring, you can use SQL extensions to execute custom queries, collect metrics, and visualize them within Dynatrace. This guide will help you transition from traditional custom queries to using Extensions 2.0 effectively.

Prerequisites

1. Dynatrace Environment: Ensure you have access to your Dynatrace environment.

2. Extension Developer Tool: Dynatrace provides tools and SDKs for developing and managing extensions.

3. Database Access: Ensure you have permission to execute custom SQL queries on your database.

Step-by-Step Guide

Step 1: Install Extension Developer Tool

1. Download and Install: Download the Dynatrace Extension Developer Tool from the Dynatrace website or GitHub repository.

2. Configure Tool: Configure the tool with your Dynatrace API token and environment details.

Step 2: Create a New Extension

1. Initialize Extension: Use the developer tool to initialize a new extension project by using dt extension create --name "CustomSQLMonitoring"

2. Create yaml file: In the extension.yaml file, define the metadata for your extension, including name, version, author, and description

Step 3: Define Custom Queries

1. Create SQL Queries: Define your custom SQL queries to gather the necessary metrics from your database.

2. Add Queries to Extension: In the extension.yaml file, add your SQL queries under the metrics section. Specify the query, expected output, and how the data should be parsed and processed.

Step 4: Assemble and Sign Extension

dt extension assemble

dt extension sign --key secrets/developer.pem

dt extension upload bundle.zip

metrics:

  - key: my.sql.custom.totalcpucount

    metadata:

      displayName:Custom Total CPU count

      unit: count

  - key: my.sql.custom.totalcputime

    metadata:

      displayName:Custom Total CPU Time

      unit: sum

sqlMySql:

  - group: Utilized CPU Count

    featureSet: "UtilizedCPUCount"

    interval:

      minutes: 1

    ingest: metrics

    query: >

      SELECT COUNT( * ) AS totalcpucount

      FROM sys.dm_os_schedulers

      WHERE status = 'VISIBLE ONLINE'

    metrics:

      - key: my.sql.custom.totalcpucount

        value: col:totalcpucount

    dimensions:

      - key: stat_name

        value: const:cpucnt

  - group: CPU Time

    featureSet: "TotalCPUTime"

    interval:

      minutes: 1

    ingest: metrics

    query: >

        SELECT SUM(cpu_time) AS totalcputime FROM sys.dm_exec_requests

    metrics:

        - key: my.sql.custom.totalcputime

          value: col:totalcputime

    dimensions:

        - key: stat_name

          value: const:cputime

Step 5: Configure Extension

1. Upload Extension: Once tested, upload your extension to your Dynatrace environment using Extension Manager Ref:- https://docs.dynatrace.com/docs/extend-dynatrace/extend-metrics/ingestion-methods/sql/microsoft-sql dt extension upload --path ./CustomSQLMonitoring

2. Activate Extension: Activate the extension within the Dynatrace UI, ensuring it is configured to collect data from your specified database.

Step 6: Configure Dashboards and Alerts

Use Dynatrace's dashboard capabilities to visualize the data collected by your SQL extension.

Comparison of DB Query Plugin to Extensions 2.0 Database Extension

DB Query Plugin using Extension 1.0

Previously, you had a script running custom SQL queries and pushing the results to Dynatrace via the API. For example:

SELECT query, execution_time FROM query_performance;

Then, push results manually or via a custom script.

Database Extension using Extensions 2.0

With Extensions 2.0, the same query is embedded in the extension configuration:

- group: Execution Time

    featureSet: "ExecutionTime"

    interval:

      minutes: 1

    ingest: metrics

    query: >

      SELECT query, execution_time

      FROM query_performence

    metrics:

      - key: my.sql.custom.totalcpucount

        value: col: execution_time

    dimensions:

      - key: stat_name

        value: const:executiontime

Conclusion

Using Extensions 2.0 for SQL Extensions in Dynatrace simplifies and enhances the process of monitoring database performance. Following these steps, you can replace traditional custom queries with a more integrated and manageable approach, leveraging Dynatrace's robust monitoring and visualization tools. This ensures efficient database monitoring, improving application performance and reliability.

Here are some helpful starting points:

A Practical Guide to SQL Extensions with Dynatrace (YouTube)

SQL Data Sources - Dynatrace Docs(https://docs.dynatrace.com/docs/extend-dynatrace/extensions20/data-sources/sql and https://docs.dynatrace.com/docs/extend-dynatrace )