Top queries
How to enable?
Collection of top queries ordered by total duration can be enabled using the Queries feature set.
Prerequisites
- Query Store must be enabled on SQL Server instance.
- The database from which queries are collected is determined by:
- Explicit database name specified in the endpoint for monitoring configuration; or
- Default database configured for the connected user.
How often is the information updated?
Top queries are fetched by extension every 5 minutes.
How to list top queries?
The query below, when executed in Logs and Events, displays top queries, as observed within the most recent 5 min timeframe, using DQL:
fetch logs, from:now()-5m
| filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-server")
| filter matchesValue(event.group, "longest_queries")
| fields total_duration, avg_duration, content, server, instance, num_executions, query_plan
| sort asDouble(total_duration) desc
Description of fields:
total_duration field represents a sum of all executions of this query over the given 5 min timeframe in secondsavg_duration represents an average execution time of this query of the given 5 min timeframe in secondscontent field contains the SQL text of the query
On Managed tenants: log records can be retrieved by filtering logs using 2 attributes
dt.extension.name: com.dynatrace.extension.sql-server; andevent.group: longest_queries.
Largest files
How to enable?
Collection of largest database files by size can be enabled using the Database files feature set.
How often is the information updated?
Top database files by size are fetched by extension every 5 minutes.
How to list the largest database files by size?
The query below, when executed in Logs and Events, displays the largest database files, as observed within the most recent 5 min timeframe, by size using DQL:
fetch logs, from:now()-5m
| filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-server")
| filter matchesValue(event.group, "largest_files")
| fields content, file_size, file_type_desc, file_state_desc, database, server, instance, file_used_space, file_empty_space
| sort asDouble(file_size) desc
Description of fields:
content field represents the physical name of the file as handled by host OSfile_size is reported in KBfile_used_space is reported in KB and represents amount of space occupied by allocated pages within a specific filefile_empty_space is reported in KB and represents amount of space that is still empty within a specifc file
On Managed tenants: log records can be retrieved by filtering logs using 2 attributes
dt.extension.name: com.dynatrace.extension.sql-server; andevent.group: largest_files.
Current jobs
How to enable?
Monitoring of current jobs can be enabled using the Jobs feature set.
How often is the information updated?
Current jobs are fetched by extension every 5 minutes.
How to list current jobs?
The query below, when executed in Logs and Events, displays current jobs, as observed within the most recent 5 min timeframe, using DQL:
fetch logs, from:now()-5m
| filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-server")
| filter matchesValue(event.group, "current_jobs")
| fields job_name, job_status, content, enabled, last_run_outcome, duration, instance, server, start_execution_date, stop_execution_date
| sort asDouble(duration) desc
Description of fields:
content field represents the last execution outcome message.job_status and last_run_outcome are identical, except for two situations:
- When the job was not executed yet, the
job_status equals Idle
- When the job is currently being executed, the
job_status equals In Progress
duration represents complete job duration in seconds after execution is finished
On Managed tenants: log records can be retrieved by filtering logs using 2 attributes
dt.extension.name: com.dynatrace.extension.sql-server; andevent.group: current_jobs.
Failed jobs
How to enable?
Monitoring of failed jobs can be enabled using the Jobs feature set.
How often is the information updated?
Failed jobs are fetched by extension every 5 minutes.
How to list failed jobs?
The query below, when executed in Logs and Events, displays failed jobs, as observed within the most recent 5 min timeframe, using DQL:
fetch logs, from:now()-5m
| filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-server")
| filter matchesValue(event.group, "failed_jobs")
| fields job_name, step_name, outcome, content, duration, instance, server, sql_severity, retries_attempted, start_execution_date, stop_execution_date
| sort stop_execution_date desc
Description of fields:
content field represents the message of the last executed step and usually contains the error.outcome represents the final job status message as composed by SQL Server Agent.duration represents complete job duration in seconds after execution is finished
On Managed tenants: log records can be retrieved by filtering logs using 2 attributes
dt.extension.name: com.dynatrace.extension.sql-server; andevent.group: failed_jobs.
