Monitoring Ssas Performance: Key Strategies For Optimum Results

SSAS performance monitoring is essential to ensure that SQL Server Analysis Services (SSAS) are operating efficiently and to prevent performance issues from becoming full-fledged bottlenecks. There are various tools available for monitoring SSAS performance, such as SQL Sentry, Windows Task Manager, Windows Performance Monitor (Perfmon), SQL Server Profiler, and Extended Events. These tools allow administrators to track server and process performance, memory usage, CPU usage, disk activity, and other key metrics that impact SSAS performance. By collecting and analyzing these metrics, administrators can identify and address potential bottlenecks and optimize the performance of their SSAS environment.

Monitor memory usage

Monitoring memory usage is crucial for optimising the performance of your business-critical information. Memory constraints are a common issue with SSAS, as the SSAS Tabular database is entirely in-memory in Tabular mode, and bottlenecks in this mode are challenging to resolve.

IDERA's SQL Business Intelligence Manager provides comprehensive information on the SQL Server Analysis Services service and instances. It allows you to monitor the health and status of your Analysis Services and the instances hosting these services. The Memory category includes charts for Overall Usage, Available Memory, and Page Transactions.

The SSAS Memory section gives details about how the server instance uses the memory allocated to it. When monitoring a Tabular instance, you can view the memory counters and the VertiPaq memory limit. This data helps you understand where physical memory usage might cause performance issues.

To improve SSAS table query performance, you can:

• Adjust Memory Limits: Familiarise yourself with the memory limits in Tabular mode (low, high, and VertiPaq) and adjust them based on your needs. The low memory setting uses 65% of physical memory, while the high memory setting uses 80%. The VertiPaq setting uses 60% but only defines memory limits for the in-memory storage engine.
• Only Include Essential Columns: Reduce your data model size by limiting the number of columns. This will save memory during columnar compression.
• Limit Cardinality: Minimise or modify columns with unique data, as these are minimally compressed and do not efficiently use memory.

Additionally, you can use Performance Monitor, a Microsoft Management Control (MMC) snap-in, to track resource usage. It collects counts of events, such as memory usage, and lets you set thresholds to generate alerts.

Monitor CPU activity

Monitoring CPU activity is crucial for maintaining optimal performance of your SQL Server databases. IDERA's SQL Server Activity Monitor offers a comprehensive solution by providing real-time insights into key performance metrics, including CPU usage. Here are some detailed instructions on how to use this tool for monitoring CPU activity:

• Viewing Real-Time CPU Usage: With the Activity Monitor, you can monitor CPU usage alongside other critical metrics like wait times, physical read/writes, cache hits, throughput, and processes. This information is presented in interactive time-series charts, making it easy to identify any anomalies or bottlenecks.
• Customizing the Dashboard: You have the flexibility to customize the dashboard according to your preferences. Adjust the sampling rate and time intervals for the time-series charts to focus on the data that matters most to you. This customization ensures that you're not overwhelmed with unnecessary details and can quickly identify CPU-related issues.
• Switching Between Instances: If you're managing multiple SQL Server instances, the Activity Monitor allows you to switch between registered instances effortlessly. With a single click, you can monitor CPU usage and other performance indicators across your entire SQL Server environment, both on-premises and in the cloud.
• Using SQL Check: IDERA also offers a free tool called SQL Check, which provides an intuitive graphical interface for monitoring CPU usage. It displays time-series charts for CPU usage, wait times, disk wait time, and other key indicators. Additionally, you can view a bubble chart for processes, showing their locking, wait, and transaction states.
• Baseline Comparison: To identify CPU-related issues effectively, it's essential to establish a baseline for comparison. IDERA's SQL Diagnostic Manager enables you to set SQL Server performance baseline metrics. By analyzing and comparing these baselines over time, you can pinpoint trends and deviations in CPU usage, helping you identify and address potential bottlenecks.
• Resource Prioritization: The SQL Diagnostic Manager also assists in prioritizing resources. By monitoring key diagnostic statistics, such as OS Memory Usage and SQL Server Memory Usage, you can ensure that the CPU is allocated efficiently. This proactive approach helps prevent slowdowns and other performance issues.
• Real-time Alerts: The SQL Diagnostic Manager offers real-time alerts to notify you of any critical issues, including CPU bottlenecks. You can customize these alerts to match your specific needs, ensuring that you're promptly informed when CPU usage deviates from accepted thresholds.
• SSAS Performance View: IDERA's SQL Business Intelligence Manager includes an SSAS performance view, which provides comprehensive information on your SQL Server Analysis Services service and instances. This view offers insights into the status, health, size, and version of your monitored instances. It also allows you to filter information by the monitored instance, edit instance properties, and manage credentials.

Monitor disk I/O rate

