SQL Server Performance Forum – Threads Archive

Which user DB is utilizing my TempDB more?

Hi, I have 4-5 databases that include the production db on the production box. Intermittently my tempdb grows to larger size. I am sure my production db is not utilizing tempdb much. I want to find out which db/application using tempdb largely. I think I can run SQL profiler to capture queries that is run from each db. I would like to here suggestions from experts to find out the easy way to identify the culprit db. Any suggestions please. Thanks,
Bala

---

You can track space utilization with sys.dm_db_task_space_usage, refer to BOL for details.
use the new DMVs to analyze which Transact-SQL statements are the top consumers of tempdb space as described in Monitoring space in this paper. For example you can use the following query that joins the sys.dm_db_task_space_usage and sys.dm_exec_requests DMVs to find the currently active requests, their associated TSQL statement, and the corresponding query plan that is allocating most space resources in tempdb. You may be able to reduce tempdb space usage by rewriting the queries and/or the stored procedures, or by creating useful indexes. SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
FROM (Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
AND (t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC Full Articlehttp://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Raulie

---

]]>