This step-by-step tutorial will help you diagnose why long-running queries may not be reported by the Dynatrace Snowflake Observability Agent.
- Prerequisites
- Understanding the
active_queriesPlugin - Troubleshooting Methodology
- Step 1: Verify Agent Task Execution
- Step 2: Validate Data in Dynatrace
- Step 3: Deep Dive Analysis for Missing Queries
- Troubleshooting Decision Tree
- Resources and Tools
- Summary
Before starting this troubleshooting guide, ensure you have:
- Access to both Snowflake and Dynatrace environments
DTAGENT_VIEWERrole permissions in Snowflake- Basic understanding of SQL queries and Dynatrace navigation
The active_queries plugin is responsible for collecting query information from Snowflake. Understanding its behavior is crucial for effective troubleshooting:
- Collects currently
RUNNING,QUEUED, and recentlyFINISHEDqueries - Captures queries that intersect with plugin execution intervals (see diagram below)
- Executes every 10 minutes by default
- Includes all FAST mode functionality
- Additionally retrieves all queries that
FINISHEDorFAILEDsince the previous execution - Looks back 2 minutes before the end of the last reported query
- More comprehensive but slower
QUERY_HISTORY() function returns only the most recent 10,000 queries. This is a hard limit that can affect data collection in high-volume environments.
This guide follows a systematic approach to identify and resolve issues with missing long-running queries. Follow these steps in order for the most effective diagnosis.
Objective: Confirm that the active_queries plugin is running correctly on the Snowflake side.
First, verify that the Dynatrace Snowflake Observability Agent tasks are executing without errors:
- Task Name:
DTAGENT_DB.APP.TASK_DTAGENT_ACTIVE_QUERIES - What to Check:
- Task executes without errors
- Task runs at expected cadence (10 minutes by default)
- Some task executions might be missing if a task ran longer than the next scheduled execution
Execute the following SQL to check the plugin's recent activity:
-- Listing last 50 executions of active_queries plugin,
-- with number of queries processed and time since previous processing
use role DTAGENT_VIEWER;
use schema DTAGENT_DB.STATUS;
use warehouse DTAGENT_WH;
select
PROCESS_TIME,
LAST_TIMESTAMP,
ENTRIES_COUNT:"processed_entries_cnt" as PROCESSED_ENTRIES_COUNT,
timediff(second,
LAG(PROCESS_TIME)
OVER (ORDER BY PROCESS_TIME),
PROCESS_TIME) as PROCESSING_GAP_SEC,
from STATUS.PROCESSED_MEASUREMENTS_LOG
where MEASUREMENTS_SOURCE = 'active_queries'
order by PROCESS_TIME desc
limit 50
;
Objective: Confirm that data from the active_queries plugin is correctly transmitted to and available in Dynatrace Grail.
Check if the plugin executions are being reported to Dynatrace:
-
Data Sources to Check:
- Business events (bizevents)
- Query history records
- Both sources should show consistent data
-
Metrics to Compare:
- Execution cadence (should match Snowflake task execution)
- Running time of the plugin
- Number of queries analyzed per execution
Examine the types of queries being reported:
-
Expected Status Types:
RUNNINGqueries should always be present (this is the most critical indicator)QUEUEDqueries may be present depending on workloadFINISHEDqueries should be present in both modes
-
Red Flags:
- No
RUNNINGqueries reported consistently - Unusual status distributions
- Missing expected query types
- No
Search for queries that should have been reported but appear to be missing:
- Query Characteristics to Check:
- Long execution times not reflected in
snowflake.time.runningmetric - Queries not reported with
RUNNINGstatus despite long execution - Discrepancies between expected and actual execution times
- Long execution times not reflected in
✅ Healthy Data Flow:
- Plugin executions visible in Dynatrace
- Consistent cadence matching Snowflake task execution
RUNNINGqueries always present- Query execution times match expectations
❌ Warning Signs:
- Missing plugin execution reports
- No
RUNNINGqueries reported - Significant discrepancies in execution times
- Gaps in data transmission
Objective: When Steps 1 and 2 don't reveal clear issues, perform detailed analysis to identify specific missing queries and their root causes.
If you've reached this step without finding obvious problems, you need to systematically identify queries that should have been reported but weren't:
- Identify Candidate Queries:
- Focus on queries with execution times longer than expected reporting intervals
- Look for queries that ran during plugin execution windows
- Document query IDs, start times, and execution durations
At Dynatrace: Check if the missing queries appear in Grail with incorrect data:
-
Search Strategy:
- Look up specific query IDs in Dynatrace
- Compare reported execution times with actual execution times
- Check if queries appear with unexpected status values
-
Data Validation:
- Verify query execution time accuracy
- Confirm status transitions are properly captured
- Check for any partial data reporting
At Snowflake: Verify that missing queries were actually visible to the QUERY_HISTORY() function:
- Use the provided SQL script:
t00_test_missing_active_queries.off.sql - Check Query Accessibility:
- Confirm queries appear in
QUERY_HISTORY()results - Verify timing of query availability
- Check if queries were within the 10,000 query limit
- Confirm queries appear in
Consistent Missing Queries:
- Queries systematically missing from all reporting intervals
- May indicate permission issues or query visibility problems
Partial Missing Queries:
- Queries missing from some intervals but present in others
- Missing from beginning, end, or middle of execution periods
- May indicate timing issues or plugin execution problems
Intermittent Missing Queries:
- Sporadic missing queries without clear pattern
- May indicate performance issues or temporary connectivity problems
🔍 If queries are missing from Snowflake query history:
- Check user permissions and role assignments
- Verify query visibility settings
- Review Snowflake query history retention policies
🔍 If queries are visible in Snowflake but missing from Dynatrace:
- Check data transmission connectivity
- Review plugin execution timing
- Verify Dynatrace ingestion pipeline
🔍 If queries are partially reported:
- Analyze timing relationships between query execution and plugin runs
- Check for plugin execution timeout issues
- Review query status transition timing
Start: Long-running queries not reported
↓
Step 1: Are agent tasks running correctly?
├─ No → Fix task execution issues, check permissions
└─ Yes → Continue to Step 2
↓
Step 2: Is data reaching Dynatrace?
├─ No → Check connectivity, data pipeline
└─ Yes → Continue to Step 3
↓
Step 3: Are specific queries missing?
├─ From Snowflake → Check permissions, visibility
├─ From Dynatrace → Check data transmission
└─ Partially → Analyze timing and execution patterns
- Snowflake SQL script
- Dynatrace notebook for testing current version ( version 0.7, version 0.8.2 )
This systematic approach helps identify the root cause of missing long-running queries by:
- Verifying the data collection source (Snowflake agent tasks)
- Confirming data transmission (Dynatrace data availability)
- Performing detailed analysis (specific query investigation)
Each step builds upon the previous one, allowing you to isolate the problem area and apply targeted solutions. Remember that the 10,000 query limit in Snowflake's QUERY_HISTORY() function is a critical constraint that affects data collection in high-volume environments.