SSAS Performance Monitoring Tool

Quickly find and fix SQL Server Analysis Services (SSAS) performance problems

Download Free Trial Email Link to Trial

Fully functional for 14 days

Learn More

Unlock Actionable Data

Proactively address SSAS performance bottlenecks

SQL Sentry^® empowers you to monitor, diagnose, and optimize the SQL Server Analysis Services (SSAS) Multidimensional and Tabular modes. With SQL Sentry, you gain unparalleled insight into SSAS performance issues, including bottlenecks related to memory and storage systems, aggregation usage, unoptimized queries, and query and processing tasks competing for the same resources.

SQL Sentry includes enhanced support for SSAS Tabular mode and continued support for Multidimensional mode so you can identify and address common SSAS performance problems.

Download Free Trial Email Link to Trial

Fully functional for 14 days

Learn More

Streamline and optimize SSAS performance tuning with clear, relevant table model SSAS tuning metrics

SQL Server Analysis Services (SSAS) performance tuning is notoriously difficult because it’s challenging to gather relevant metrics all in one place. The information needed to find a root cause could be anywhere, and you don’t necessarily have the time to search for it. SolarWinds^® SQL Sentry offers a performance analysis dashboard including SSAS Activity, SSAS General, SSAS Memory, and SSAS Storage—four key metrics impacting SSAS table query performance. Now, you can take a quicker and more targeted approach to SSAS performance tuning.

Download Free Trial Email Link to Trial

Fully functional for 14 days

Learn More

Use SSAS Top Commands to understand the total impact of SSAS performance and operations tuning

To dig deeper into SSAS performance and operations tuning, you need to break down SSAS performance issues with user hierarchies by time and see how specific events affected performance. SQL Sentry captures all MDX, DAX, DMX, and XMLA commands, and you can set time-based thresholds for collecting them. By viewing runtime stats for each command and investigating queries at a granular level, you can see which statements affect SSAS table query performance the most and address them quickly.

Download Free Trial Email Link to Trial

Fully functional for 14 days

Learn More

Troubleshoot SSAS performance issues with user hierarchies more effectively with SSAS Usage Totals

SSAS Performance Tuning Tool 3 Features Array Item - features item image

In high-performance SSAS environments, having set remediation actions at the ready is critical. SQL Sentry SSAS Usage Totals offer the deeper data usage insights you need to troubleshoot rapidly. “Attributes” shows which attribute combinations hit most often, “Aggregations” shows which aggregations are being used, and “Partitions” shows which partitions are read the most. These three groups will help you determine what responses must be taken based on activity levels, which speeds up troubleshooting.

Download Free Trial Email Link to Trial

Fully functional for 14 days

Learn More

Unlock more actionable data with extended support for performance tuning on SSAS tabular

As more DBAs start monitoring SSAS in Tabular mode, performance tuning on SSAS tabular becomes essential. SQL Sentry now features enhanced support for SSAS in Tabular mode. New Tabular mode-exclusive metrics have been added to the performance dashboard, SQL Sentry now features enhanced DAX query analysis, and the SSAS Usage Totals now include Object Memory Usage. These features will help you take table model SSAS tuning to the next level.

Download Free Trial Email Link to Trial

Fully functional for 14 days

Learn More

Get More on SSAS Performance Monitoring

Frequently asked questions

SSAS Performance Metrics
Why are SSAS performance monitoring and tuning important?
How to improve SSAS table query performance in SSAS tabular mode?
How to improve processing performance in an SSAS attribute hierarchy?
How can I get to the root cause of SSAS performance problems?
How can I successfully troubleshoot SSAS performance problems?
How to optimize MDX or DAX queries?

The SSAS Performance Metrics displayed by the Performance Analysis Dashboard in SQL Sentry give you valuable insight into the root cause of performance problems with SSAS. With actionable information, you can save time in troubleshooting SSAS performance.

SSAS Activity

The SSAS Activity section displays information to help you correlate the rate of rows sent by the server to the clients with the number of threads used by the SSAS Formula and Storage Engines.

SSAS General

The SSAS General section displays information about the activity taking place on your monitored server instance, including average time, which shows what the SSAS instance is spending most of its time doing. The General section also displays activity such as the number of MDX cell calculations, which can help you pinpoint issues with either the SSAS Formula or Storage Engine.

SSAS Memory

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

SSAS Storage

The SSAS Storage section provides information to help you determine whether the disk system is a bottleneck for your SSAS instance.

All database administrators know databases, particularly SQL databases, are invaluable resources you can’t simply set and forget. They must be constantly monitored and optimized to ensure they’re operating as efficiently as possible, actively contributing to your IT environment's success, not detracting from it. The same can be said for SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS) packages.

