TempDB requests capture

Question

TempDB requests capture

Achilies

SSCommitted

Points: 1695
More actions
May 22, 2013 at 3:52 pm

#273023

I am trying to capture all the requests hitting tempdb for a period of 24 hours. I tried to do it in sql server profiler and filter by by the databaseid and exclude all other rows, but not able to see the records hitting. While this is running this task is in suspended state in activity monitor.
It is sql server 2008 R2 enterprise edition. Please suggest how can i capture the requests hitting tempdb.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

You can use sys.dm_db_task_space_usage to see tempdb objects. But if you're looking for more general performance statistics of tempdb, you can use performance monitor, info here: http://technet.microsoft.com/en-US/library/cc966545.aspx

This query will return a plan for queries using TempDB

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, qp.query_plan

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

CROSS APPLY sys.dm_exec_query_plan(t2.plan_handle) qp

WHERE t1.session_id = t2.session_id

AND (t1.request_id = t2.request_id)

ORDER BY t1.task_alloc DESC

Achilies SSCommitted Points: 1695 More actions · Answer 2

How can we capture whatever hitting tempdb in profiler?

Andrew G SSChampion Points: 12809 More actions · Answer 3

It works if you select TSQL Replay template, then filter on Database Name tempdb, exclude rows without value

Achilies SSCommitted Points: 1695 More actions · Answer 4

Foxxo,

Can you please share the details how to select t-sql replay template?

I tried selecting show all events, show all columns->TSQL->select all the checkboxes in it. and filtered the tempdb excluding the other rows.

Can you suggest please?

Thanks.

ErrLog Ten Centuries Points: 1040 More actions · Answer 5

I am facing the same issue. DBCC checkdb is eating all the tempdb space, so tried to capture what exactly it is writing. No luck at all.

Andrew G SSChampion Points: 12809 More actions · Answer 6

There is a box to select the template under the "General" tab when you first open a New Trace.

However, that just sets the options of the "Events Selection" tab.

Under Objects, tick Created and Deleted and you'll see the temp tables if you are still filtering by database name = tempdb.

You wont be able to see the query which created the object if it was run in a different database, otherwise you'd have to filter by that database and have a filter on something like Textdata like '%#%'.