We have already introduced many tools that can be
used to get data on processing operations, using trace, performance
counters and dynamic management views. The same tools can also be used
to monitor query performance, and in this section we'll see how this can
be done.
Monitoring queries with trace data
Regardless of the tool we use
to collect it (SQL Server Profiler, ASTrace, XMLA, or Flight Recorder),
trace data is the most important source of information on query-related
operations. Trace data provides information on the internal operations
of the Storage Engine and the Formula Engine, for example showing if
aggregations are used or not or if calculations are evaluated in bulk
mode or not.
The most important trace
events for analyzing query performance are as follows (once again, the
integer identifier for each event is shown in parentheses after its
name):
Progress Report Begin (5) / Progress Report End (6) there is only one subclass event that is relevant for query operations:
Query Begin (9) / Query End (10)- are raised at the start and end of query evaluation. They usually contain only one interesting subclass event:
Calculate Non Empty Begin (72) / Calculate Non Empty End (74)- have no related subclass events. The Calculate Non Empty events are raised when Analysis Services performs non empty filtering operations, for example when the NonEmpty MDX function or the Non Empty statement is used in a query, and these operations are often the cause of slow query performance. When the value of the Duration column for Calculate Non Empty End is large, we should check the column to see if it bulk mode is being used or not. When the value of IntegerData
is 11, Analysis Services iterates over all the tuples in a set to
perform non empty filtering, and this can be very slow. For all other IntegerData values (usually 1), Analysis Services is operating in bulk mode which is usually much faster. IntegerData
MDX Script Begin (78) Execute / Execute MDX Script End (80)-
have no related subclass events. They are raised at the beginning and
end of the evaluation of the MDX Script on a cube, which is usually a
very quick operation. However, if there are any complex named sets
defined on the cube then MDX Script evaluation can take a long time.
Get Data From Aggregation (60)- has no related subclass events. This event is raised when the Storage Engine reads data from an aggregation.
Get Data From Cache (61)-
is raised when data is read from cache. There are several subclass
events that identify the type of cache used. This event will be seen
very frequently on a production server, because after a query has been
run all of the data it returns should be kept in cache. If this is not
the case, it usually indicates that either there is not enough memory
available for caching, or that calculations have been written so that
the values they return cannot be cached.
Get data from measure group cache (1)
Get data from flat cache (2)
Get data from calculation cache (3)
Get data from persisted cache (4)
Query Cube Begin (70) / Query Cube End (71)- have no related subclass events. After a Query Begin
event has been raised, in some scenarios the MDX Script of a cube must
be evaluated before query evaluation proper can start; in these
scenarios Query Cube Begin is raised after the Execute MDX Script End event and shows when query evaluation actually starts.
Query Dimension (81)-
this event is raised when queries retrieve members from dimension
hierarchies. These members may or may not be in cache already; if they
aren't we would expect to see some I/O read operations executed.
Cache data (1)
Non-cache data (2)
Query Subcube (11)- a single MDX query might result in several different subcubes of data being requested from the Storage Engine. A Query Subcube event is raised for each of these requests, and may be one of the following types:
Cache data (1)
Non-cache data (2)
Internal data (3)
SQL data (4)
Query Subcube Verbose (12)- is functionally identical to Query Subcube, but it adds more information about the multidimensional space of the subcube that is queried.
Cache data (1)
Non-cache data (2)
Internal data (3)
SQL data (4)
Serialize Results Begin (75) / Serialize Results End (77)- have no related subclass events. They mark the start and end of query results being sent back to the client. The Serialize Results Begin
event might be raised before the calculation of all of the cell values
in a cellset has been completed, although it often starts just after all
calculation is finished (for example, a Non Empty statement in an MDX query could force this behavior).
Monitoring queries with Performance Monitor counters
Analysis Services
Performance Monitor counters are more useful for monitoring general
query behavior than understanding what is happening when individual
queries are run. Logging these counters can help us to understand the
characteristics of all of the queries our users are running, and
therefore help us to find query performance bottlenecks. Here's a list
of all of the relevant categories and the important counters in them:
MSOLAP: Cache
CurrentEntries is the number of entries in the cache
Total Direct Hits
is the number of subcube queries answered from existing cache entries
this number should be compared to Total Misses to evaluate the
percentage of subcube queries that are resolved using cache
Total Misses
is the number of cache misses this number should grow after the cache
has been cleared (for example, after the Analysis Services service has
been restarted, or after cube processing) and should not grow too much
once the cache is warm. If this number continues to grow, we need to
evaluate if there is not enough memory for cache or if the queries that
are being run cannot take advantage of existing cache.
MSOLAP:MDX contains several counters with very detailed information, the most important of which is:
Total NON EMPTY un-optimized
is the number of Calculate Non Empty operations (as described earlier
in the section on trace data) that are using an un-optimized algorithm.
If this number continues to grow, there may be MDX queries that are
running slowly for this reason and we might want to find them and
optimize them.
MSOLAP: Memory we can use the same counters used to monitor processing operations to monitor query operations.
MSOLAP: Storage Engine Query
contains several counters with detailed information that we can use to
measure the workload of Analysis Services; the most generic counter here
is:
Monitoring queries with Dynamic Management Views
DMVs are not really useful
for finding slow queries or understanding what happens when a query is
run; trace data is much more useful for this purpose. That said, the
data that DMVs return on memory usage, for example, is very useful for
understanding the state of Analysis Services at a given point in time,
which will have an impact on query performance.
MDX Studio
MDX Studio is a powerful tool that allows us to analyze MDX
expressions, monitor the performance of MDX queries and use trace data
to determine whether the Storage Engine or the Formula Engine is the
cause of poor query performance. A complete description of MDX Studio
and links to download it (for free) are available at:http://tinyurl.com/MdxStudio.
