sys.dm_os_tasks – Day 13 – One DMV a Day

Hello Geeks and welcome to the Day 13 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.

So far we have covered the simple, straight forward but important DMVs. I will start with the main stream and very useful OS related DMVs today. These DMVs help in troubleshooting many performance related problems. I will start with sys.dm_os_tasks which I have already introduced while talking about sys.dm_db_task_space_usage.

Sys.dm_os_tasks exposes some specific task details which connects the dots between other DMVs. The parent_task_address, scheduler_id, worker_address, session_id, request_id are few of them. Before we go run this DMV and show how the output looks like let’s understand few basics.

When a query is run in a session it gets broken into smaller units called tasks. It can be due to parallelism or different tasks. The task which has some work to do goes into the work queue. A worker is assigned to it from the worker pool which is needed to perform the work. Once the worker is assigned and all resources are allocated for its run it moves to Runnable queue. Here it waits till a scheduler is free and runs on the scheduler in running state. The task is associated with the worker till it’s complete.

SQL Server OS works on cooperative and non-preemptive scheduling. That means a task should yield after its work is done. If it does not yield due to any reason you will see high wait times on SUSPENDED state and wait type would be SOS_SCHEDUER_YIELD. This scenario in few cases leads to non-yielding scheduler dumps. This is how your query gets processed at a very high level.

Sys.dm_os_tasks while run by itself has more irrelevant information. As is the case with many other OS related DMVs which I am going to cover in next 4-5 days. I will run simple queries to show the important columns of these DMVs individually. After covering all of them I will put in few queries which use combination of these DMVs for ease of the flow when reading the DMV series. 🙂

The simple query on sys.dm_os_tasks will be as below. I had to make the column names smaller to fit the image. But you can map them with the names from the query.

SELECT task_address,
	task_state,
	context_switches_count AS switches,
	pending_io_count AS ioPending,
	pending_io_byte_count AS ioBytes,
	pending_io_byte_average AS ioBytesAvg,
	scheduler_id,
	session_id,
	exec_context_id,
	request_id,
	worker_address,
	parent_task_address
FROM sys.dm_os_tasks
WHERE session_id > 50

If you observe the output you will fine session 56 has done a lot of context switching without doing any IO. I was running the below infinite loop to show this output.

WHILE (1 = 1)
BEGIN
	SELECT * FROM sys.sysprocesses WHERE 1=0
END

So both my sessions 55 and 56 are associated to different workers and are running on same scheduler. Whenever you see this output you will see 55 is in RUNNING state and the other session 56 is in SUSPENDED state.

Tomorrow I will talk about another OS related DMV. 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