topic Re: Memory configuration for Analysis Services / Vertipaq engine ? in Report Server

Memory configuration for Analysis Services / Vertipaq engine ?

slhermite — Thu, 07 Nov 2019 13:23:46 GMT

Hi,

My client is using Power BI Report Server (on prem). Users are complaining about slowness when they try to open reports, and failures when they upload pbix files to the server.

The ReportServer MSSQL database seems fine.

On the 2 VMs hosting the ReportServer portal, basic CPU & Memory & IO metrics are fine.

More precisely, less than 35% of the total RAM is used. But I can see the process msmdsrv.exe (analysis services vertipaq, right ?) has high values for perf counter Page/sec.

On SSAS stand alone, I know there are options in file msmdsrv.ini to tweak the memory policy.

Is this configuration file used when msmdsrv runs in Power BI context ? In other words, if I change settings in that file, will it effectively change the behavior of the msmdsrv process ?

Thanks for your help,

Re: Memory configuration for Analysis Services / Vertipaq engine ?

d_gosbell — Thu, 07 Nov 2019 22:13:59 GMT

There are details in the docs below on how to edit the msmdsrv.ini file for the Report Server SSAS instance

https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh-troubleshoot#configuring-analysis-services-settings-msmdsrvini

Also if you are running on a load balanced cluster you need to make sure you have followed all the steps in the following docs

https://docs.microsoft.com/en-us/sql/reporting-services/report-server/configure-a-report-server-on-a-network-load-balancing-cluster?view=sql-server-ver15

Take special care to check the step on the backconnectionhostname setting. The first thing PBIRS does in uploading a file is to cache the file locally on c:\temp so if you don't have the backconnectionhostname property set properly the request to copy the file from the local temp folder to the database can fail as it can get "load balanced" to the other node where the cached file does not exist.

Re: Memory configuration for Analysis Services / Vertipaq engine ?

slhermite — Fri, 08 Nov 2019 11:33:46 GMT

Thanks d_gosbell for your answer ! We are doing these NLB configuration checks right now, keep you posted.

By the way, do you know if this high paging rate of msmdsrv is a normal behavior ? Isn't it supposed to consume the available memory on the server instead of paging ? Let me precise that nothing else Power BI Report Server runs on these VMs, and RAM usage is less than 35%.

Based on https://docs.microsoft.com/en-us/analysis-services/server-properties/memory-properties I would like to try setting the paging policy to 0, but I am open to any advice here!

Here is the memory settings we have, default values I presume:

<Memory>
 <MemoryHeapType>-1</MemoryHeapType>
 <HeapTypeForObjects>-1</HeapTypeForObjects>
 <HardMemoryLimit>85</HardMemoryLimit>
 <TotalMemoryLimit>70</TotalMemoryLimit>
 <LowMemoryLimit>60</LowMemoryLimit>
 <VertiPaqMemoryLimit>0</VertiPaqMemoryLimit>
 <MidMemoryPrice>10</MidMemoryPrice>
 <HighMemoryPrice>1000</HighMemoryPrice>
 <VirtualMemoryLimit>80</VirtualMemoryLimit>
 <SessionMemoryLimit>50</SessionMemoryLimit>
 <MinimumAllocatedMemory>25</MinimumAllocatedMemory>
 <WaitCountIfHighMemory>10</WaitCountIfHighMemory>
 <DefaultPagesCountToReuse>2</DefaultPagesCountToReuse>
 <HandleIA64AlignmentFaults>0</HandleIA64AlignmentFaults>
 <PreAllocate>0</PreAllocate>
 <VertiPaqPagingPolicy>1</VertiPaqPagingPolicy>
 <PagePoolRestrictNumaNode>0</PagePoolRestrictNumaNode>
 <HeapTypeForObjectGroupCount>0</HeapTypeForObjectGroupCount>
 <UseCountersPerCPU>-1</UseCountersPerCPU>
</Memory>

Thanks again for your help,

Silvère

Re: Memory configuration for Analysis Services / Vertipaq engine ?

d_gosbell — Mon, 11 Nov 2019 01:35:49 GMT

slhermite wrote:

By the way, do you know if this high paging rate of msmdsrv is a normal behavior ? Isn't it supposed to consume the available memory on the server instead of paging ?

It's hard to say as I'm not sure which counters you are looking at or what the rate is. But in general I would not expect to see a lot of paging from the msmdsrv process unless it is under memory pressure which it does not sound like it is in your case.