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

As we said, there are two different types of directly generated I/O operations
that we need to worry about when monitoring Analysis Services. First, there are
disk-related operations, which are made when Analysis Services processes cubes
and dimensions (mainly to write data to disk) and when Analysis Services reads
cube data to answer queries. Monitoring reads to the disk is important if we are
to spot situations where more RAM or more aggregations are needed to improve
performance. Another I/O operation generated by Analysis Services is the transfer of
query results to the client. Usually this is not a slow operation, but if a query returns
a very large number of cells in a cellset (for example, a list of one million customers
with several measure values for each one), the query response time might be affected
by the time needed to transfer the cellset from the server to the client. Take a look at
network traffic to understand if this is a possible issue.
Most of the I/O bottlenecks for Analysis Services operations are
related to disk access. It is out of scope of this topic to talk about
hardware configurations. However, it is important to highlight that
for Analysis Services, a set of local disks (especially if in RAID 0 or
10) could be much faster than accessing volumes on a SAN. Getting
the same bandwidth for a SAN as we can get with local disks could
be very expensive, and bandwidth is very important when Analysis
Services needs to do something such as scanning all the partitions in a
measure group. An interesting Microsoft whitepaper about hardware
optimization for a multiterabyte data warehouse and cube is available
at http://tinyurl.com/ProjectRealSsas .
Tools to monitor resource consumption
The first tools we're going to look at for monitoring resource consumption are
Task Manager and Performance Monitor . These tools are available on any server
we might use to host an Analysis Services instance and they can also be used to
monitor any other process that might compete with Analysis Services for the use
of system resources.
Windows Task Manager
The Windows Task Manager is a utility included with the Microsoft Windows
operating system that provides information about running applications and their
system resource usage (for example, CPU, memory, and network activity). Task
Manager also allows us to kill a process and change its priority. However, we are
only interested in the monitoring features of this tool. There were been some changes
and improvements in Task Manager moving from the XP/2003 version to the
Vista/2008 version. From here on, we will only focus on the latest release of the tool.