sys.dm_db_task_space_usage – Day 10 – One DMV a Day

Hello Geeks and welcome to the Day 10 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday we have covered sys.dm_db_session_space_usage and today we are going to cover another index which gives similar details but in much deeper perspective. sys.dm_db_task_space_usage give you the same information as the first one but is different in the sense that this information is specific to each task using TempDB.

So how is a session different from a task? To answer that you need to understand how the execution of a request is handled by the database engine. To keep it simple, each session can run multiple batches each containing one or more queries. Depending on the query it can run in multiple threads or tasks better known as parallel execution.

Now we know what a task is let’s run sys.dm_db_task_space_usage and get the output details. To make it more understandable I will be joining this with another DMV sys.dm_os_tasks. There are few other OS level DMVs which I will be covering in later parts of this series which are very useful in troubleshooting scenarios.

SELECT t.task_address, 
	t.parent_task_address,
	tsu.session_id,
	tsu.request_id,
	t.exec_context_id,
	tsu.user_objects_alloc_page_count/128 AS Total_UserMB,
	(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)/128.0 AS Acive_UserMB,
	tsu.internal_objects_alloc_page_count/128 AS Total_IntMB,
	(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count)/128.0 AS Active_IntMB,
	t.task_state,
	t.scheduler_id,
	t.worker_address
FROM sys.dm_db_task_space_usage tsu
INNER JOIN sys.dm_os_tasks t
ON tsu.session_id = t.session_id
AND tsu.exec_context_id = t.exec_context_id
WHERE tsu.session_id > 50
ORDER BY tsu.session_id

I joined it with the sys.dm_os_tasks DMV to get few important columns like the parent task address, status of the task, scheduler on which the task is executing and worker which it is using to execute. So from the output of sys.dm_db_task_space_usage I have displayed only SPID 79 which is running in 16 parallel threads. The task with exec_context_id 15 is RUNNABLE and every other thread is SUSPENDED. The worker_id for each of these tasks will be unique and multiple workers can run on same schedulers. The parent_task_address for all the 16 threads is same. In simple words, the SPID 79 has started 16 threads (exec_context_id from 1-16) from the single parent thread (exec_context_id – 0)

You will know more about the columns worker_id, scheduler_id when I write the OS related DMVs. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook