DAX Studio – What do all those numbers mean in the server timing output?

When you run a query in DAX Studio with the Server Timings feature switched on you will see output like the following.

image

A number of the metrics that are gathered by the server timings have been abbreviated and if you may not be aware of what they all mean. The topic of performance tuning DAX queries can get quite involved. This post is just designed to give a brief overview and bit of background about how the different metrics are calculated.

Metric Description
Total This is the total query duration in milliseconds – taken from the Query End profiler event. This is the total time the server took to process the query. (so it will exclude any time the client took to process the result set)
SE CPU This is the amount of CPU time that was spend on Storage Engine queries (note that this figure *may* not be 100% reliable, so don’t place a high amount of importance on it) the blue ratio under SE CPU is the factor of SE CPU over SE and is a very rough indicator of the average parallel operations that the SE was running. This figure is calculated by adding up the CPU duration from the Storage Engine events
FE This is the amount of time spent in the Formula Engine, calculated by Subtracting the SE duration from the Total. The blue figure underneath is the percentage of FE / Total
SE This is the amount of time spend in the Storage Engine, calculated by adding up the duration of all the Storage Engine queries. The blue figure underneath is the percentage of SE / Total. As a rough rule of thumb you want to try to get your queries to spend more time in the Storage Engine as it is multi-threaded so can do more operations in parallel. While the Formula Engine is single threaded and cannot make use of multiple CPU cores. Note that you cannot have a query that is 100% handled in the storage engine as the FE sits over the top of the SE. The FE is what issues the requests to the SE and it also serializes the result set before it is sent back to the client
SE Queries this is the number of Storage Engine queries that were performed during the processing of the query
SE Cache this is the number of Storage Engine cache hits

You may also wonder what that “SQL like” query is that captured by the scan event. This is called xmSQL and is textual representation of the requests that the Formula Engine sent to the Storage Engine. As far as I am aware there is no way of executing these queries, they are merely a textual representation of the requests sent to the Storage Engine to enable people to understand what operations the storage engine was performing.

Print | posted on Sunday, January 20, 2019 10:17 PM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)