We are going very close to the release of SQL Server 2012 and after using Analysis Services 2012 for almost one year in a relatively small number of scenarios, I’m really curios to see the questions and the issues that will emerge when its adoption will go mainstream. I expect that many will consider a Tabular instance of Analysis Services similar to a Multidimensional one, also from a system engineer perspective, whereas there are many important differences. For example, the hardware required to scale up an Analysis Services instance is different (very different) between Tabular and Multidimensional, but this will be the topic for a future post. By now, I just want to make some consideration about memory settings.

Because Tabular and Multidimensional share several system settings and configuration, you have to be aware of different behaviors of Analysis Services depending on the type of instance you use. I published an article about memory settings in Tabular that should help you understanding the role and the behavior of these Memory settings:

  • HardMemoryLimit
  • LowMemoryLimit
  • TotalMemoryLimit
  • VertiPaqMemoryLimit
  • VertiPaqPagingPolicy

As usual, you might want to change memory settings in case your server is used to host several services, and/or when you are tuning a development machine that can have other different services running.

Because VertiPaq stores all data in memory, the areas in which you may optimize performance by tuning memory parameters are only data processing and cache during query execution. But as usual you should have a holistic approach with memory settings and also consider what other services are running on the same server.

Well, if you think it’s too early to worry about memory settings and you want to start working with Tabular, you can attend the Microsoft TechEd pre-conference seminar that I and Alberto Ferrari will deliver in June – and you have two choices, North America and Europe!

The pre-conference seminar is Using BISM Tabular in Microsoft SQL Server Analysis Services 2012 and is targeted to a technical audience that want to learn how to build a Tabular model from scratch, covering all the most important features available in Tabular, including Security and Partitioning. We will not have time to also talk about performance tuning, but this will be a good topic for other discussions during TechEd!