SSAS Performance Monitoring Tool

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 Limits
  If 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 Need
  Decreasing 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 Cardinality
  Whenever 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.

The cause of SSAS performance problems could be lurking in any number of places. To successfully troubleshoot SSAS performance, you need a baseline. You also need the right monitoring tool—one that can give you deep insights into the performance metrics that help you analyze your SSAS environment in real time and during a specified period in the past.

SQL Sentry gives you the detailed metrics you need to monitor, diagnose, and optimize the performance of your SSAS environment.

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.