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

The CPU column shows what percentage each process uses of the total
processing power available on the system. On a dual core machine, a single
thread can consume up to 50 percent; but the same thread can only consume
up to 25 percent on a quad-core system. Analysis Services scales well on multiple
cores in most scenarios, so we should expect to see very high CPU values for
Analysis Services during cube processing and query resolution.
Working Set (Memory) and Commit Size are the most important counters that
we can analyze and correspond to the physical RAM and virtual memory used by
the process. As we said earlier, Memory (Private Working Set) is a value that, for
Analysis Services, should be slightly lower than Working Set (Memory) . Therefore,
for our purposes, they can be used almost in the same way. What is interesting is the
comparison between virtual memory usage and physical RAM usage. When virtual
memory usage is higher than physical RAM usage, probably part of the Analysis
Services cache has been paged to disk. This condition is not an issue if Analysis
Services does not have a high workload, but if it happens when users are running
queries and does not improve over time (the physical RAM usage should grow to
almost matching the virtual memory usage), then we should investigate the cause of
the issue. The server might have insufficient physical RAM or there could be other
processes that are consuming physical RAM at the same time. In both cases, adding
RAM would be the best way to solve the problem. We can also mitigate the issue by
moving other processes to different servers if we can afford to do so, or by reducing
the amount of memory available to Analysis Services (which we'll see later in this
chapter). Page Faults Delta is another counter that we can monitor to check which
process is responsible for any paging activity.
Performance counters
The Windows operating system gathers data about system performance and exposes
it through performance counters, which can be displayed through a Microsoft
Management Console ( MMC ) snap-in called Performance Monitor . In reality,
performance counters are available through a set of APIs and there are third-party
tools that can access them too. However, in this topic our goal is to highlight the most
important performance counters to monitor on a server running Analysis Services.
We will use Performance Monitor, but the concepts we are going to explain are valid
regardless of the tool used to display them. Moreover, in this section we will cover
only operating system counters, but there are also other counters specific to Analysis
Services instances that we will look at later on in this chapter.