• Another belated thought on this: If you're seeing a LOT of tempdb use, are connections using shapshot isolation (either explicit, or "read committed snapshot isolation" enabled on the databases)?

    That will pick up the load on tempdb pretty heavily in many cases, since that's where the row versions are stored.

    If so, tempdb's heavy I/O may have nothing to do with temp tables or table variables, and data on memory vs disk on those two may not apply in the slightest.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon