One other thing: did you verify that there are no other batch jobs, maintenance jobs (such as backups or checkdbs), etc running between 2330 and 0330?

I agree with Jeff, 4 hrs to redo indexing on 65GB isn't unreasonable unless you have pretty good hardware.

I would check out IO stalls (fn_virtualfilestats). Also, how...

Actually there are MANY...

It is unfair to simply hate SANs. You should hate...

GilaMonster (11/4/2008)

---

What, like this one? Except of...

Jeff Moden (11/4/2008)

---

Then you should probably split it up... doing an update on something that large all at once will probably drive your code past the "tipping point" that every...

balbirsinghsodhi (11/4/2008)

---

SQLGURU.. so you are saying that I should hire a person in my company to troubleshoot the issue or you will help me by charging money. Is that...

Hmm, what we have here is a failure to communicate. 🙂

I said you need a PERSON, not a DOCUMENT, to help you get your systems working well and to...

balbirsinghsodhi (11/4/2008)

---

Wow, I put maxdop 4 and hash join and the query return in less than five minute. Guys you are great.

You really deserve for big award.

Guru is...

Just thought of something else. Change the select to a simple select count(*) and run the query to see how many rows hit for each section please. Maybe...

1) you will need parallelism for this monster. I would try 4 for max

2) try using a OPTION (HASH JOIN) hint for query and see how that goes. ...

Vegard Hagen (11/3/2008)

---

Michael Crider (5/1/2007)

---

This seems to happen when updating 2 very large tables (over 2 million)by joining them together. My process waits on a PAGEIOLATCH and goes to sleep...

This - ((@ClientName Is Null) Or (DN200.CLIENT_NAME = @ClientName)) - is BAD BAD BAD. 🙂 The optimizer just cannot do the right thing when you have that....

perf mon counters avg disk sec/read and avg disk sec/write for each individual drives should reveal such performance problems, as would examining the virtual files stats DMV (or fn_virtualfilestats, which...

See here for description: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/e4734b3b-8fbe-4b65-9c48-91b5a3dd18e1.htm

and here for setting: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9f38eba6-39b1-4f1d-ba24-ee4f7e2bc969.htm

NOTE: you can really cause unintended consequences if you mess with this setting, so be careful and watch what...