Monday, January 23, 2012

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.

image_thumb2

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.

image

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.

image

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.

17 comments:

  1. Este blog é uma representação exata de competências. Eu gosto da sua recomendação. Um grande conceito que reflete os pensamentos do escritor. Consultoria RH

    ReplyDelete
  2. The two main types available are the pharmaceutical pills which you need a prescription and the generic over-the-counter supplements you can readily buy. In this article, we’ll look into both erectile sex pills. These have various potency but all the same, works effectively to give you full erections https://www.penetric.com/best-male-enhancement-pills/

    ReplyDelete
  3. Hey!! Thanku you so much for sharing information about blog and also download Score hero mod Traffic rider mod apk Vector mod

    ReplyDelete
  4. I definitely enjoying every little bit of it. It is a great website and nice share. I want to thank you. Good job! You guys do a great blog, and have some great contents. Keep up the good work. OMC outdrive parts

    ReplyDelete
  5. Online medical research paper writing services are very difficult to complete and many students are always searching for Medical Assignment Writing Services to help them complete their medical coursework writing services and medical research paper services.

    ReplyDelete
  6. Thank you very much for giving us to express our feeling and thoughts about above information. I think you will keep updating and changing these information time to time if there is need to change. revenue assurance audit delhi, company registration in delhi online , top 10 ca company in India, read more, business advisory consulting services in india, top accounting companies in india.

    ReplyDelete
  7. Thanks for this blog. It really provides awesome information to all readers. keep it up and keep posting these types of blogs on digital marketing services, it's really helpful.
    fixed asset management companies in india

    ReplyDelete
  8. Through www.amazon.com/mytv - how you can connect your mobile phone to Amazon Prime. Through amazon.com/mytv, you can watch your favorite TV shows, series movies. You can watch prime videos anywhere on your device. Users need to create an Amazon account if they don’t have an Amazon account and enter the Amazon my TV activation code to watch Amazon prime videos on your device.

    amazon.com/mytv | www.amazon.com/mytv

    ReplyDelete
  9. Amazing and infromative post, checkout this as well:- online organic chemistry tutor

    ReplyDelete
  10. I am glad to see this brilliant post. all the details are very helpful and good for us, keep up to good work.I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    PLSQL Course in pune

    ReplyDelete
  11. Nice post. I learn something new and challenging on websites I stumbleupon on a daily basis.풀싸롱

    ReplyDelete
  12. I’ve recently started a site, the information you offer on this site has helped me greatly. Thanks for all of your time & work.

    Also visit my webpage - 우리카지노

    ReplyDelete
  13. Once you’ve found your perfect freelancer crm, most of the hard work is over. The next step is to familiarize yourself with this new business software tool, and set it up in a way that will help you accomplish important business objectives.A good CRM will help you automatically log and act on email conversations with clients and prospective clients.

    ReplyDelete
  14. Thanks for the article very informative informationyou share.keep it up.
    If the Quickbooks user cannot locate the taskbar.What are the possible causes of quickbooks error 15227
    Please see the following potential causes of error code 15227:-
    In the event that a QuickBooks download has been damaged or corrupt.
    QuickBooks installed partially or incompletely.

    ReplyDelete
  15. 추첨 방식은 로또와 비슷하나 확률은 그에 비해 극악인데 2012년 이전까지는 1~49 사이의 숫자 5개와 1~42 사이의 숫자 하나(이 숫자가 새겨진 볼 이름이 파워볼이다.)를 맞히면 1등을 할 수 있었으나 2012년 이후로는 1~69 사이의 숫자 5개, 1~26 사이의 파워볼 숫자 하나를 맞혀야 한다.

    ReplyDelete
  16. 스웨디시 마사지는 미국과 유럽에서 매우 인기가 있습니다. 스웨덴 웁살라에 있는 스웨덴 농업 과학 대학은 사람들이 그러한 마사지를 선호하는 이유와 방법을 알아내기 위해 연구를 수행해 왔습니다. 사람들이 집에서 마사지를 선호하는 진짜 이유는 받을 것을 선택할 수 있기 때문이라는 사실을 알고 놀랐습니다.

    ReplyDelete