none
Memory allocation issue RRS feed

  • Question

  • Hi Team,

    While inserting a data into a temp table in azure datawarehouse 

    (

    create #table

    ) ;

    I am getting the below error:

    Unable to allocate 256267 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 am running a job via an account which belongs to medium RC.

    So is there any way to remove this issue without scaling up the azure datawarehouse.

    And also does the ADW use columnstore index in temp #tables ? Can we update it to rowstore type?

    Friday, September 6, 2019 11:16 AM

Answers

  • Yes, You can and the sample code is below.

    Hope this is helpful!

    CREATE TABLE #myTable
      (  
        id int NOT NULL,  
        lastName varchar(20),  
        zipCode varchar(6)  
      )  
    WITH ( HEAP );

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by Nandan Hegde Monday, September 16, 2019 3:13 AM
    Thursday, September 12, 2019 10:20 AM

All replies

  • Hi 

    You can try creating a heap temporary table as below. Let me know if this works.

    CREATE TABLE myTable
      (  
        id int NOT NULL,  
        lastName varchar(20),  
        zipCode varchar(6)  
      )  
    WITH  
      (   
        DISTRIBUTION = REPLICATE,
        CLUSTERED INDEX (lastName)  
      );  


    Thanks Please mark as answer if my post is helped to solve your problem and vote as helpful if it helped so that forum users can benefit

    Saturday, September 7, 2019 12:10 PM
  • Hi

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue, please  mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. 

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, September 12, 2019 4:39 AM
  • Hi Naveen,

    The CTAS table utilises the database memory ,whereas #tables utilises temp memory.

    So is there any way by which we can update the #table index?


    • Edited by Nandan Hegde Thursday, September 12, 2019 7:41 AM grammer
    Thursday, September 12, 2019 7:41 AM
  • It is same in the case of temporary tables as well. Hope the below script helpful

    CREATE TABLE #myTable
      (  
        id int NOT NULL,  
        lastName varchar(20),  
        zipCode varchar(6)  
      )  
    WITH ( CLUSTERED INDEX (id) );

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, September 12, 2019 9:55 AM
  • Hi Naveen,

    Is it possible to use Heap in the creation of temp tables. That is, instead of Clustered index or ColumnStore Index can i use Heap.

    Thursday, September 12, 2019 10:14 AM
  • Yes, You can and the sample code is below.

    Hope this is helpful!

    CREATE TABLE #myTable
      (  
        id int NOT NULL,  
        lastName varchar(20),  
        zipCode varchar(6)  
      )  
    WITH ( HEAP );

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by Nandan Hegde Monday, September 16, 2019 3:13 AM
    Thursday, September 12, 2019 10:20 AM