SQL Server Analysis Services (SSAS) Performance Monitoring

Free Trial Request Quote

Struggling with SSAS slowdowns?

Troubleshooting SQL Server Analysis Services (SSAS) performance problems can be frustrating, especially for data professionals who are new to the platform. To identify and resolve SSAS performance challenges quickly and correctly, you must understand where potential bottlenecks might lie and what metrics help identify the problem areas.

Challenging to identify cube processing bottlenecks

Identifying when, how long, and what performance impact processing your cubes are having... 

SSAS restarting or crashing for unknown reasons

Lack of visibility into concurrent, inefficient, high-impact events can make it difficult to get the complete picture of your server’s issues.

Slow reporting delaying data delivery

If SSAS slows to a crawl just when you need to deliver reports to stakeholders, your system might need to be optimized for high-concurrency workloads.

Finding SSAS performance problems is burning time

If your SSAS performance troubleshooting involves setting up traces or DMV queries, then you might be wasting time you could be spending on more strategic initiatives.

Design-related performance problems

Finding performance problems related to the database design can be a struggle, especially if you can't easily consult the team who created the data model.

Querying and processing in SSAS

Querying and processing activities affect SSAS performance—whether you’re using Multidimensional mode or Tabular mode. Many of the same concepts and best practices applied to queries and backups in relational databases such as SQL Server can be applied to or used to understand querying and processing in SSAS.

Activity Description
 Querying
  •  Languages used when querying SSAS:
    • Multidimensional Expressions (MDX); most often used in Multidimensional mode 
    • Data Mining Extensions (DMX)
    • Data Analysis Expressions (DAX); most often used in Tabular mode
  • Similar to querying relational databases
Processing
  • Creates or updates data and recalculates and reorganizes existing data, primarily through XML for Analysis (XMLA)
  • Similar to backups on relational databases


Finding the source of SSAS performance bottlenecks

When performing queries, SSAS uses two primary engines: the formula engine and the storage engine. Either engine can be the source of a performance bottleneck; troubleshooting identifies which engine is the problem. The table below breaks down the primary features of each type of engine.

Engine Type Features
Formula Engine
  • Performs complex calculations
  • Single-threaded per request
Storage Engine
  • Reads data and writes data to storage
  • In Multidimensional mode, can read from the file system on the server; both physical disk input/output (I/O) as well as Windows file cache
  • In Tabular mode, data is typically already in-memory, unless there are paging or memory pressure issues; usually has no physical I/O
  • Multithreaded


The first thing you need to determine when looking at performance problems is whether processing activities are happening at the same time as querying activities. Once you've ruled that out, SQL Server Profiler trace events can determine which query engine—formula or storage—is creating the bottleneck. The following table provides details on the Profiler trace events that are most important when troubleshooting SSAS performance challenges.

Profiler Trace Event Trace Event Indicators
Command Begin/End Processing commands begin and end
Query Begin/End Queries begin and end
Query Subcube/ Verbose Duration the formula engine is querying to the storage engine (Multidimensional mode)
Progress Report Begin/End Begin and end of file reads (Multidimensional mode)
Get Data From Aggregation Time spent getting data from aggregations. This data is stored, pre-calculated data typically needed to handle the workload in the environment (Multidimensional mode)
VeritPaq SE Query Begin/End Duration the formula engine is querying to the storage engine (Tabular mode)
Direct Query Begin/End Time spent querying back to the relational data source for the data

 
To determine which engine is the source of the problem, add up the cumulative durations indicated in Query Subcube/Verbose, Progress Report Begin/End, Get Data from Aggregation, VertiPaq SE Query Begin/End, and Direct Query End and compare to the Query Begin/End total time.

If the total duration is:

  • 50% or more of the Query Begin/End total time, the problem is in the storage engine.
  • Less than 50% of the Query Begin/End total time, the problem is in the formula engine.


Key areas to investigate when troubleshooting query performance issues

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.

 
Optimizing MDX or DAX queries

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:

  1. 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.
  2. 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.
  3. 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.

 

Quickly find and fix SSAS performance problems with SolarWinds SQL Sentry

SQL Sentry empowers you to monitor, diagnose, and optimize 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, as well as 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 that you can identify and address common SSAS performance problems.

  • Comprehensive visibility into processing activity, its performance, and impact on your workload

  • Unique insight into VertiPaq memory limits and paging policy 

  • Top Commands view that captures and aggregates all high-impact SSAS commands (MDX, DMX, DAX, and XMLA), and displays top commands by CPU and I/O usage

  • Object Memory Usage tab that helps you optimize in-memory compression of your data model

  • Visibility into SSAS cache and disk activity   

 

SQL Sentry provides actionable information so that you can proactively address SSAS performance bottlenecks

 

The SQL Sentry performance dashboard shows both Windows- and SSAS-specific metrics side by side for a complete view of data platform performance. Learn more about the extended support for SSAS Tabular in SQL Sentry. 

SQL Sentry gives you a complete view of SSAS performance so that you can identify and address problems that have widespread impact across your data estate.

Unleash SSAS performance with these powerful capabilities

 

sql-sentry-ssas-top-commands

SSAS Top Commands

See the total impact of your MDX, DAX, or XMLA Commands

The SQL Sentry Top Commands tab shows SSAS query-level collection and any MDX, DAX, or XMLA commands that have run longer than one second. All the like queries have been rolled up and grouped together so you can see their total impact.

Learn More

SSAS Usage Totals

Use relevant metrics to troubleshoot SSAS problems

The SQL Sentry SSAS Usage Totals tab provides information that can be used to determine what responses to take based on activity levels. For Multidimensional mode, this information is divided into three different groups:

  • Attributes—used to see which attribute combinations are hit the most to determine if creating an aggregation would improve performance

  • Aggregations—shows the aggregations that are being used

  • Partitions—shows which partitions are being read the most

In Tabular Mode, view Object Memory Usage to easily see how your data models are being stored and compressed in-memory and identify areas where you might be able to increase data compression for more efficient memory consumption.

Learn More

sql-sentry-ssas-usage-totals
sql-sentry-ssas-performance-metrics

SSAS Performance Metrics

Accelerate SSAS performance with detailed, relevant metrics

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

Ready to end your database performance problems?

 

Download a SQL Sentry trial

See for yourself how SQL Sentry can help you achieve optimal database performance.

Download Free Trial

Schedule a SQL Sentry demo

Learn more about how SQL Sentry can help you troubleshoot and prevent database performance problems. Register to attend a public demo or schedule a one-on-one session.

Schedule Demo

Learn more about SQL Sentry

Discover how you can quickly find and fix SQL Server performance problems with SQL Sentry, which delivers proven scalability, easy-access dashboards, and visibility across your entire database environment.

Learn More