• Home
  • Observe and explore
  • Query data
  • Dynatrace Query Language
  • DQL Best practices

DQL Best practices

This page describes actions you can take to improve query performance.

Narrow the query time range

A shorter analysis window provides better performance based on identical data sets. Use available timeframe selectors provided by the user interface or directly specify the query time range within the fetch command.

dql
fetch bizevents, from:-10m

Utilize available sampling options

Currently, sampling is applicable for Log data within the initial fetch pipeline statement. Depending on the specified value, a fraction (1/<samplingRatio>) of all available raw log records is returned.

The applicable value ranges for sampling are:

  • 1: Default value, resulting in no applied sampling.
  • 10
  • 100
  • 1000
  • 10000

The following query uses sampling to improve query performance to observe an approximation of error logs over time.

dql
fetch logs, from:-7d, samplingRatio:100 | summarize c = countIf(loglevel == "ERROR"), by:bin(timestamp, 3h) | fieldsAdd c = c*100

Utilize options to limit the scanned amount of data

Particularly when dealing with logs, a large amount of data is ingested in short periods. Even with a narrow query time range, a query can take considerable time to complete.

To stop reading data after a specified amount, use the scanLimitGBytes fetch command optional parameter.

dql
fetch logs, from:-2h, scanLimitGBytes:100

Recommended order of commands

It is advised to use the below order in your DQL queries:

  1. fetch
  2. Commands, for example filter, fields, fieldsAdd, parse
  3. Functions, for example summarize
  4. sort
  5. limit

Example query with the recommended order:

dql
fetch logs | filter isNotNull(dt.entity.host) | filterOut loglevel == "NONE" | summarize count = count(), by: {dt.entity.host, loglevel} | sort loglevel asc, count desc | limit 3

It is recommended to place sort at the end of the query. Sorting right after fetch and then continuing the query will reduce the query performance. Example:

dql
fetch logs | sort timestamp desc | filter contains(content, "Error")

However, the query will run smoothly once you change the order to:

dql
fetch logs | filter contains(content, "Error") | sort timestamp desc

You can repeat the same command within one query and still stick to the recommended order. In the below example, you first filter the fetched content, then again you filter the parsed content, but the sort command and summarize function retain their positions:

dql
fetch logs | filter dt.entity.host == "HOST-1" and dt.entity.process_group_instance == "PROCESS_GROUP_INSTANCE-123AB35" | parse content, "ipaddr:ip ld ' POST ' ld:action ' HTTP/1.1 ' long:status ld" | filter action == "/cart" or action == "/cart/checkout" | summarize count(), by:{ip, log.source} | sort timestamp desc

Filter early

The overall query performance is greatly improved by narrowing the dataset before further processing.

  • For business event queries, filter on any ingested field.

    dql
    fetch bizevents | filter event.provider == "www.easytrade.com"
  • For log queries, filter on fields representing the topological context of your data.

    dql
    fetch logs | filter k8s.container.name == "coredns" and (loglevel != "NONE" or loglevel != "INFO")

    Commonly used fields within logs:

    • log.source
    • loglevel
    • host.name
    • dt.process.name
    • event.type
    • dt.kubernetes.cluster.name
    • k8s.namespace.name
    • k8s.deployment.name
    • k8s.container.name
    • k8s.pod.name
    • dt.host_group.id
    • dt.event.group_label
    • dt.entity.host
    • aws-log_group

Use string comparisons with care

  • Use == or != whenever the value of a field is known.

    dql
    fetch logs | filter k8s.container.name == "coredns" and (loglevel != "NONE" or loglevel != "INFO")
  • Use matchesPhrase() instead of contains() whenever the value of a field is only partly known or unknown.

    dql
    fetch logs | filter matchesPhrase(content,"No files matching import")

Fields names to be avoided or used in backticks

It is not recommended to use the below eight reserved keywords as field identifiers (field names) or dimensions:

  • true
  • false
  • null
  • mod
  • and
  • or
  • xor
  • not

However, you can still use these words as field names, identifiers and dimensions if you put them in backticks ('`')

For example, if you have a dimension named 'true':

dql
... | fields x = true // creates a boolean field that is always true
dql
... | fields x = `true` // allows to access the custom dimension named 'true'

Similarly, if you need to sort by a field named 'not':

dql
... | sort not desc // sorts by a boolean value of dimension `desc`
dql
... | sort `not` desc // sorts descending by a field named `not`
Related topics
  • Dynatrace Query Language

    How to use Dynatrace Query Language.

  • How to use DQL queries

    Find out how DQL works and what are DQL key concepts.

  • DQL compared to SQL and more

    See how DQL compares to other query languages.

  • DQL language reference

    Dynatrace Query Language syntax reference.

  • DQL commands

    A list of DQL commands.

  • DQL functions

    A list of DQL Functions.

  • DQL operators

    A list of DQL Operators.

  • DQL data types

    A list of DQL data types.