Sql-server – tempdb usage in SQL Server

sql servertempdb

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.

Best Answer

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
Related Question