buchachon - Fotolia
Microsoft SQL Server Analysis Services (SSAS) provides a powerful engine for creating and managing data mining applications and online analytical processing (OLAP) systems. To achieve optimal OLAP performance, you should carefully monitor and optimize OLAP cubes and the underlying relational data source. Let's look at the tools you could use to monitor SSAS and optimize OLAP performance.
SQL Server Profiler
You can use SQL Server Profiler to capture SSAS instance activities based on selected events. SQL Server Profiler captures activity as a trace and includes a set of predefined templates that meet most common trace capture scenarios. You can save the trace to file or in an SSAS database, which allows you to monitor the data in real time. You can also replay the trace in real time or step by step in the same or on another SSAS instance. By replaying the trace, you can easily identify slow-running Multidimensional Expressions, or MDXes, benchmark their performance in different environments, and test and debug them. You can also use SQL Server Profiler to audit security information. For example, you can set the trace file to audit failed connection attempts or permission failures when a user tries to access an object. For more details about how to create and run traces, see Create Profiler Traces for Replay (Analysis Services) and Analysis Services Trace Events.
One of the most commonly used tools for monitoring the performance of local and remote instances of SSAS, and the operating system and computer on which it runs, is System Monitor, a component of the Windows Performance utility. System Monitor, which provides near-real-time performance information, is used for live monitoring only, and is ranked as one of the best tools for measuring performance and identifying hardware bottlenecks. However, you cannot use System Monitor to identify the cause of a performance problem. For example, System Monitor may display high CPU usage, but it does not identify the cause. You can monitor several computers from one location using System Monitor, which reduces the resource requirements for each system being monitored and gives you a way of directly comparing performance statistics on different computers.
Extended Events and Analysis Services Dynamic Management Views
Extended Events (XEvents) is an event infrastructure that is a highly scalable and lightweight performance monitoring system that uses very few system resources. With XEvents, you can capture and target all SSAS events to specific consumers. XEvents infrastructure is integrated directly into SQL Server and can be managed easily with Transact-SQL. For more information, see SQL Server Extended Events.
Analysis Services Dynamic Management Views (DMVs) are query structures that provide plenty of information about Analysis Services instance state and server health. You can use this information to diagnose and tune Analysis Services instances or database performance. All DMVs reside inside $System. For more information about monitoring Analysis Services using DMVs, see the MSDN resource here.
Now that you know about the tools available to monitor SSAS and OLAP performance, here are points to consider as you tackle OLAP optimization.
Indexes improve query performance in the underlying relational database, which affects the processing speed of a multidimensional OLAP (MOLAP) partition and the query speed of a relational OLAP (ROLAP) partition. Most Analysis Services databases are read-only and therefore usually benefit from indexing. A general rule of thumb is to create indexes that cover all queries that Analysis Services executes. Moreover, to achieve optimal performance, you should create all indexes with a 100% fill-factor.
There is a cost associated with indexing an underlying relational database. For example, data update queries (INSERT, UPDATE or DELETE) must also update table indexes. Therefore, increased indexing could significantly decrease the performance of DML operations, but this does not affect a read-only system. In addition, indexing takes time and disk space. It is good practice to use SQL Server Profiler and the Database Engine Tuning Advisor or index-related DMVs and Dynamic Management Functions (DMFs) to regularly analyze queries and index usage. That helps you identify which indexes to create and which to delete.
Choose suitable aggregations
Query performance relies heavily on having suitable aggregations. However, it is not necessary to add aggregation to each level of dimension. When using the Aggregation Design Wizard or the Usage-Based Optimization Wizard, you should try different performance gain values, starting from a low value. Initially, you will notice major improvements to query speed when you increase the performance gain value. Although the performance gain value increases, there are usually diminishing returns, and increases in disk space usually escalate for each increase in performance. You can also use the query log to store user queries for future analysis. You can use the query log data with Usage-Based Optimization Wizard to optimize aggregations. You can also use Aggregation Design Wizard to create appropriate aggregations. Aggregation Design Wizard is useful for a new system with little or no query log data; the Usage-Based Optimization Wizard yields better results when a system matures.
Use proactive caching
To achieve faster results when querying OLAP cubes, you must use MOLAP storage. However, if you do use MOLAP, there is some latency in the data because it is processed only periodically. To receive up-to-date results, you must use ROLAP storage, but ROLAP's storage performance is significantly slower than MOLAP's in terms of query response. That's where a proactive caching configuration option is very useful because it allows Analysis Services to select from both MOLAP and ROLAP data, thus providing the performance of MOLAP with the up-to-date data of ROLAP. Find more information about proactive caching in this MSDN resource.
Learn how to pick out the right OLAP storage model for your use case.
Check out some of SQL Server's built-in tools to troubleshoot memory usage.