Part 4: An Introduction to SSAS Performance and SQL Sentry Performance Advisor for Analysis Services

In my last post I talked about how to detect performance issues related to unoptimized MDX and different strategies based on the type of issue. A big part of ensuring that you are as efficient as possible with your troubleshooting efforts is to first identify if the bottleneck is in the Formula Engine (FE) or Storage Engine (SE). Technically unoptimized MDX itself is likely to manifest as an FE bottleneck where an SE bottleneck really resides more with the underlying cube architecture. That said, all of this really ties back to the first post and the anatomy of an MDX query.

In part 1, I walked through the anatomy of an MDX query and described what role the FE and SE play in the handling of a request. When troubleshooting slow MDX performance, it’s not likely that the bottleneck is entirely with the FE or entirely with the SE. So how do you tell where the biggest bottleneck is, so you can ensure you are getting the most out of your tuning efforts?

Where is SSAS spending most of its time?

To put it simply, if SSAS is spending most of its time in the FE while handling a request, that’s where you should start your troubleshooting; likewise for the SE. But how can you accurately measure something like that? The most accurate way using native tools is a trace. There are certain trace events in SSAS that map closely to the activity we covered in the “anatomy of MDX” discussion. I’ll break them down here:

SSAS Trace Events

Command Begin/End – This signifies the beginning/end of an XMLA command. This is often associated with processing activity.

Query Begin/End - This encapsulates MDX, or other queries like DMX, SQL, etc. from request submission to results returned.

Query Subcube/Verbose – The FE requests data from the SE

Get Data From Cache – There was a cache hit to either the SE or FE caches. The event subclass will tell you which.

Progress Report Begin/End – This indicates SE file system activity either for processing or querying. The event subclass will tell you which.

Get Data From Aggregation – This indicates the SE made use of an aggregation to return data from the file system.

There are other events and additional detail that is provided within the trace, but this covers the primary events we’ve discussed. Note there can be many of these events, excluding the command or query begin and end, for a single request and it can get a bit complicated fairly quickly, especially if there are multiple requests hitting the server at the same time. However, if you are troubleshooting a particular query and run a trace, you do have the information to add up the duration for each event and come up with an idea of where most of the time is being spent.

Fortunately, SQL Sentry PA for SSAS does all of this for you! You may have noticed a rather unique chart on the PA for SSAS dashboard.

This chart monitors all of this activity for you continuously at a fraction of the overhead a Profiler trace tends to impart. It summarizes this information in real time and retains history so you can quickly and easily see where most of the time is being spent. It even breaks it down into more detail for you. Serialization is handled by the FE and is related to Non Empty behavior as discussed in an earlier post. Processing and SQL queries are handled by the SE. By the way, SQL queries are likely due to data reads for processing, but can also be seen if you are using ROLAP partitions.

For a further breakdown of this activity on a query by query basis in PA for SSAS, refer to the Top Queries tab. Not only will you see if your query experienced more FE or SE time, but you can expand the view for a complete breakdown by Measure Group, Partition, Aggregation, and Dimension. More information on this functionality is available on the website.

In the next post I’ll dig into troubleshooting server resource bottlenecks specific to SSAS. We’ll take a closer look at the hardware side of things and discuss some configuration options to ensure your server is properly tuned.

Part 3: An Introduction to SSAS Performance and SQL Sentry Performance Advisor for Analysis Services

In my last post I discussed SSAS processing. I showed you some counters that can help identify when processing is taking place, and at what rate. I then gave some suggestions as to how you might be able to optimize your processing strategies in order to improve overall performance on your server. In this post I’m going to talk about unoptimized MDX code as the source of your performance issues.
Unoptimized Code
There are quite a few resources available on how to write MDX, so I won’t be going into that here. The point I want to make is that there are ways you can identify queries running on your SSAS instance that may benefit from optimization, even without actually knowing MDX. In this section I’ll mention a handful of key performance counters that will allow you to identify issues related to query optimization. You can then take this knowledge to the developers of the queries with useful feedback as to how to improve performance.
Monitoring MDX

