Analysis Services Query/Command using most memory

Ken 2017-10-17 09:59:31

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.


SDyckes2 2017-10-17 13:56:46
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.
Ken 2017-10-17 14:01:55
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.
SDyckes2 2017-10-17 14:35:08
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".
Ken 2017-10-17 15:38:01
Thanks for the info