Monitor and optimize SQL Server OLAP (online analytical processing)

The OLAP system is the main application of the data warehouse system. It is specially designed to support complex analysis operations and focuses on decision-making support for decision makers and senior management personnel, you can quickly and flexibly process complex queries with large amounts of data according to the requirements of analysts, and provide the query results to decision makers in an intuitive and easy-to-understand manner, so that they can accurately understand the business status of the Enterprise (company), understand the needs of the object, and formulate the correct solution.

It is very important to optimize the performance of online analysis and processing. Fortunately, some tools can help monitor and improve the operation of OLAP databases.

The Microsoft SQL Server Analysis Service (SSAS) provides a powerful engine used to create and manage data mining applications and online analysis and processing systems. To achieve the best OLAP performance, you should carefully monitor and optimize OLAP databases and potential relational data sources. This article describes SSAS monitoring and OLAP performance optimization tools.

SQLServer Profiler

You can use SQL ServerProfiler to capture the activity of an SSAS instance based on the selected event, SQL Server Profiler captures activities in a tracking manner and contains a set of predefined templates that meet the most common tracking and capturing scenarios, you can save the tracked information to a file or an SSAS database that allows you to monitor data in real time, you can also replay a trail on the same or another SSAS instance in real time or step by step. By replaying the trail, you can easily find a slow multi-dimensional expression or MDXes, compare performance benchmarks in different environments for testing and debugging. You can also use SQL Server Profiler to review security, for example, you can set a link attempt to review failed attempts or a trace file that allows failed attempts when a user attempts to access an unauthorized object. For details about how to create and run a trail, see create analysis tracking and analysis service tracking events for replay (analysis service.

System Monitor

One of the most commonly used tools for monitoring the performance of local and remote SSAS instances and operating systems as well as running computers is the system monitor, which is a practical component for Windows performance, provides near real-time performance information that is only used for real-time monitoring, and is considered one of the best tools to measure performance and identify hardware bottlenecks. However, you cannot use the system monitor to identify the cause of performance problems. For example, the system monitor may display high CPU usage, but it does not identify the cause; you can use the system monitor to monitor multiple computers from one location, which can reduce resource usage for each system to be monitored, we also provided you with a method to compare performance statistics of different computers.

Extended event and analysis service dynamic management view

An extended event (XEvents) is an event infrastructure that monitors the system for high scalability and lightweight performance with a very small amount of system resources. XEvents, XEvents infrastructure has been directly integrated into SQLServer and can be easily managed using T-SQL, which can capture all SSAS events to the specified user, for more information, see SQL Server expansion events.

The dynamic management view of analysis services (DMVs) is used to provide a large number of query structures for analyzing service instance statuses and server health information, this information can be used to diagnose and optimize analysis service instances or database performance. All DMVs are built into $ System. For more information about using DMVs to monitor and analyze services, please refer to the MSDN resources.

The above are tools used to monitor the performance of SSAS and OLAP. The following are some considerations for optimizing OLAP.

Use Index

Indexes can improve the query performance of basic databases that affect the processing speed of multi-dimensional online analytical processing (MOLAP) partitions and the query speed of Relational Online analytical processing (ROLAP) partitions, most analytics service databases are read-only, so they can benefit from the index. The general rule of thumb is to create queries that cover all Analysis Services. In addition, to achieve the best performance, all indexes you should create should use a fill factor of 100%.

The cost in the basic database is related to the indexes used. For example, the index will be modified for queries (INSERT, UPDATE, or DELETE) that UPDATE data. Therefore, A large number of indexes will reduce the performance of these DML operations (INSERT, UPDATE or DELETE), but this will not affect a read-only system. In addition, indexes will occupy time and disk space, the best practice is to use the SQL Server Profiler and database engine optimization wizard or index-related DMVs and dynamic management functions DMFS to regularly analyze queries and index usage, this will help you find the indexes to be created and the indexes to be deleted.

Select the appropriate aggregation

Query performance depends largely on the appropriate Aggregation, but there is no need to add Aggregation at the level of each dimension. When you use the Aggregation Design Wizard (Aggregation Design Wizard) or in the Usage-Based optimization wizard, you should try different performance increase values from a lower value. At first, when you increase the performance increase value, you will notice the major increase in query speed, although the performance increase value is increased, the benefits are often reduced. Each one-time improvement can gradually increase the disk space, you can also use search logs to store user queries for future analysis. You can use the Usage-Based optimization wizard to optimize the aggregation of query log data. You can also use the aggregation design wizard to create an appropriate aggregation, the aggregation design wizard is very useful for a new system with few or no log data to be queried. When a system is mature, the Usage-Based optimization Wizard will produce better results.

Use active cache

You must use MOLAP storage to Query OLAP databases quickly. However, if you use MOLAP, because it processes data cyclically, the data may be delayed, to obtain the latest data, you must also use ROLAP storage, but the storage performance of ROLAP is much slower than that of MOLAP in query response, this is where the active cache configuration option is useful, because it allows the analysis service to retrieve data from both the MOLAP and ROLAP, thus providing performance for the MOLAP that uses the latest ROLAP data, you can find more information about active cache in MSDN resources.