SSAS optimal settings for processing and querying with OLAP site

Vinay kumar 1 Reputation point
2021-03-19T19:06:34.857+00:00

I have a cube server with 16 virtual core and 64 gb memory . the average usage of memory is around 45% and CPU usage is about 60% max. We have almost 10 cubes on this server.
We are observing whenever we process a cube(any client) the web application which communicates via olap site , queries get stuck . All dashboards for any client hang until we kill the cube processing or it is over.
We were experimenting with multiple setting but any guidance will very helpful as we have restless client on our head.
Tweaking ThreadPool\Query\Max thread from 0 to 160 did helped if cube is not running. Also if we process the cube with max parallel =8 or 16 we are able to query dashboard but slower than expected.
We also add following settting in msmdpump config file
<ConfigurationSettings>
<ServerName>localhost</ServerName>
<SessionTimeout>3600</SessionTimeout>
<ConnectionPoolSize>500</ConnectionPoolSize>
<MaxThreadPoolSize>160</MaxThreadPoolSize>
<MaxThreadsPerClient>60</MaxThreadsPerClient>
</ConfigurationSettings>

Could anyone please help what is going wrong here and how we can improve it.

Internet Information Services
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,242 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Darren Gosbell 1,466 Reputation points
    2021-03-21T21:59:20.47+00:00

    Also if we process the cube with max parallel =8 or 16 we are able to query dashboard but slower than expected.

    So there are a couple of potential issues, but this might be the source of your problem. Are you using a tabular or multi-dimensional model? I'm guessing by your description that maybe this is a multi-dimensional model.

    By default if you do not set the MaxParallel setting the server will run as much in parallel as possible in order to finish the processing operation as fast as possible which can consume most of the resources on the system not leaving anything for queries to run at the same time. On a 16 "cpu" server I would look at setting the MaxParallel to 4 or lower since most processing operations will often spin up a read and write thread, so this should leave 8 cores free to answer queries. In reality it's not quite as simple as this and since you are not seeing the CPU spiking to 100% the CPU might not be your bottleneck it could be the read/write speed of your storage system. But even if it is the storage system that is the bottleneck, reducing the parallelism is probably the best way to free up resources to allow queries to run during processing operations. This will slow down your processing, but it should allow queries to run without being too much slower than usual.

    0 comments