question

Vinaykumar-2539 avatar image
0 Votes"
Vinaykumar-2539 asked ·

SSAS optimal settings for processing and querying with OLAP site

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.

sql-server-analysis-serviceswindows-server-iis-configuration
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered ·

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.



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.