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

Know your hardware configuration!
Too many times, we have seen performance issues caused by a lack of
knowledge about hardware configuration. For example, RAID 5 disks
are very common, but they can be a performance nightmare for ETL and
cube processing. The data we collect from performance counters only
makes sense if we can compare it to the known physical limits of our
hardware. Therefore, we need to know how to interpret terms as LUN,
RAID and spindles; we also need to know exactly what hardware we're
using in our own environment.
For disk-related terminology, the following glossary is a good place to
start: http://tinyurl.com/DiskGlossaryTerms . A description of
all possible RAID levels is available at http://en.wikipedia.org/
wiki/Standard_RAID_levels and http://en.wikipedia.org/
wiki/Nested_RAID_levels .

If we want to see data on I/O operations broken down by process, we can use some
of the counters available in the Process category, as shown:

• IO Data Bytes/sec : This is the amount of data transferred in I/O

operations. For Analysis Services, this includes reads and writes to files and
communication between client tools and Analysis Services. Monitoring it can
help us understand if a lack of CPU activity while queries are running on
Analysis Services is the result of I/O operations or not.

• IO Write Bytes/sec : This is the amount of data transferred in write

I/O operations.

• IO Read Bytes/sec This is the amount of data transferred in read

I/O operations

Finally, we have the Processor category of counters, which provides data on the
usage of each processor as well as aggregated values for all processors on the server.
When monitoring Analysis Services, the only significant counter is % Processor
Time , which allows us to compare the CPU usage of Analysis Services to the total
CPU usage of all processes on the server. If, for example, Analysis Services is
consuming 50 percent of CPU and overall CPU usage is at 100 percent, then Analysis
Services is sharing the CPU with other processes. In this kind of situation, to free
up resources for Analysis Services, we either need to move other processes to a
different server, or we can add more processing power to the server. To monitor the
CPU usage of Analysis Services, we can use the % Processor Time counter from the
Process category, and select the msmdsrv process.