question

PaulHernandez-8067 avatar image
0 Votes"
PaulHernandez-8067 asked HimanshuSinha-MSFT answered

SQL DW Copy Command operation failed with error Unable to allocate 178149 KB for columnstore compression

Hi everyone,

I'm trying to load data from azure data lake v2 to azure sql dedicated pool.

The target table has the following definition for distribution and partition:

 WITH
 (
     DISTRIBUTION = ROUND_ROBIN,
     CLUSTERED COLUMNSTORE INDEX,
     PARTITION
     (
         [PARTITION_ID] RANGE LEFT FOR VALUES (1)
     )
 )

The table has around 320 columns, therefore I suspect the row size is playing a role here.

When the insert start using the copy command after a couple of minutes I get this error:

Operation on target CD_EDL_STG_DWH failed: ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL DW Copy Command operation failed with error 'Unable to allocate 178149 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.'

I have no idea how to tackle this. The only info I gathered was using the DBCC PDW_SHOWSPACEUSED command:
89995-image.png

Could someone help me to troubleshoot this?

I think I have enough DWU and is a design problem.


azure-synapse-analytics
image.png (53.8 KiB)
5 |1600 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.

HimanshuSinha-MSFT avatar image
1 Vote"
HimanshuSinha-MSFT answered

Hello @PaulHernandez-8067 ,

Thanks for the ask and using the Microsoft Q&A platform .
Can I suggest you to increase the DWU as suggested in the error message and see if that helps ? If you are facing the same issue after the upgrade also , please let us know .

Thanks
Himanshu

5 |1600 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.

PaulHernandez-8067 avatar image
0 Votes"
PaulHernandez-8067 answered

Hi @HimanshuSinha-MSFT ,

I checked the current dwu using this query and noticed it was very low (DW100c):

 SELECT  db.name [Database]
 ,        ds.edition [Edition]
 ,        ds.service_objective [Service Objective]
 FROM    sys.database_service_objectives   AS ds
 JOIN    sys.databases                     AS db ON ds.database_id = db.database_id

I increased the DWUcs using this query:

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

I monitored the usage of memory and now it works.







5 |1600 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.