Run script in tempdb . Can be used to troubleshoot tempdb space usage problems usually associated with large queries that use a lart amount of space for work tables and work files.
USE tempdb
Go
SELECT t.text,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage st
JOIN sys.sysprocesses sp
ON sp.spid = st.session_id
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) t
GROUP BY t.text
ORDER BY 2 DESC 
Have you ever checked the size of the SQL Server tempdb after restarting SQL Server to find that it's reset? Simon Liew explains this behaviour.
2017-05-31
3,326 reads
Sergey Gigoyan looks at how to address the warning: Operator used tempdb to spill data during execution with spill level 1.
2016-01-22
3,958 reads
References and links to help you learn how to create multiple tempdb files.
2015-06-29
3,045 reads
With the introduction of SQL Server 2012 Microsoft officially supports local disk TempDB in SQL Server cluster configurations. This tip will show you how to configure TempDB on a local disk when installing your SQL Server 2012 cluster.
2012-12-20
2,993 reads
One of the most obvious bottlenecks in the performance of tempdb is caused by PAGELATCH, in-memory latch contention on the allocation bitmap of each data file used. We can use one of the rules-of-thumb to choose what should be roughly the best number of files, but how then do you check to see whether you've got it right for your data and workload?
2012-08-07
2,869 reads