I think something is creating a temporary file on that drive and then it removes the file when it is done.

Perhaps the backup routine is writing the backups to this...

You could try this:

* Disable all indexes on productionaudit before performing the update, then reenable them after the update.

* Dont update all 20M rows in one single transaction - use...

The reason the SQL server is using a merge join for joining with ProductToZone is that the cost for reading 653191 rows with sequential IO from the index and then...

I agree with Lutz. The indexes suggested above will probably fix the problem.

GilaMonster (5/14/2010)

---

However a loop join to join 16 million rows to 20 million rows is insane. Loop joins work well if one of the tables has a small number of...

rossnruthie (5/13/2010)

---

We have 10 large 15 million+ row tables that all have 1 index(clustered) on a column called individual_id_number. These tables are never updated and never have rows inserted...

Please post the actual execution plans of both the fast and slow run.

OK, here are my opinions:

I like to keep things as simple as possible, so I would probably do it like this:

2 discs raid 1 for system+programs+paging

2 discs raid 1 for...

You could also load the data into an OLAP Cube and reprogram the application to query that instead.

A few points:

* The main reason this particular update is so slow is that you have a non-clustered index (IX_UserName) on the UserName column. The update would probably be much...

It looks like you are on the right track.

My guess is that one of the variables @tableHTML @newdb @backupHTML is NULL. Add some debugging print statements...

Paul, thanks for the explanation.

I thought this was what you where referring to, but I wasnt sure.

/SG

I think it would be a good idea to post the actual definition of Table1 including all its indexes.

Please do not edit the generated script in any way, just post...

ricardo_chicas (4/29/2010)

---

How many rows are in the table ?

A\ 70 million rows

How many rows does the query above really return ?

A\query returns about 70 thousand

What do your indexes look like...

Grant Fritchey (4/29/2010)

---

Logically the data is sorted in precise order, meaning that the index knows exactly which comes next in sequence. But the storage mechanism may have the data stored...