Achieving Peak SQL Server Analysis Services (SSAS) Performance

Troubleshooting performance problems with SQL Server Analysis Services (SSAS) 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. In the SentryOne webinar "Getting Peak Performance for SQL Server Analysis Services (SSAS)", SentryOne Director of Sales Steve Wright discusses querying and processing in SSAS and describes how to identify bottlenecks when a query runs. He also demonstrates how SentryOne BI Sentry can help efficiently troubleshoot and resolve SSAS performance problems.

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

 

“If you understand SQL Server best practices, it makes it easier to translate what you’re working with and seeing when trying to troubleshoot issues in SSAS.”

– Steve Wright, SentryOne

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.

Identifying and Solving SSAS Performance Problems

 

Although SQL Server Performance Monitor (PerfMon) and other tools offer many metrics useful in identifying performance problems, SentryOne’s BI Sentry helps IT teams identify performance problems in a way that they can drill down to both find and resolve the cause of the issues.

BI Sentry continuously collects and stores metrics so that you can use historical information in an investigation. The dashboard (shown below) allows you to correlate multiple metrics across time frames, simplifying your view into potential problem areas.

 

BI Sentry Dashboard

 

“In BI Sentry, you can go back at any point in time to see what was going on; you don’t have to be looking at the server when the problem is occurring to investigate it.”

– Steve Wright, SentryOne

 

From the dashboard, you can drill down into additional information that can be useful in troubleshooting. For example, BI Sentry makes it easy to identify whether the formula engine or the storage engine is the problem by displaying the percentage of time a query spends within each engine, as well as raw counter information.

Another useful tool is an Outlook-style calendar view that shows all queries, processes, and other events that occurred in SSAS. This calendar can help identify potential resource conflicts as well as failed events that might have affected performance.

 

BI Sentry Calendar View

 
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.

 

Additional Resources The SentryOne team’s SSAS blogs offer insight into SSAS and how SentryOne is used to troubleshoot and fix Microsoft Data Platform performance challenges. Using tools such as SentryOne BI Sentry helps take the guesswork out of SSAS performance problem identification and resolution. SQL Server Customer Advisory Team (SQLCAT) provides white papers and other documentation that offer SSAS tips, including information about query construction.