Analysis Services Query/Command using most memory

Hi, is it possible in the SentryOne Performance Analysis Dashboard e.g. Top Commands (or any other way) to find which Query/Command is/was using the most memory. We have a situation where the memory of one of our SSAS instances increases suddenly to the TotalMemoryLimit. Until recently the memory usage was stable and low but this has suddenly started happening.


avatar image By Ken 1 asked Oct 17 at 09:59 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

Steve Wright wrote a great blog series about using the BI Sentry product for SSAS. You are asking specifically about Memory, with is Part 5 of the series. This still does not provide you with a "Smoking Gun" for your memory issue, but you can start to correlate the usage on the PA Dashboard, highlight that time range, and us the JumpTo feature to narrow the time range on the Top Commands or SSAS Usage Totals tabs to find the offending query(s). The metrics on the Dashboard will help you determine if the bottleneck is occurring in the Storage Engine or Formula Engine, which will better help you pinpoint your root cause.

avatar image By SDyckes2 81 answered Oct 17 at 01:56 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

Thanks, it would be nice to be able to see the memory each query/command was using but I don't think SSAS exposes this.

avatar image By Ken 1 answered Oct 17 at 02:01 PM
more ▼
(comments are locked)
avatar image SDyckes2 Oct 17 at 02:35 PM

Because a SSAS query can impact both the formula and storage engine, the memory associated with the query may vary in the different engines. Additionally, we do not know how much memory a query utilizes, until the query completes and we pull the information from the trace running on a SQL Server. But, SSAS does not have the same information available in its "Trace".

avatar image Ken Oct 17 at 03:38 PM

Thanks for the info regards

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Oct 17 at 09:59 AM

Seen: 20 times

Last Updated: Oct 17 at 03:38 PM