Columnstore compression issue for a CTAS - Synapse SQL pool

Question

Hi,

in a DW100c Synapse dedicated SQL pool I've a hash distributed table with 260 columns and 2 millions of rows. For this table it was specified the clause CLUSTERED COLUMNSTORE INDEX.

To change the hash distribution column I've used a CREATE TABLE AS SELECT maintaining the above clause:
and I've obtained this error:
"Unable to allocate 260736 KB for columnstore compression because it exceeds the remaining memory from total allocated for current resource class and DWU. Please rerun query at a higher resource class, and also consider increasing DWU. See 'https://aka.ms/sqldw_columnstore_memory' for assistance."
So, I've tried to assign to my user (by calling sp_addrolemember) first the dynamic resource class xlargerc and then the static resource class staticrc80 but the CTAS still has gone in error. Obviously, I've
Despite to assign an high resource class when I've tried to monitor the CTAS statement by joining the sys.dm_pdw_exec_requests and sys.dm_pdw_exec_sessions DMVs I've seen the smallrc resource class for my user and not the xlargerc one or the staticrc80 one.

Any suggests to me in order to solve a such issue?
Do I need to increase the service level?

Or do I need to choose only some columns for the clustered columnstore index, if possible?

I need to change the hash distribution colums to proof the performance of a join maintaining the right columnstore index.

Answer 1

I think you need to increase the DWU as suggested in the error message, example of the query :

ALTER DATABASE [my-database-name] MODIFY (SERVICE_OBJECTIVE = 'DW300c')

Answer 2

@pmscorca ,

Thankyou for using Microsoft Q&A platform.

Alternatively, you can directly scale up from UI by dragging the slider to the desired DW units. Kindly checkout the below screenshots:

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou