You posted over 150 lines of stuff in the original post. Forums are useful for helping out with targeted, fairly straight forward/simple problems and are 'worked' by people who...

colin.Leversuch-Roberts (2/4/2010)

---

I think that sometimes the desire to achieve a goal masks the requirement. I've seen many hours spent trying to replace cursor statements on pieces of code which run...

I didn't see it in my quick scan of the thread, but here is the Bible for deadlock resolution: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Note there are 2 more parts to that post.

1) Dynamic sql

2) better indexing

There are several DMVs specifically created for monitoring tempdb space AND the queries that are using said space.

See here in Books Online: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/148a5276-a8d5-49d2-8146-3c63d24c2144.htm

note the See Also Reference...

statistics are only updated 'automatically' when >20% of the values for that statistic are modified. that can take a LONG time for some objects, and the staleness of the...

Try this:

use tempdb

go

CREATE TABLE dbo.Data

(

row_id INTEGER IDENTITY PRIMARY KEY,

...

DOH! More goof - you need an index on the value joined or it can never do anything but a table scan/hash. Back in a moment with better...

AHA! Do this:

dbcc freeproccache

go

DECLARE @OneRow

TABLE (

-- row_id INTEGER IDENTITY PRIMARY KEY,

value...

Quite interesting Paul! I have seen numerous real-world systems where that was a killer. Things that make you go "HMMMMM"!!

The inability to have statistics on table var (except for PK if you have one) can be a killer even if there is just ONE row in the table var....

sam rebus (1/28/2010)

---

I need help from all the people familiar with inner workings of SQL 2005 memory managemnt. Educate me!

i have a few systems that exhibit similar strange memory usage...

If an "emergency" had been crushing my server for TEN DAYS I would long since have been on the phone to Microsoft Support or brought in a performance tuning consultant...

Thanks for sharing your work!

Steve Jones - Editor (2/3/2010)

---

That's an interesting thought on the partitioning. I would think it would be an issue. If you go to SSDs, I think you want to use...