tempdb usage in SQL Server

Question

I have a SELECT INTO statement writing to a database which is in SIMPLE recovery mode, and is not tempdb. Will this use tempdb at all?

The SQL is something like

USE TGTDB
GO

SELECT * INTO DestinationTable FROM SRCDB.dbo.SourceTable
GO

Will this use tempdb at all? We are running into contention issues.

Answer 1

First, run your query and make a note of your SESSION_ID.

Then, run these two queries to see how much TempDB space your original query is using. Be sure to update them with your SESSION_ID.

-- TempDB session usage
select * 
from sys.dm_db_session_space_usage
where session_id = 123
go

-- TempDB task usage
select *
from sys.dm_db_task_space_usage t
where session_id = 123
go

Also, run this query to show us the layout of your TempDB. Please post the results.

use tempdb
go
exec sp_helpfile
go

Answer 2

Tembdb is used to store temporary objects (cursors, tables, etc.) and as a work area to spool and sort records in certain situations.

I would have expected there to be no tempdb usage for the simple query in your example (unless the source table is actually a view).

Can you elaborate on the "contention issues" you're experiencing?

Answer 3

I suppose it could since it will use tempdb sometimes for internal objects to hold intermediate results.

I would suggest that you go to Books online and search for tempdb and then read the section on troubleshooting. It has some suggestions and queries for how to find out what is causing your tempdb problem.