bangsql (2/21/2013)
2. no of physical cpu = no of tempdb files.
Temp tables and table variables are always in TempDB, they're both tables, they're both entered in the TempDB system tables, they're both allocated space in the TempDB database. They, however, may not be on disk. Allocated space in TempDB != written to the disk. They won't necessarily get written to disk unless there's memory pressure.
Sorts and hashs will spill to TempDB when they can't get a large enough memory grant.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability