O tempdb é uma tabela que o servidor sql utiliza como rascunho em suas operações. Sempre que você dá uma pesquisa com order by, sort by, group by o sql pode trazer as informações da fonte de dados, copiar para o tempdb, fazer a opção solicitada na pesquisa (order, sort, etc.) e aí retorna o resultado obtido.
Portanto, assim como o log, o tempdb é estratégico para o bom desempenho do servidor. Em grandes servidores costumamos colocar cada item do banco de dados em um disco diferente ( dados, indices, log, tempdb ) e, se possível, em controladoras de disco diferentes porque conseguimos mandar as ordens para esses periféricos em paralelo aumentando a performance geral do processo.
Hoje muito tem melhorado os processos e o uso da memória como rascunho do sql tem se tornado mais comum mas ainda não há previsão da retirada do tempdb do sql server.
Note que o tempdb é criado sempre que o sql server sobe, entra em funcionamento. Literalmente é um rascunho dos processos do servidor.
Só costumamos mexer no tempdb quando temos um problema com ele, como espaço em disco.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\Arquivos de programas\Microsoft SQL Server\90\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\Arquivos de programas\Microsoft SQL Server\90\templog.ldf');
GO