I am facing issues in loading data between SQL DW Internal tables and I am trying to load just 50 records, thats there in my source - but the "Insert" statement is taking very long time and not executing succesfully [ It keeps running for more than 60 minutes ]
Few stats regarding this • Source table (say S1) has 50 records, 105 columns , Columnar Store, Round robin Distribution on DWU 100 [ DDL of this table given below ] • Target table (say T1) has been created with same 105 columns, Columnar Store, Round robin Distribution on DWU 100 • Select top 5 * from S1 works • Select * from S1 works • Insert into T1 as select * from S1 is running for long time with no response [ more than 60 minutes ] • Insert into T1 as select top 5 * from S1 , worked once and not working after that • Insert into T1 as select top 5 all_columns_listed from S1 , works always and executes in < 1 minute • Insert into T1 as select top 30 all_columns_listed from S1 , works always and executes in < 1 minute • Insert into T1 as select top 50 all_columns_listed from S1 , is running for more than 25 minutes
I am unable to understand, what might be happening in the background - when insert into T1 as select * from S1 is running;
Is something going wrong with DMS ? or is this because we have 105 columns ?
All above operations was tried by scaling up to DWU 200 - but still no luck.
All above operations was tried on altogether different database, still no luck.
Is there any thing else that can be checked on what is happening ? How to handle this ?
Also I have tried running below statement to see- if there are any other queries running actively - which might make my insert statement suspended or wait.. but I could see- only my query was actively running on the DB.. select * from "sys"."dm_pdw_exec_requests" where status='Running' order by submit_time desc
S1 DDL is given below
CREATE TABLE S1
(
col1 [uniqueidentifier] NOT NULL,
col2 nvarchar NULL,
col3 [uniqueidentifier] NULL,
col4 nvarchar NULL,
col5 nvarchar NULL,
col6 [decimal](26, 6) NULL,
col7 [decimal](26, 6) NULL,
col8 [decimal](26, 6) NULL,
col9 [decimal](26, 6) NULL,
col10 [decimal](27, 6) NULL,
col11 [decimal](27, 6) NULL,
col12 [decimal](26, 6) NULL,
col13 [decimal](25, 6) NULL,
col14 [decimal](25, 6) NULL,
col15 datetimeoffset NULL,
col16 nvarchar NULL,
col17 datetimeoffset NULL,
col18 [smallint] NULL,
col19 [decimal](25, 6) NULL,
col20 [decimal](25, 6) NULL,
col21 [decimal](26, 6) NULL,
col22 [decimal](26, 6) NULL,
col23 datetimeoffset NULL,
col24 [decimal](25, 6) NULL,
col25 [decimal](25, 6) NULL,
col26 [int] NULL,
col27 [decimal](25, 6) NULL,
col28 datetimeoffset NULL,
col29 [decimal](25, 6) NULL,
col30 [decimal](25, 6) NULL,
col31 datetimeoffset NULL,
col32 datetimeoffset NULL,
col33 datetimeoffset NULL,
col34 datetimeoffset NULL,
col35 datetimeoffset NULL,
col36 datetimeoffset NULL,
col37 [decimal](25, 6) NULL,
col38 [decimal](25, 6) NULL,
col39 datetimeoffset NULL,
col40 [int] NULL,
col41 nvarchar NULL,
col42 [smallint] NULL,
col43 [smallint] NULL,
col44 [decimal](25, 6) NULL,
col45 [decimal](25, 6) NULL,
col46 [decimal](25, 6) NULL,
col47 [decimal](25, 6) NULL,
col48 [decimal](25, 6) NULL,
col49 datetimeoffset NULL,
col50 [decimal](25, 6) NULL,
col51 [decimal](25, 6) NULL,
col52 [decimal](25, 6) NULL,
col53 [decimal](25, 6) NULL,
col54 [decimal](25, 6) NULL,
col55 [decimal](25, 6) NULL,
col56 datetimeoffset NULL,
col57 [decimal](25, 6) NULL,
col58 [decimal](25, 6) NULL,
col59 [decimal](25, 6) NULL,
col60 [decimal](25, 6) NULL,
col61 [decimal](25, 6) NULL,
col62 [decimal](25, 6) NULL,
col63 datetimeoffset NULL,
col64 [decimal](25, 6) NULL,
col65 [decimal](25, 6) NULL,
col66 [decimal](25, 6) NULL,
col67 [decimal](25, 6) NULL,
col68 [decimal](25, 6) NULL,
col69 [decimal](25, 6) NULL,
col70 datetimeoffset NULL,
col71 [decimal](25, 6) NULL,
col72 nvarchar NULL,
col73 nvarchar NULL,
col74 datetimeoffset NULL,
col75 datetimeoffset NULL,
col76 datetimeoffset NULL,
col77 datetimeoffset NULL,
col78 datetimeoffset NULL,
col79 nvarchar NULL,
col80 nvarchar NULL,
col81 nvarchar NULL,
col82 nvarchar NULL,
col83 nvarchar NULL,
col84 nvarchar NULL,
col85 nvarchar NULL,
col86 nvarchar NULL,
col87 nvarchar NULL,
col88 nvarchar NULL,
col89 [bit] NULL,
col90 nvarchar NULL,
col91 nvarchar NULL,
col92 datetimeoffset NULL,
col93 [decimal](25, 6) NULL,
col94 nvarchar NULL,
col95 nvarchar NULL,
col96 [decimal](25, 6) NULL,
col97 [decimal](25, 6) NULL,
col98 [decimal](25, 6) NULL,
col99 [decimal](25, 6) NULL,
col100 [decimal](25, 6) NULL,
col101 datetimeoffset NULL,
col102 nvarchar NULL,
col103 nvarchar NULL,
col104 nvarchar NULL,
col105 nvarchar NULL,
col106 nvarchar NULL,
col107 datetimeoffset NULL,
col108 datetimeoffset NULL,
col109 varchar NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN,
HEAP
)
