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

• Security : Security has implications on Analysis Services' ability to share

cache between users, which means more work is likely to be taking place for
each query. Using the Enable Visual Totals option with Dimension Security
also has an impact on aggregation usage. In both cases, we should check if
we can make structural changes to the cube or to how we have implemented
security that can improve aggregation and cache usage.

A final consideration is that an out-of-memory condition should be prevented by
dealing with memory issues in advance. Memory issues cause paging and paging
causes bad performance. Therefore, monitoring operating system performance
counters is the first step to ensuring that Analysis Services performs as well as possible.

Sharing SQL Server and Analysis Services on
the same machine
Many Analysis Services installations share a server with SQL Server. This happens
mainly because of licensing. The license required for Analysis Services is in fact a
SQL Server license, so having both installed on the same machine requires only one
server license to be purchased. However, this configuration has a hidden cost; since
memory and sometimes CPU must be shared between these services, many of the
scenarios we describe in this chapter are much more likely to occur.

It is not always true that it is a good idea to install Analysis Services on a different
server to SQL Server though. If we don't have a multiterabyte data warehouse and
the hardware necessary to support it, chances are that the bandwidth available
between two servers could be a bottleneck compared with the faster transfer rate that
we could have if Analysis Services and SQL Server were installed on the same server.
This is particularly important during cube processing. There is also the question of
cost. Often Analysis Services and SQL Server use neither CPU nor disks at the same
time, and only require a modest amount of memory to cache data. Nowadays, 64-bit
servers allow us to double the memory on a server at a fraction of the cost necessary
to provide a new server with the same amount of RAM. In our experience, adding
RAM to a server has a much better ROI than investing in a new server. In the end,
having both services on the same server results in faster processing and cheaper
hardware costs; although it can of course, increase the chance of memory issues.