Specific to MDX, there are a few performance counters that can provide a lot of insight. I’ll actually group them into three general issues. The first group are:
· MDX: Total cells calculated
· MDX: Number of calculation covers
· MDX: Total Sonar subcubes
A high number for any of these metrics while a query is executing suggests the query is using cell by cell calculations instead of a block-oriented, also known as “subspace”, evaluation. So what does that mean? To stick with our SQL example from before, you can relate cell by cell calculations to using cursors in SQL. This is often an inefficient and resource intensive way of processing data. Maybe you’ve heard of the term ”RBAR” in SQL development, which stands for “Row By Agonizing Row”. Well you could call this CBAC, or “Cell By Agonizing Cell”.
The MDX performance whitepaper I mentioned in the first post in this series goes into great detail about this. There have been many enhancements to block-oriented evaluation mode since then and updated details can be found in Performance Improvements for MDX in SQL Server 2008 Analysis Services.
Another counter that can indicate serious performance issues with MDX:
· MDX: Total recomputes
This indicates errors in calculations during query execution. A non-zero value here can indicate issues where unnecessary recalculations are taking place and can even lead to a fall back to cell by cell mode in an effort to eliminate the error.
Jeffrey Wang has a great blog post that walks though some examples of where recomputes can have a dramatic impact on performance.
One more set of counters that I think gives us a good look into MDX performance:
· MDX: Total NON EMPTY unoptimized
· MDX: Total NON EMPTY for calculated members
In a nutshell, SSAS data tends to be sparsely populated data. Take data on sales orders for an example. A typical sales order is not likely to include at least one of every item available for purchase. There are likely to be a lot of empty cells for items not purchased for any given order.
You’ll remember earlier I mentioned the performance impact of cell by cell calculations. When you have sparse data, specifying a NON EMPTY algorithm can also speed up performance, but there is actually more than one code path for this algorithm. A slower code path can actually cause performance degradation. This behavior has been improved in SSAS 2008, but can still occur. The above counters can help you identify when that’s the case. More details can be found in the NON_EMPTY_BEHAVIOR section of the Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014.
Improving MDX Performance
So to wrap up this edition of the series, what are some primary strategies for optimizing MDX performance? First, proper aggregations and partitions can make a big difference, especially if the bottleneck is in the storage engine. There is tons of information on aggregation strategy that I won’t repeat here, but to reuse our analogy, aggregation strategy is similar to SQL index strategy. There is a sweet spot. Too many aggregations can be just as bad as too few. Aggregations provide pre-calculated data that can improve query performance. However, aggregations must be maintained and too many will increase the resource requirements during processing as well as storage requirements for all this extra data.
If the bottleneck appears to be in the formula engine, watch for cell by cell calculation, and eliminate empty cells and tuples using the recommended strategies in the linked whitepapers mentioned above.
So at this point you might be wondering, aside from the counters we just mentioned, how do we know where the main bottle neck might be, formula engine or storage engine? Where should we focus our performance troubleshooting? I’ll help you further pinpoint those efforts in the next post.

Part 2: An Introduction to SSAS Performance and SQL Sentry Performance Advisor for Analysis Services

In the last post, I spoke about the fact that SQL Sentry Performance Advisor for Analysis Services (PA for SSAS) “lifts the veil” from the black box known as SQL Server Analysis Services. But, there is little information available that helps someone new to the technology easily interpret all this new information in order to optimize the performance of their servers. In an effort to provide some insight into this new source of information, I wrote about the SSAS engine and how it handles incoming requests. In part 2 of this series, I want to dig deeper into the common sources of bottlenecks and what information is available to troubleshoot these issues.

What can impact MDX performance?

For the purposes of this post, I’ll break down into six categories what can impact MDX performance on your SSAS server.

Those categories are:

· Processing

· Unoptimized code

· CPU

· Memory

· Disk

· Network

I’ll tackle each one by giving an explanation and refer to specific metrics, primarily performance counters, that can be used to analyze the given issue.

Processing

I mentioned in the last post that processing involves the creation and updating of cubes with new data and/or recalculating and reorganizing existing data. I related it to maintenance on SQL Server and many of the same factors you consider when you implement maintenance in SQL Server apply to processing in SSAS.

Processing primarily involves the Storage Engine (SE), which you’ll remember is multi-threaded, and can obviously be a resource intensive operation. Since processing may involve new data or recalculation of existing data, cached data will become outdated and will be flushed. This then means that the next time a query runs that requires that data, the SE will have to query the file system to retrieve needed data from the Windows cache or the disk system to replace the flushed data.  This will result in a performance hit to the query on its first run after processing. Querying while processing is occurring on the same cube can lead to blocks. Querying any cube while processing is occurring can lead to general resource contention on the server.

Monitoring Processing

So how can we tell when processing is occurring on the server? While there are many performance metrics that have varying levels of usefulness, I’ll mention a handful for each topic that will give you better insight for that topic. Don’t infer that if I don’t mention it, it’s not useful or vice versa. The point of this series is to give you a better understanding of what’s going on in SSAS and how to make better use of the information tools like SQL Sentry’s PA for SSAS provide.

· Processing: Rows written/sec tells you the rate of rows written during processing.

· Proc Aggregations: Rows created/sec more specifically tells you the rate aggregation rows are created during processing.

· Proc Indexes: Rows/sec tells the rate of rows from MOLAP files used to create indexes.

So, a non-zero value for these counters confirms that processing is taking place. These metrics can also help determine the effectiveness of any processing tuning efforts.  If your tuning efforts are effective, you should see an increase in these values accordingly.

Improving Processing Related Performance

So how do you improve performance related to processing? I mentioned earlier that you can relate SSAS processing with maintenance in SQL Server. Many of the same techniques applied in SQL Server maintenance apply here as well.

· Scheduling – If possible, schedule processing activities during time of least querying activity on the server to avoid blocks and resource contention.

· Processing Type – Use the most appropriate processing commands. Just as there are different types of backup strategies in SQL Server such as Full, Log, Differential, etc. the same applies in SSAS processing. For example, use ProcessData and ProcessIndex where appropriate instead of ProcessFull.

· Partitioning – If you’re working with Enterprise Edition, be sure to take advantage of the ability to break Measure Group data into partitions. Not every partition may need to be processed every time. Partitioning can serve to reduce the overall amount of work that is done. Remember that the SE is multi-threaded, so architect your data to take advantage of parallel processing capabilities.

For more information see the Analysis Services Processing Best Practices article in TechNet.

We’ve really just scratched the surface with the series.  In the next installment I’ll discuss identifying and understanding issues related to unoptimized MDX, and from there we’ll finish with server resource bottlenecks.  Stay tuned!

Part 1: An Introduction to SSAS Performance and SQL Sentry Performance Advisor for Analysis Services

Over the past couple years, since SQL Sentry introduced Performance Advisor for Analysis Services, I’ve heard many people say they see SSAS as a “black box” on their servers with no real visibility into troubleshooting performance issues. Performance Advisor (PA) lifts the shroud from Analysis Services, but for some it may not be obvious how to interpret all the information gained from this new visibility.

There are many resources out there that cover OLAP architecture and MDX, but much less in the way of an explanation to all the various metrics that are available and how they correlate with each other. In response, I developed a presentation I’ve given several times now at SQL Saturdays that I believe serves to “bridge the gap” for DBA’s that may be new to SSAS, or possibly even developers who lack a dedicated administrator and need to be able to tell where the bottleneck might be on their SSAS server.  I was also privileged to be invited to an interview on RunAs Radio where I spent some time discussing this with Richard Campbell and Greg Hughes.

Finally I’ve had the time to put much of this same advice down here. This is the first part of a couple posts I’ll dedicate to the topic. Anyone working with SSAS should find this useful, but users of PA for SSAS should find themselves even more proficient in troubleshooting SSAS performance issues. We’ll start with a very basic introduction to the service itself and how a query is handled, and move into specific metrics and identifying bottlenecks shortly.