Monitoring disk I/O rate is crucial to ensure optimal performance of your SQL Server and prevent bottlenecks. IDERA's SQL Diagnostic Manager offers comprehensive tools to help administrators effectively monitor disk I/O and maintain system health. Here's a detailed guide on how to use IDERA SQL Diagnostic Manager to monitor disk I/O rate:

Disk Tab: The Disk tab in SQL Diagnostic Manager provides key insights into how your disks are utilised. It offers four customisable charts to analyse disk performance:

• Disk Queue Length: This chart displays the average length of the disk queue during the sample interval. You can view the total queue length for all disks combined or the average queue length per individual disk.
• SQL Server Physical I/O: This chart shows the number of read and write requests sent to the physical disk.
• Disk Reads/Writes per Second: These charts break down the number of reads and writes performed per disk per second.
• Average Disk ms/Read and Write: These charts indicate the average time taken for disk read and write operations during the sample interval.

• Disk Space Usage Forecast Tool: This tool enables administrators to proactively monitor disk space usage and predict future requirements. By analysing current disk usage and historical trends, administrators can easily determine when more disk space is needed and mitigate potential bottlenecks.
• Resources Summary Feature: The Resources Summary feature provides real-time charts to monitor key diagnostic statistics, including disk reads/seconds per disk. This allows administrators to keep a close eye on resource allocations and ensure the system is running smoothly.
• Custom Alert Thresholds: SQL Diagnostic Manager allows administrators to set custom alert thresholds for various metrics, including disk-level alerts. This ensures that any warning signs, such as high disk I/O rates, are promptly brought to the administrator's attention.
• SSAS Storage Engine Cache: IDERA's SQL Business Intelligence Manager offers additional insights into disk I/O through the SSAS Storage Engine Cache category. It includes charts for Data File Activity, Cache Copy Reads, and Queries Answered from Cache, providing a detailed view of disk utilisation.

Monitor MDX cell calculations

Monitoring MDX cell calculations is crucial for understanding the performance of Microsoft Analysis Services (SSAS) and addressing any issues. The number of MDX cell calculations can be monitored through the SSAS General section of the Performance Analysis Dashboard in SQL Sentry. This section provides insights into the average time spent by the SSAS instance on various activities, helping to identify problems with the SSAS Formula or Storage Engine.

To further enhance your understanding of MDX cell calculations, Performance Monitor, a Microsoft Management Control (MMC) snap-in, can be utilised. This tool allows you to track server and process performance using predefined objects and counters. By selecting the "Microsoft Analysis Services MDX Calculations" performance object, you can access detailed statistics. These statistics include the total number of evaluation nodes built by MDX execution plans, the current number of evaluation nodes, and the breakdown of these nodes into storage engine, cell-by-cell, bulk-mode, and other types.

Additionally, Performance Monitor enables you to set thresholds on specific counters, generating alerts to notify operators when certain conditions are met. This feature can be particularly useful for proactive MDX cell calculation monitoring.

Moreover, tools like SQL Sentry capture all MDX commands, allowing you to set time-based thresholds for collecting and analysing runtime statistics. This capability provides granular insights into the impact of specific MDX statements on SSAS table query performance, facilitating quick identification and resolution of performance issues.

By leveraging these monitoring tools and the detailed metrics they provide, you can effectively monitor MDX cell calculations, optimise SSAS performance, and ensure the smooth operation of your database applications.

Monitor cache hits and cache lookups

Monitoring cache hits and cache lookups is crucial for understanding the performance of the SSAS Formula Engine Cache and the SSAS Storage Engine Cache.

The Formula Engine Cache, or FE cache, is single-threaded, meaning each request is processed by a single core. It has two types of internal caches: one for flat data and one for calculated data. The flat cache, for example, is limited to 10% of the TotalMemoryLimit property of the Analysis Services instance.

The SSAS Formula Engine Cache includes charts for Query Pool Threads, Query Pool Job Queue Length, Processing Pool Threads (Idle vs. Busy), Processing Pool Job Queue Length, Flat Cache Activity (Cache Lookups vs. Cache Hits), and Calculation Cache Activity (Cache Lookups vs. Cache Hits).

The SSAS Storage Engine Cache, on the other hand, handles requests for sub-cube data, which is a subset or logical unit of data for querying, caching, and data retrieval. It consists of the Dimension and Measure Group Cache. The SSAS Storage Engine Cache displays charts for Data File Activity (Bytes Read vs. Logical Reads vs. Queries From File), Cache Copy Reads, Queries Answered from Cache, Filtered Queries Answered from Cache, Average Time per Query, Dimension Cache Activity (Cache Hits vs. Cache Lookups), and Measure Group Cache Activity (Cache Hits vs. Cache Lookups).

By monitoring the cache hits and cache lookups for both the FE cache and the SE cache, you can gain insights into the performance of the SSAS and identify areas where optimisation may be needed.

Frequently asked questions