data value distribution skew can ABSOLUTELY cause the optimizer to estimate rowcounts that will lead to a plan that mathematically is most efficient based on the estimate but which due...

1) are you getting partition elimination in the query? my guess is not - either your query isn't written to allow this or you are getting screwed by the...

1) cost threshold for parallelism of 5 is almost universally too low. I recommend 10-15 for oltp-type boxes and 25-40 for olap boxes, then monitor for cxpacket waits.

2) max...

1) you do NOT want /PAE on with /3GB.

2) why in the world is someone building a windows server 2003 box in this day and age?? it was...

1) try queries with OPTION (MAXDOP 1), just for posterity's sake

2) if this query can't possibly result in duplicates, use UNION ALL instead. that will avoid a SORT/DISTINCT operation:

SELECT itp.InvoiceKey,...

1) NEVER EVER put inserted/deleted values into variables (unless you are doing a cursor in the trigger, in which case you have other problems). :w00t: I guarantee you without...

WayneS (6/7/2011)

---

Ah, so you were. I heard it went pretty well.

As long as they have fairly...

WayneS (6/7/2011)

---

TheSQLGuru (6/7/2011)

---

I applaud you Wayne for thinking outside the box on this with a switch to an mTVF. I am sure you aware that the optimizer can no...

I applaud you Wayne for thinking outside the box on this with a switch to an mTVF. I am sure you aware that the optimizer can no integrage the...

WayneS (6/7/2011)

---

cgrammont (6/7/2011)

---

I have really nothing of significance to add. But I'd like to say "thank you" for going through this problem for everyone to see. The thought process, going...

dji (6/6/2011)

---

For anyone remotely interested!!

I bought a new laptop recently, intel i7 quadcore, 4gb ram. Loaded SQL 2008 R2 attached my db featured in this thread and ran this query.

Return...

I just spent a few minutes looking at one of your later plans. It is my belief that you have no hope of significant improvements based on your current...

I am at a loss. I have never heard of this happening other than for attach/create for attach rebuild log type scenarios.

sqldba_icon (6/6/2011)

---

Thanks everyone. I don't think it is parameter sniffing issue because it doesn't take any parameters at all. Mentioned below is the query where it occasionally hangs.

sELECT Row_number()...

Shark Energy (6/6/2011)

---

Yeah I must admit I was surprised.

The chain of errors reads -

- Device activation error. The physical file name 'E:\Program Files\MSSQL\Logs\reporting_log.LDF' may be incorrect.

- Attempting to rebuild primary...