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

sys.dm_os_tasks

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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *