Friday, January 13, 2012

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


· 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.


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!


  1. This comment has been removed by the author.

  2. So you're responsible for managing a documentation project. Before jumping into the writing, it's a good idea to understand the scope of the project. useful site

  3. here Providing accounting and financial management assignment help is not a task that you should entrust to just anyone.

  4. The main aim of getting online education is to cover all skills usually and accept by the profession. If you’re a medical students then don’t worried about your assignment work. Our doctoral level writers each time available for your academic help with services and get authentic information in affordable price.more

  5. Training is a more helpful type of making an interpretation of complex data into more dependable data that can be access by anybody and with no uncertainty since questions are making things more odd to deal with.capstone services

  6. Sports board also uses education as the platform of sport. In education, sports base knowledge exists. We can write a report related to sports knowledge. Our writers have knowledge about the sport. spss service

  7. Is your business having issues because of the overflow of assorted web sites showing on the Internet? does one wish your website to be visible to clients? square measure you too busy to jot down a web log for your site? Then, this can for certain be the solution to any or all your problems. calorimetry lab report

  8. In the matured telecommunication markets, the service suppliers face stiff competition and their major challenge is client retention. Customers is maintained by correct management of overall client expertise and empowering them. reverse coding in spss

  9. Writing skills gives chance to put thoughts in to words to share with the society. Therefore, education is important for everyone to transfer and to understand the feeling, emotions, experience of each other.doctoral dissertation

  10. Inside the aged telecommunication areas, the particular program vendors confront inflexible opposition and also their particular key concern will be consumer maintenance. Consumers will be preserved simply by appropriate supervision regarding total consumer experience and also empowering these. Visit here

  11. It’s in reality a great and helpful piece of info. I’m satisfied that you simply shared this useful info with us. Please keep us up to date like this. Thank you for sharing.
    buy dissertation online

  12. This is beautiful place i really like her dress thank you so much. Sp flash tools Leapdroid Deezloader

  13. Very nice and interesting article. It`s always great to read about people who make all these useful things for us. Thank you. blockchain

  14. This is beautiful place i really like her dress thank you so much. Sp flash tools deezloader download