Basics of reading a query profile

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

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.