Performance Tuning and Troubleshooting Tabular Models - SQL Server Analysis Services 2012 Cube Development - page 401

Optimizing and managing a model's
design
As the designer of a Tabular Model, your experience is affected by different factors
than those of the consumers of the solution you deploy.
Managing memory usage
A common issue that users encounter with the Power Pivot add-in for Excel occurs
when a large volume of data is imported that uses all the available memory resources
on the local computer. This is especially true when running a 32-bit operating system
or Office edition with restricted memory resources. The logical path for many of these
solutions is to migrate the desktop Power Pivot model to a Tabular Model that runs on
a well-equipped server.
The xVelocity In-Memory Analytics Engine is designed to utilize memory, rather
than disk I/O, to provide improved performance when compared to more conventional
technologies. Since models reside entirely in memory, the way we think about provid-
ing and managing resources must be fundamentally different than it would be for man-
aging relational databases or even multidimensional semantic models. The simple
reality is that when a Tabular database server doesn't have enough memory to hold all
of the data in the model, it simply will not work. In many ways, the xVelocity In-Memory
Analytics Engine is less complicated and can be easier to troubleshoot. Many prob-
lems can be avoided by simply providing extra memory to hold data, process objects,
and execute queries.
Many database administrators and virtual server administrators aren't accustomed to
thinking about server management in this way. In fact, the objective when configur-
ing a virtual server is to restrict memory and CPU resources as much as possible so
they can be allocated to other virtual machines on the same physical hardware. Make
sure that when you specify the memory requirements for a Tabular Analysis Services
server, you should not only consider the size of each model that will be loaded into
memory but also the additional headroom needed for growth, object processing, and
other contingencies.