Unknown SQL Server Query holds back datawarehouse load -

we experience problems our automated loading of data our data warehouse. loading scheduled via sql server agent jobs lately noticed weird queries holding further loading.

example query:

select distinct     sum(dbo.fac_witness_queue_actuals.aabandons)     dbo.dim_division  inner join      dbo.fac_witness_queue_actuals on (dbo.fac_witness_queue_actuals.division_sk = dbo.dim_division.division_sk) 

notice there no where clause means query loads long time (more hour). when kill process find query same structure column in sum field.

there no indexes on tables mentioned in from (also no primary key). process started user ssis (which used starting jobs no job running using kind of queries).

anyone suggestions in direction look?

thx in advance

first not sure key distinct useful here :-)

when sum or count on large table, sql locks writing entire table make sure not changed whilst calculating output. default, no insert or update can performed during time.

there ways improve query or make faster need sort of time stamp column index or add autoinc bigint field column can sum new rows every day , add results day before <= favourite way of doing it!

you @ various locking options: http://technet.microsoft.com/en-us/library/cc546505.aspx perhaps 1 of them ok need?

finally, join might big (meaning big!) , perhaps break down using cursor or such dreadful thing , add waitfor delay loop through values let sql breath bit... or add memory box if possible?