Basic Architecture

It’s important to understand that SSAS is its own engine. The executable is msmdsrv.exe. It’s licensed with SQL Server, but in large production environments you aren’t likely to be running it on the same machine as a SQL Server. There are two primary types of activity that occur here; querying and processing.

Types of Activity

Querying can be done by various methods, but the most common type of queries are Multidimensional Expressions (MDX). This is just a standard language, similar to SQL, optimized for calculations. I won’t go into the how’s and why’s of MDX itself as there are many resources already available on its mastery. While this series won’t tell you how to write MDX, by the time we’re done you should be able to tell, with a few basic pieces of information described later, whether there is room for query optimization or if the bottleneck lies elsewhere.

Processing is essentially the maintenance activity of your cubes. It’s how cubes are created and updated with new data and/or how existing data is recalculated and reorganized. Just as with SQL Server, what level of processing you perform and when is essential to ensuring proper performance without interfering with peak querying times. We’ll dive into those options later.

The Formula Engine

Under the covers there are two primary “engines” within SSAS, the Formula Engine (FE) and the Storage Engine(SE). The FE accepts MDX requests and parses the query to process. It will send requests to the SE when data is needed. It then performs calculations on the retrieved data to provide a final result set. An interesting thing about the FE is that it is single threaded. If you are watching the CPU’s of your multi-core server with SSAS while a request is being handled and you only see one core spike, it’s probably because the FE was doing the work at that time.

The Storage Engine

The SE on the other hand is multi-threaded. The SE reads and writes data to and from the file system for the server. This can include working against the Windows file cache. This is an important distinction between Analysis Services and the relational engine in SQL Server. SQL Server’s relational engine has an internal cache, and then goes to disk for any other needed IO. It does not utilize the Windows file cache for database files. SSAS, on the other hand, does. This means that when SSAS is interacting with the file system on your server, it does not necessarily mean this will result in physical disk IO. SSAS utilizes its own caches as well as the file system cache to get what it needs.

SSAS Caches

So how does SSAS cache data? Each engine within SSAS has its own set of caches. The FE caches store flat values (the Flat Cache) as well as calculated data (the Calculation Cache). It’s important to understand that these caches are often scoped, which means the data might only be available for a limited time or only for a particular user, even in cache. The flat cache in particular is in fact restricted to a max of 10% of the TotalMemoryLimit property in SSAS, which we will discuss later.
So, that said, when we talk about SSAS cache optimization, warming, etc., we are more often dealing with the SE caches. I know that’s a bit of a simplification, but this is an introduction after all. ;)
So the SE consists of a Dimension Cache and a Measure Group Cache to store the data retrieved from the corresponding sources in the file system itself.

Anatomy of an MDX Query

So now that we understand the basic components under the covers in SSAS, how are they used to handle a request? First, the query is accepted and the FE parses it. If the appropriate data is already available in the proper scope in the FE cache, some or all may come from there. The FE then requests any other needed data from the SE. The SE then retrieves whatever it can from its caches. If still more data is needed, the SE queries the file system on the server. The data from the file system may, at least in part, come from the Windows file cache. Remember we mentioned this distinction earlier. Anything else results in physical disk IO. These steps may be iterated as needed while data is moved from SE caches to FE caches, etc., but you get the picture. Another graphical representation of this process can be found in Appendix A of the Microsoft Best Practices Article “Identifying and Resolving MDX Query Performance Bottlenecks in SQL server 2005 Analysis Services”, authored by Carl Rabeler and Eric Jacobsen. I highly recommend reviewing this article for a very in-depth look into this topic. Even though the title mentions SQL Server 2005, it’s still very relevant and useful.

What can impact MDX performance?

So now that we have a good understanding of how SSAS handles MDX requests, the question we need to answer is, “What can impact MDX performance, and how can we tell”? That is the question we will answer in the next post.