I don't have time to build out some code, but I believe you can do this with a proper use of ROW_NUMBER, OVER, PARTITION BY, ORDER BY. See...

1) since you have no clustered index, you best check for forwarded records. See sys.dm_db_index_physical_stats on how to check for this. Note that you need to do DETAILED,...

Nicely written and detailed article. I like the mechanism too.

Guess I will be the first to ask about your hardware. Clearly it is insufficient, but I want to know just how much so. IMNSHO you can tune...

I would go with an indexing problem...

I routinely see clients put data into a temp table, index that temp table, then join that temp table to other things WITH NO FILTER and ONLY ONCE, which means...

I think everyone is focusing on the wrong thing here. I think it is the SSIS/DTS engine that needs the memory and that INCREASING sql server memory AND having...

Nice stuff. Did I miss the link where I can download the source code and/or compiled object??

do wait stats, file IO stall and blocking analysis while you are doing your select into. that will expose the real cause of the problem I suspect.

1) do not set max and min memory to same value.

2) 6GB likely isn't enough free for a 36GB box

3) max memory is only limiting BUFFER POOL memory. There...

Virtually everyone is STUNNED when I tell them about how few rows it takes (1-2% is common) before the SQL Server relational engine will switch from index seek/bookmark lookup plan...

From whence did you acquire this information Paul? Neat stuff!

I doubt this has anything to do with the way you put data into the temp table. It is all about TWO MASSIVE JOIN queries UNIONed together using the...

1) how much free space is in your database? If you are like almost every one of my clients then you are using autogrowth to manage your file size...

1) I really don't understand exactly what this means: "I check the processor in taskmanager pagefile is showing nearly above 80%". Can you give more detail?

2) have you...