Monitoring Cube Performance and Usage - Expert Cube Development with SSAS Multidimensional Models - page 345

Shrinkable objects can be purged by Memory Manager if new allocations require
memory. However, it can also be useful to take a look at non-shrinkable objects
because they cannot be purged, and we might want to know what the most
expensive objects are. They will give us an indication of which databases
should be moved to another server to reduce resource consumption:

SELECT *
FROM $System.DISCOVER_OBJECT_MEMORY_USAGE
ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC

Much of the information provided by DMVs is not available from other sources. For
this reason, it is important to know of their existence and to know how to use them
in our monitoring infrastructure.

Monitoring query performance
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, and 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 ( 14 ): This shows when the Storage Engine accesses a partition
or aggregation to get data. This will only happen if the data required
is not available in the Storage Engine cache.