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

• MSOLAP:MDX : This contains several counters with very detailed

information, the most important of which is:
° Total NON EMPTY un-optimized : This 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 : This contains several counters with

detailed information that we can use to measure the workload of Analysis
Services; the most generic counter here is:
° Total Queries Answered : This shows the total number of queries
run against Analysis Services; if this number does not increase, no
queries are being run.

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.

Monitoring usage
Apart from monitoring the performance of processing and queries, another
important activity is monitoring the overall usage of Analysis Services. At any
given point in time, we might want to know who is connected to Analysis Services
and who is running queries, for example, because we might want to warn users that
we're about to restart the service for maintenance reasons. Also, in the long term, it
is interesting to know which cubes and hierarchies are used and which are not, what
type of queries are being run, who the most active users are, which users never use
the system, and so on.

To monitor usage, we can use the same tools that we've used in the previous sections.