New server cube processing timing out because the query wont run parallelism, what do i do?

Question

So I am testing upgrading from 2017 to 2019 and one of the cubes keeps timing out after 1 hour.
I have tried changing any timeout options on SSAS from 60 to 180, yet it still fails.
The query plan on the previous server uses parallelism yet the new server wont.
I have changed the server parallelism cost down to 1 and maxdop to 4 and then to 0.
The only way to force the parallelism is to use querytraceon 8649, obviously this is not an option for processing the cube.

Does anybody have any idea why sqlserver has decided the best way is to process this really large query in single thread?
The server has 2 physical cores and 6 logical ones with 64GB RAM running sqlserver 2019 with the latest patch.

Answer 1

It would seem that the optimizer thinks that the serial plan is cheaper.

If you run in compat level 140, do you get the parallel plan? If so, do you have Query Store enabled on the database? If not, enable Query Store, and then use Query Store to force the parallel plan and change compat level to 150.

Answer 2

Hi @jonjoseph@gmail.com ,

Does anybody have any idea why sqlserver has decided the best way is to process this really large query in single thread?

The number of worker threads spawned for each task depends on:

1.Whether the request was eligible for parallelism as determined by the Query Optimizer.

2.What is the actual available degree of parallelism (DOP) in the system based on current load. This may differ from estimated DOP, which is based on the server configuration for max degree of parallelism (MAXDOP). For example, the server configuration for MAXDOP may be 8 but the available DOP at runtime can be only 2, which affects query performance. Please reference:thread-and-task-architecture-guide

You can test as next:

Set SQL Engine Server side:
USE dbname;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO

USE dbname ;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'max worker threads', 512 ;
GO
RECONFIGURE;
GO

Set SSAS Process side:
Test as next: Change Settings->Processing options->Parallel (Maximum parallel tasks) =4

More information:
configure-the-max-worker-threads-server-configuration-option,
parallel-processing-best-practices

BR,
Mia
If the reply is helped, please do “Accept Answer”.

Answer 3

Hi @jonjoseph@gmail.com ,

Is the reply helpful?

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

BR,
Mia