Shulou(Shulou.com)05/31 Report--

The content of this article mainly focuses on how to monitor and optimize the OLAP database. The content of the article is clear and clear. It is very suitable for beginners to learn and is worth reading. Interested friends can follow the editor to read together. I hope you can get something through this article!

Optimizing the performance of online analytical processing is very important, and fortunately, there are tools that can help monitor and improve the operation of OLAP databases.

Microsoft SQLServer Analytical Services (SSAS) provides a powerful engine for creating and managing data mining applications and online analytical processing systems. In order to achieve the best OLAP performance, you should carefully monitor and optimize OLAP databases and potential relational data sources. Tools for monitoring SSAS and optimizing OLAP performance are described below.

SQLServer Profiler

You can use SQL ServerProfiler to capture the activities of SSAS instances based on selected events. SQL ServerProfiler captures activities in a tracking manner and contains a set of predefined templates that meet the most common tracking capture scenarios, saving the tracked information to a file or a SSAS database that allows you to monitor data in real time. You can also replay the trace on the same or another SSAS instance in real time or step by step. By replaying the trace, you can easily find slow multidimensional expressions, or MDXes, compare performance benchmarks in different environments for testing and debugging, and you can also use SQL Server Profiler to audit security, such as You can set up a trace file to audit failed link attempts or a failed license when a user tries to access an unauthorized object, for details on how to create and run a trace, see creating an analysis trace and an analysis service trace event for playback (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 the computers running, is the system Monitor, a practical component of Windows performance that provides near-real-time performance information that is only used for real-time monitoring, and is seen as one of the best tools to measure performance and identify hardware bottlenecks, but you cannot use system Monitor to identify the cause of performance problems For example, the system monitor may show high CPU usage, but will not identify the reason You can use system monitors to monitor multiple computers from one location, reducing resource consumption for each system that needs to be monitored, and giving you a way to directly compare the performance statistics of different computers.

Extend the dynamic management view of event and analysis services

Extended event (XEvents) is an event infrastructure of a highly scalable and lightweight performance monitoring system that takes up very little system resources. Using XEvents, you can capture all SSAS events to specified users. XEvents infrastructure has been directly integrated into SQLServer and can be easily managed using T-SQL. For more information, see SQLServer extension events.

The Analysis Service dynamic Management View (DMVs) is used to provide a large number of query structures about the analysis service instance status and server health information, which can be used to diagnose and tune the analysis service instance or database performance. All DMVs are built into $System. For more information about using DMVs to monitor the analysis service, see the MSDN resources.

These are the tools used to monitor the performance of SSAS and OLAP, and here are the areas that should be considered for optimizing OLAP.

Use index

Indexes can improve the query performance of basic databases that affect the partition processing speed of multidimensional online analytical processing (MOLAP) and relational online analytical processing (ROLAP). Most analysis service databases are read-only, so they can benefit from the index. the general rule of thumb is to create queries that cover the execution of all analysis services, and in order to achieve the best performance All indexes you should create should use a 100% fill factor.

Cost consumption in the underlying database is related to the indexes used. For example, queries that update data (INSERT,UPDATE or DELETE) must also modify indexes, so a large number of indexes will degrade the performance of these DML operations (INSERT,UPDATE or DELETE), but this will not affect a read-only system. In addition, indexes will take up time and disk space. The best practice is to use SQL Server Profiler and database engine optimization wizard or index-related DMVs and dynamic management function DMFS to analyze the use of queries and indexes on a regular basis, which can help you find indexes that need to be created and deleted.

Select the appropriate aggregation

Query performance depends to a large extent on appropriate aggregations, but it is not necessary to add aggregations at each dimension level. When using the aggregation Design Wizard (Aggregation Design Wizard) or the Usage-Based Optimization Wizard, you should try different performance gain values from lower values. Initially, when you increase the performance gain value, you will notice the main query speed increase, although the performance gain value has improved. You can also use search logs to store user queries for future analysis, use the Usage-Based optimization wizard to optimize aggregation of query log data, and you can also use the aggregation design wizard to create appropriate aggregations The aggregation design wizard is useful for a new system with little or no query log data, while the Usage-Based optimization wizard produces better results when a system matures.

Use active caching

In order to achieve faster results when querying OLAP databases, you have to use MOLAP storage, but if you use MOLAP, because it processes data periodically, there will be some latency in the data. In order to get the latest data, you must also use ROLAP storage, but the storage performance of ROLAP is significantly slower than MOLAP in terms of query response, which is where the active cache configuration option is very useful. Because it allows the analysis service to retrieve from both MOLAP and ROLAP, it provides performance for MOLAP that uses the latest data from ROLAP, and can find more information about active caching in MSDN resources.

Thank you for your reading, I believe you have some understanding of "how to monitor and optimize OLAP database", go to practice quickly, if you want to know more related knowledge points, you can follow the website! The editor will continue to bring you better articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.