Basics of reading a query profile

Created by Steve Place, Modified on Mon, 13 Nov 2023 at 03:20 PM by Steve Place

(To learn how to get the query profile, see this article.)


If you've opened a ticket about query performance, Support has almost certainly asked you for a query profile. You can help us diagnose your issue faster by trying to parse the profile yourself. The output can be complicated, but there are some basics that anyone can understand.


Profiling results


At the top, you'll see "Profiling result". (If you do not see this, you're looking at the query plan. Return to the above-linked article and get the profile.) The next line will be:

Query executed in X ms and returned Y result(s)

or something like:

Query executed in X ms and hit an error: OperatorException.Cancelled: Query execution cancelled: Execution time exceeded query timeout 300000
(note error messages may vary)


Below that is total used memory and pre-processing/post-execution time.


What you're looking for in this section:

  • Is the query executing slower, possibly much slower, than I'm expecting?
  • Is the query returning the expected number of results?
  • Am I getting an error?
  • Is the query using more memory than I'm expecting?
  • Is pre-processing/post-execution time taking longer than I'm expecting?

These times all vary widely depending on your data, so use your queries that are behaving normally as benchmarks.


Prefixes


These have no bearing on the query profile and can be ignored for the purposes of troubleshooting query performance.


The query plan + metrics


The first 1-2 lines may specify the scope of the query (e.g., "From local" or "From named local named"). You can ignore these unless you think your query is returning incorrect results because you're querying the wrong dataset.


After this, you'll see query hints you specified (if any). They look like #pragma [hint]=option (e.g., #pragma join.bind=OFF). Note query hints may appear in other parts of the query plan depending on where you specified them in the query.


Next will be the query plan itself. Understanding it deeply is outside the scope of this article, but you can read more about reading query plans here. The important part for our purposes are the metrics at the end of each line.


A breakdown of those metrics:

  • The number in brackets (e.g. [#5634172.0M]) is the cardinality of that line, or how many results the query optimizer is expecting the operation to produce. If this number is very large (as it is in the example), this is probably causing problems.
  • Memory shows how much memory an operation used. This only shows up on some operations (e.g., Distinct, Sort, HashJoin). If the number and/or percentage are very large (e.g., memory: {total=5.7G (99.9%)}), this will cause problems.
    • The suffixes correspond to the order of magnitude for bytes. i.e., G = gigabytes, and so on.
  • Results (e.g., results: 296K) show the actual number of results the operation produced. If this number is very different (think orders of magnitude) from the cardinality of the operation, there may be a problem.
    • If you see the Scan operation producing large cardinality misestimations, your statistics may be out of date. You can run db optimize to recompute them.
  • Wall time (e.g., wall time: 454 ms (0.1%)) shows how long, in real time, each operation took. Here, you're looking for operations that took much longer than most of the others. The percentage following the number of milliseconds will help you find this quickly.


Conclusion


This is only a basic overview of reading a query profile, but it should give you enough to find the bottlenecks in your queries and point them out to Support.


If you see a particular join is taking a lot of time/memory, you can try turning it off with a join control query hint. Two notes on trying this:

  1. This will not always work.
  2. Query hints are tuned to specific queries, so if you change your query at all, you should remove any query hints and only add them back if the revised query is exhibiting the same issues as the original.



Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article