common culprits for large tlog files:

1) FULL recovery mode but not doing ANY tlog backups

2) leaving long running tranactions open even in SIMPLE mode. then committed transactions cannot be...

Steve Jones - Editor (8/17/2010)

---

While you can hint for some types of joins, or indexes, I'm not sure you can control join order. The optimizer looks at statistics, data sizes,...

did you resolve your problem? parallelism could be result of missing index, which could improve performance a thousand times or more. You might also be getting too many...

1) trigger

2) blocking

3) unindex foreign keys

4) horrible tlog write performance

5) server being crushed by other activity

1) search web for track_waitstats_2005 to get a very good script to do wait stats analysis by time-spread analysis.

2) I disagree with previous poster that CXPACKET waits dont' indicate a...

I would like to put in a plug here for the amazing FREE tool SQLJobVis. Gives you a Gantt chart of job schedules/executions. VERY handy for detecting overlaps...

Nice stuff Tim - oh, and good to see you at SQL Saturday 28 in Baton Rouge this w/e!!

Another thing I have found to be important and telling...

You should also analyze wait stats and especially IO stalls to find out where the process is bottlenecking. tempdb IO performance issue here anyone? 😀

Here is the "bible" for deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Note there are 2 additional parts to this blog series.

Maybe it is that big because someone else KNEW it needed to be that big?? No...

The "spamming" you describe is from LINKED SERVER activity. Bad stuff there. I have had several clients get several orders of magnitude improvement from replicating out stuff that...

Microsoft is WAY behind the curve on this topic, and PDW will take at least another 18 months or so to be anywhere close to where it should be. ...

Paul White NZ (8/10/2010)

---

The 'cost threshold for parallelism' setting applies to the whole SQL Server instance.

The query optimiser will only consider looking for parallel plan alternatives if the best serial...

ejbatu1 (8/9/2010)

---

I had another question on this... is backing up the log file (overwrite mode) every two hours and backing up the DB once a day (at night) a good...

I still refer to numerous 2005 features as "new". When you have been using the product since 6.5 first...