SQL Server Analysis Services 2005/2008 Administration
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Although Microsoft Analysis Services (MSAS) is part of the SQL Server product suite, it is a data container in its own right and as such requires management by qualified database administrators. With early MSAS versions, the DBA arsenal was rather limited. Cube development and administration was done through a single tool called Analysis Manager. Administrators could collect performance counters and tweak registry settings, but managing, troubleshooting, tuning, and monitoring MSAS was difficult at best.
With MSAS 2005 and 2008, the story has changed. MSAS still doesn’t provide nearly as many administrative utilities as the SQL Server relational database engine, but DBAs are finally allowed to look under the hood and are given some tools to detect, troubleshoot, and resolve issues. This article discusses various tasks and corresponding tools for Analysis Services DBAs.
Typical SQL Server DBA duties include:
- Managing server-wide configuration settings
- Ensuring that databases are available and healthy
- Configuring security at the server level
- Collecting backups and restoring databases as needed
- Monitoring and tuning performance
- Resolving blocking issues
- Scaling up or scaling out database solutions
- Implementing clustering, log shipping, database mirroring, or replication for high availability
Let’s consider how MSAS DBA would go about performing similar functions. Please note that each of these areas briefly discussed here deserves its own article if not a book. In this article, I’m only providing a short overview of exciting improvements introduced with the latest releases of the product.
Configuring Server Properties
Newer versions of MSAS store server configuration properties in the msmdsrv.ini file which you can find in the config folder of the installation directory. Many of these properties are exposed through SQL Server Management Studio (SSMS), but many more can only be tweaked by changing values directly in the file. Apparently many server properties are only used during product development and testing because they’re not documented or supported.
By and large, the default configuration values work well in most environments, but here are just a few that you will likely need to adjust:
- You need to configure query logging if you care about using the Usage-Based Optimization (UBO) Wizard or Aggregation Manager sample tool for fine-tuning aggregations based on queries executed against your measure groups. For more information about UBO and Aggregation Manager, please refer to my previous article.
- You can store query logs in a file with the .trc extension or in a SQL Server table. The latter option is preferred because it supports using UBO or Aggregation Manager.
- On powerful 64-bit servers with plenty of memory, it often makes sense to increase the number of threads available for processing and queries. You could also adjust low memory limit and total memory limit settings if default values aren’t sufficient for your environment.
- You may want to experiment with the PreAllocate property. By default, Analysis Services loads metadata into memory at startup; additional memory is acquired and released as needed. If you have plenty of memory on your server, you may wish to reserve some memory for MSAS queries and processing as soon as the service starts. Doing so may help improve performance if MSAS is indeed spending significant amount of time for allocating memory. Setting the PreAllocate property to a non-zero value reserves a percentage of all available memory when you start the MSAS service.
- Flight Recorder collects MSAS trace events out of the box. By default, Flight Recorder only collects a few trace events, and is overwritten as soon as the file size reaches 10MB. You could (and should) modify the “trace definition file” and “file size MB” properties to record additional trace events and keep them longer. The output of Flight Recorder is often the most helpful artifact for troubleshooting issues.