tempdb

Question

tempdb is very small (300mb), but is has 200 mb\min traffic

how to detect what operations (in what db) cause this operations ?

Answer 1

SELECT top 10 * 
FROM sys.dm_db_task_space_usage
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC

Hope that helps.

Answer 2

- You can use the following script to indentify if 
-- temporary objects are being cached or not in a stored 
-- procedure. It shows number of temporary objects created 
-- when you invoke a particular stored procedure ten times.

DECLARE @table_counter_before_test bigint;
SELECT @table_counter_before_test = cntr_value
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Temp Tables Creation Rate';

DECLARE @i int;
SELECT @i = 0;
WHILE (@i < 10)
BEGIN
  -- <execute your stored procedure>
   SELECT @i = @i+1;
END;

DECLARE @table_counter_after_test bigint;
SELECT @table_counter_after_test = cntr_value
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Temp Tables Creation Rate';

PRINT 'Temp tables created during the test: ' +
       CONVERT(varchar(100), @table_counter_after_test 
                             - @table_counter_before_test);

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 3

SELECT top 10 u.*, DB_NAME(r.database_id) AS 'Database_Name'
 FROM sys.dm_db_task_space_usage u
   INNER JOIN sys.dm_exec_requests r ON (u.session_id = r.session_id)
 ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC

Above includes 'in what db' answer too.

Hope that helps.

Answer 4

This script will give you the SPID related to heavy TEMPDB usage

SELECT tsu.session_id
, tsu.database_id
, SUM(user_objects_alloc_page_count)* 8 / 1024 as user_objects_alloc_mb
, SUM(user_objects_dealloc_page_count)* 8 / 1024  as user_objects_dealloc_mb
, SUM(internal_objects_alloc_page_count)* 8 / 1024  as internal_objects_alloc_mb
, SUM(internal_objects_dealloc_page_count)* 8 / 1024  as internal_objects_dealloc_mb
--, SUM(user_objects_alloc_page_count) as user_objects_alloc_page_count
--, SUM(user_objects_dealloc_page_count) as user_objects_dealloc_page_count
--, SUM(internal_objects_alloc_page_count) as internal_objects_alloc_page_count
--, SUM(internal_objects_dealloc_page_count) as internal_objects_dealloc_page_count
, er.plan_handle AS [Plan]
, est.text AS [Text]
FROM sys.dm_db_task_space_usage tsu
JOIN sys.dm_exec_requests er ON er.session_id = tsu.session_id
CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) est
GROUP BY tsu.session_id, tsu.database_id, er.plan_handle, est.text
ORDER BY SUM(internal_objects_alloc_page_count) desc

Hope this helps

---

Geert Vanhove

Answer 5

evernite, is your query resolved? If yes then please click resolve else let us know if you would require further information.