long running query and tempdb

SQL Lion » SSIS

long running query and tempdb

(2 posts) (2 voices)

Started 1 year ago by infomod
Latest reply from kumarraj

Activate e-mail notification in your profile settings to get updates. [Help ?]

Tags:

infomod
Member

We have a long running query that is working on a main table that has ~ 1 Billion rows.
The query does a group by and creates aggregates along with calculations and also CONVERT statements...it has 4 joins with the other 3 tables having ~ 333 Million rows. Indexes seem ok, execution plan looks ok.

Box is a 64 bit 2 proc with 4 cores each and 24 GB or RAM. The large table is ~ 75 GB w/out indexes.

What we are seeing is heavy use of tempdb both reading and writing, tempdb has grown to .4 TB in size.

Question: does this as you would expect in terms of resource utilization and time (1 day running so far)? Any ideas or advice on what to look at in terms of efficiency and also how to tell how far along it is in the the process of crunching thru?

thank you.
Rick

Posted 1 year ago #
kumarraj
Member

Hi Rick,
Its obvious that it will utilize that much of resource while dealing with Billions of records. Moreover you know it also depends on the columns and the datatypes that is used for the final output.
My suggestion is to check for the below points:
1) Do aggregation calculation on integer columns only and if possible extract only ids in the final output and later on join with the master table to get other relevant information.
2) Do (NOLOCK) for each of the tables you are joining.
3) Check for indexes for joining columns, a "cover index" is mostly recommended if possible.

And regarding the tempdb size, .4 TB is not a surprising volume as it is joining Billions of records (joining wouldn't take much tempdb size) but aggregating and storing the aggregated value for millions of records will definitely take more space.

--Kumar

Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.