SSAS performance tuning and monitoring are essential because these processes help you optimize the performance of your business-critical information and find, isolate, and remediate performance issues before they turn into full-fledged bottlenecks.

Finding performance problems in SSAS and SSIS can be challenging for DBAs and their data teams because it’s often difficult to identify the real underlying cause of said performance problems. This becomes especially difficult if you can’t consult the team who created the data model you’re using. Your SSAS might restart or crash unexpectedly and cause a processing or SSAS table query performance issue. Or, your SSAS might slow to a crawl right before you need to send an important report to a stakeholder. SSAS performance issues with user hierarchies like these are common. They can be resolved without seriously compromising your productivity if caught early enough, but finding SSAS performance problems is incredibly time-consuming. What’s more, if you’re not using an SSAS performance and operations tuning tool, you might be missing data points that could be the key to finding and resolving performance issues. All of the time you spend setting up traces or dynamic management view (DMV) queries is time taken away from more strategic initiatives and projects.

The best way to solve your SSAS or SSIS performance problems is to use an SSAS performance tuning tool like SQL Sentry. SQL Sentry helps DBAs optimize the performance of their business-critical information and gain unparalleled insight into SSAS table query performance issues.

One of the best ways to improve SSAS table query performance is to improve your SSAS memory efficiency. Memory constraints are a common issue with SSAS because the SSAS Tabular database is entirely in-memory in Tabular mode, and performance bottlenecks are particularly challenging to unravel in this mode. Why? When SSAS is in Tabular mode, the database in-memory is compressed 10 times smaller than it would be on disk. If your system isn’t configured correctly, any data-processing actions you take can bring your SSAS to a crawl. The best way to avoid this is to use a memory-efficient data model to ease any memory pressure caused by processing data in SSAS.

Here are three ways to improve SSAS table query performance, reduce bottlenecks, and boost processing speeds by improving memory efficiency in SQL Sentry:

Adjust Memory LimitsIf you run your SSAS on the same machine as your SQL Server instance, you might run into issues as the two programs compete for resources and try to use as much of the system’s memory as possible. The best way to avoid this problem is to familiarize yourself with the memory limits in Tabular mode—low, high, and VertiPaq—and adjust them accordingly based on your current needs.The low memory setting uses 65% of the physical memory on the server, and high memory uses 80%. Whenever the total memory limit has been exceeded, the SSAS cleaner will conservatively or aggressively remove things from the cache to free up more space. The VertiPaq setting uses 60% of the physical memory but only defines the memory limits specific to the in-memory storage engine.You can improve memory efficiency further by setting memory limits allowing a minimum of 2.5x the compressed data size. Doing so creates a shadow copy of your data each time it’s run and reduces any additional overhead from requests made on the system while the data is being processed.
Only Include the Columns You NeedDecreasing your data model size makes your data model more efficient, which can give your SSAS table query performance a much-needed boost. One easy way to reduce the size of your data model is to limit the number of columns included. If you only have the columns essential from the relational data source, you’ll save memory during columnar compression.

Limit CardinalityWhenever possible, you should limit cardinality. Columns with unique data are minimally compressed if it all, which isn’t an efficient use of memory. If you have a column with high cardinality, improve compression by either modifying it or splitting it into separate columns.

The SSAS Performance Metrics displayed by the Performance Analysis Dashboard in SQL Sentry give you valuable insight into the root cause of performance problems with SSAS. With actionable information, you can save time in troubleshooting SSAS performance.
SSAS Activity
The SSAS Activity section displays information to help you correlate the rate of rows sent by the server to the clients with the number of threads used by the SSAS Formula and Storage Engines.
SSAS General
The SSAS General section displays information about the activity taking place on your monitored server instance, including average time, which shows what the SSAS instance is spending most of its time doing. The General section also displays activity such as the number of MDX cell calculations, which can help you pinpoint issues with either the SSAS Formula or Storage Engine.
SSAS Memory
The SSAS Memory section gives you details about how the server instance is using the memory allocated to it. When monitoring a Tabular instance, you can see the memory counters and the VertiPaq memory limit. This data helps you understand where physical memory usage might cause performance issues.
SSAS Storage
The SSAS Storage section provides information to help you determine whether the disk system is a bottleneck for your SSAS instance.
One of the best ways to improve SSAS table query performance is to improve your SSAS memory efficiency. Memory constraints are a common issue with SSAS because the SSAS Tabular database is entirely in-memory in Tabular mode, and performance bottlenecks are particularly challenging to unravel in this mode. Why? When SSAS is in Tabular mode, the database in-memory is compressed 10 times smaller than it would be on disk. If your system isn’t configured correctly, any data-processing actions you take can bring your SSAS to a crawl. The best way to avoid this is to use a memory-efficient data model to ease any memory pressure caused by processing data in SSAS.
Here are three ways to improve SSAS table query performance, reduce bottlenecks, and boost processing speeds by improving memory efficiency in SQL Sentry:
Adjust Memory LimitsIf you run your SSAS on the same machine as your SQL Server instance, you might run into issues as the two programs compete for resources and try to use as much of the system’s memory as possible. The best way to avoid this problem is to familiarize yourself with the memory limits in Tabular mode—low, high, and VertiPaq—and adjust them accordingly based on your current needs.The low memory setting uses 65% of the physical memory on the server, and high memory uses 80%. Whenever the total memory limit has been exceeded, the SSAS cleaner will conservatively or aggressively remove things from the cache to free up more space. The VertiPaq setting uses 60% of the physical memory but only defines the memory limits specific to the in-memory storage engine.You can improve memory efficiency further by setting memory limits allowing a minimum of 2.5x the compressed data size. Doing so creates a shadow copy of your data each time it’s run and reduces any additional overhead from requests made on the system while the data is being processed.
Only Include the Columns You NeedDecreasing your data model size makes your data model more efficient, which can give your SSAS table query performance a much-needed boost. One easy way to reduce the size of your data model is to limit the number of columns included. If you only have the columns essential from the relational data source, you’ll save memory during columnar compression.
Limit CardinalityWhenever possible, you should limit cardinality. Columns with unique data are minimally compressed if it all, which isn’t an efficient use of memory. If you have a column with high cardinality, improve compression by either modifying it or splitting it into separate columns.
Processing in SSAS involves creating and updating cubes with new data and recalculating or reorganizing existing data. All processing involves a multi-threaded Storage Engine (SE), which means processing can quickly become a resource-intensive endeavor. Slow processing speeds will bring your SSAS to a crawl, negatively affecting performance tuning in SSAS. What do you do if you suspect slow processing speeds are causing your SSAS performance issues?
The good news is you can use many of the same techniques used to optimize processing speeds in SQL Server to optimize processing speeds in SSAS. Here are two tried-and-true tips for improving processing performance in an SSAS attribute hierarchy:
Check your scheduling: Sometimes, processing performance issues are simply caused by bad timing. In any enterprise or IT department, resource usage naturally ebbs and flows throughout the day. You might have noticed processing speeds are a little slower on Monday mornings at 9:00 a.m. than on Friday afternoons around 5:00 p.m. when employees prepare to leave for the weekend. Whenever possible, schedule all processing activities during off-peak hours or when there won’t be increased querying activity going on in the server to boost processing performance.
Check your processing type: Like SQL Server can utilize many different types of backup strategies, SSAS can use many various processing commands. If you choose a processing command not optimized for the specific processing task, you might end up using more resources than necessary. This can slow down your processing speeds. To avoid this, make sure you’re using the correct processing type to help you complete your task as quickly and efficiently as possible.
You’ll need accurate information, and you’ll need to consider the following:
Are the MDX or DAX queries performing poorly?
Do you have details about the SSAS Storage Engine processing performance?
Does your server have memory or other server resource constraints?
In addition to understanding which query engine is affecting performance, it’s essential to identify specific query and system attributes that are contributing to performance problems.
Processing uses resources, impacting query performance if querying is happening at the same time. Questions to ask include: When is processing happening? How is processing happening? How often is processing happening?
Un-optimized code, including MDX and DAX queries, can be rewritten to improve query performance.
Server resources—CPU, memory, disk, and network—can negatively affect performance.
In some cases, the identified performance problems involve query optimization. When MDX or DAX query optimization is necessary, there are three primary factors to consider:
Query construction choices—There are many ways to construct a query, and some will perform better than others. Functions used in the query can make a significant difference in performance. For example, in DAX, many developers choose to use the Filter function instead of the Calculate function because it is easier to use. However, the Calculate function pushes more of the activity down to the VertiPaq storage engine, optimizing performance.
Order of operations—Beyond the functions chosen, efficiency can also be affected by the way a query is written (i.e., its order of operations). For example, it might be more efficient to perform a calculation on data first, and then apply the filter after a quick scan in the storage engine for the results.
Understanding the data—A deep understanding of the data also plays a significant role in query optimization. Team members need to know the data to understand how to apply the functions and order of operations since the data and environment will affect performance. In some cases, this will not be straightforward; optimization will be a matter of trial and error as new information about the data is understood.

Get started with SQL Server Analysis Services (SSAS) performance monitoring

Fast root cause analysis
Visibility across the data estate
Deep, actionable performance metrics with easy deep-dive capabilities

Download Free Trial Email Link to Trial

Fully functional for 14 days

Let's talk it over.

Contact our team. Anytime.

Explore More Resources

View All Resources