How to monitor and optimize OLAP database _mssql

Optimizing the performance of online analysis processing is very important, and fortunately, some tools can help monitor and improve the operation of OLAP databases.

Microsoft SQL Server Analytics Services (SSAS) provides a powerful engine for creating and managing data mining applications and online analytical processing systems, and you should carefully monitor and optimize OLAP databases and potential relational data sources for optimal OLAP performance. This article describes the tools for monitoring SSAS and optimizing OLAP performance.

SQL Server Profiler

You can use SQL Serverprofiler to capture the activities of the SSAS instance based on selected events, and SQL Server Profiler captures the activity in a tracked manner and contains a predefined set of templates that meet the most common trace capture scenarios. You can save tracked information to a file or to an SSAS database that allows you to monitor data in real time. It is also possible to replay traces on the same or another instance of SSAS in real time or in one step, and can easily find multidimensional expressions that run slowly by replaying the trace. Or mdxes, which compares performance benchmarks in different environments for testing and debugging, you can also use SQL Server Profiler to audit security, for example, you can set up a link attempt to audit failure, or a trace file that fails when a user attempts to access an unauthorized object. For more information about how to create and run traces, see Creating profiling Trace and profiling service tracking events for playback (profiling services).

System Monitor

One of the most commonly used tools for monitoring local and remote SSAS instances and operating systems and the performance of the computers running is System Monitor, a practical component of Windows performance that provides near-real-time performance information that is used only for real-time monitoring. and is seen as one of the best tools to measure performance and identify hardware bottlenecks, however, you cannot use System Monitor to identify the cause of performance problems, for example, System Monitor may show high CPU usage, but will not identify the cause; You can use System Monitor from one location to monitor multiple computers, For each system that needs to be monitored, you can reduce the resource footprint and give you a direct comparison of the performance statistics of different computers.

Extended event and profiling Services dynamic management view

An extended event (xevents) is an event infrastructure that consumes a very small amount of system resources and is a highly scalable and lightweight performance monitoring system, using xevents to capture all SSAS events to the specified user. The xevents infrastructure has been directly integrated into SQL Server and can be managed using T-SQL for more information, see SQL Server Extension events.

The Analysis Services dynamic management view (DMVs) is used to provide a large number of query structures for analyzing service instance state and server health information that can be used to diagnose and tune an analysis service instance or database performance, and all DMVs are placed inside $system. For more information about using the DMVS Monitoring Analysis Service, check out the MSDN resources.

These are the tools used to monitor SSAS and OLAP performance, and the following are some of the areas that you should consider to optimize OLAP.

Using indexes

Indexing improves the query performance of the underlying database, which affects the speed of multidimensional online analysis processing (MOLAP) partitioning and relational online analysis processing (ROLAP) partitioning queries, and most Analysis Services databases are read-only so they can benefit from the index. The general rule of thumb is to create a query that overrides the execution of all Analysis Services and, in addition, to achieve optimal performance, all indexes you should create should use a 100% fill factor.

Cost consumption in the underlying database is related to the index used, for example, queries that update data (INSERT, update, or delete) will also modify the index, so a large number of indexes can degrade the performance of these DML operations (insert,update or deletes), but , this does not affect a read-only system, and the index consumes time and disk space, preferably by using the SQL Server Profiler and the Database Engine Tuning Wizard or the DMVs and dynamic management functions associated with the index DMFS periodic analysis queries and index usage. This will help you find the indexes you need to create and the indexes you delete.

Select the appropriate aggregation

Query performance relies heavily on the appropriate aggregations, but there is no need to add aggregations at the level of each dimension, when using the Aggregation Design Wizard (Aggregation-Wizard) or the Usage-based Optimization Wizard, You should start experimenting with different performance gain values from a lower value, initially, when you increase the performance gain value, you will notice that the main query speed increases, although the performance gain value increased, and often there will be diminishing returns, each performance of the increase in disk space growth gradually upgrade, You can also use the search log to store user queries for future analysis, use the Usage-based Optimization Wizard to optimize aggregation of query log data, and also use the Aggregation Design Wizard to create the appropriate aggregations. The Aggregation Design Wizard is useful for a new system with fewer or no query log data, and the Usage-based Optimization Wizard produces better results when a system matures.

Using Active Caching

In order to achieve a faster effect when querying an OLAP database, you must use MOLAP storage, but if you use MOLAP because it periodically processes data, there will be some delays in the data, and you must use ROLAP storage to get the latest data. But ROLAP's storage performance is significantly slower than MOLAP's in query response, which is where the proactive caching configuration option is useful because it allows the profiling services to be retrieved from both MOLAP and ROLAP, thus providing the performance for MOLAP using the latest data from ROLAP. More information about proactive caching can be found in the MSDN resources.

How to monitor and optimize an OLAP database do you know? Hope can be applied to everyone's work.