SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type – Day 4 of 28

Previously, we covered the DMV sys.dm_os_wait_stats, and also saw how it can be useful to identify the major resource bottleneck. However, at the same time, we discussed that this is only useful when we are looking at an instance-level picture. Quite often we want to know about the processes going in our server at the given instant. Here is the query for the same.

This DMV is written taking the following into consideration: we want to analyze the queries that are currently running or which have recently ran and their plan is still in the cache.

SELECT dm_ws.wait_duration_ms,
dm_ws.wait_type,
dm_es.status,
dm_t.TEXT,
dm_qp.query_plan,
dm_ws.session_ID,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads,
dm_es.total_elapsed_time,
dm_es.program_name,
DB_NAME(dm_r.database_id) DatabaseName,
-- Optional columns
dm_ws.blocking_session_id,
dm_r.wait_resource,
dm_es.login_name,
dm_r.command,
dm_r.last_wait_type
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
GO

You can change CROSS APPLY to OUTER APPLY if you want to see all the details which are omitted because of the plan cache.

Let us analyze the result of the above query and see how it can be helpful to identify the query and the kind of wait type it creates.

Click to Enlarage
Click to Enlarage

The above query will return various columns. There are various columns that provide very important details. e.g.

wait_duration_ms – it indicates current wait for the query that executes at that point of time.

wait_type – it indicates the current wait type for the query

text – indicates the query text

query_plan – when clicked on the same, it will display the query plans

There are many other important information like CPU_time, memory_usage, and logical_reads, which can be read from the query as well.

In future posts on this series, we will see how once identified wait type we can attempt to reduce the same.

Read all the post in the Wait Types and Queue series.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

12 thoughts on “SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type – Day 4 of 28

  1. i have a question.your blog is very usefull to me.how can i import from foxpro (dbf)to sql server 2008 with openrowset ?
    i know how to import from excel,but from dbf no.thanks a lot dave.

    Like

  2. I have used your blog for many issues and solutions, so many thanks!

    However, this is one I cannot seem to find anything in regards to it.

    We are trying to figure out why we have a procedure, same object_id, same database_id that is listed twice in this view sys.dm_exec_procedure_stats. I know it is possible to have the same procedure name listed, but everything tells me that this must have a different object_id. In our situation we have the same Object_Id and Database_Id for this one procedure.

    When this situation happens the procedure run times are extremely long, most timeout after several minutes. Normal run times are sub-second for this procedure.

    We have tried rebuilding indexes, but that had no effect on this issue.

    Only way we have resolved this is to drop and create the procedure.

    Anyone advise you can offer on this weird situation?

    Like

  3. Hello Pinal,

    My server has 4CPUs 32bit sql server 2008 OS:windows 2008.
    I hav been facing a problem maximum CPU utilization by sql server(90 to 100%)there is only one database that is extensively used and only one application.But still the cpu utilization suddenly increases to 100% and the application gets hanged and we have to sometimes restart the server.Have enabled the Awe settin which was not there before and also monitored the server it shows high wait times for CXPACKET,ASYNC_NW_IO,LATCH_EX,SOS_SCHEDULAR_YEILD,
    Hence i changed the MDOP which was 0 to 3 and it worked fine for a week but now again it is showing huge wait time for SOS_Schedular_yeild.
    Also have to mention that there is only one drive i.e D: drive on the server. I am new to the performance issues hence unable to decide on whether we can use performance monitor to detect this as not sure of the counters to be used.
    Its has 8GB memory.Also checked for blocking and updated the ststistics and also reindexed the tables .
    Please assist in the same……..

    Like

  4. Hi Pinal,

    Thank you very much for detailed explanation on wait times.
    Could you please provide me some detailed info on exec_context_id column in sys.dm_os_waiting_tasks?

    For a session_id I have different exec_context_ids and they were getting blocked by these context_ids.

    For example

    session_id exec_context_id wait_type blocking_session_id blocking_exec_context_id
    75 4 CXPACKET 75 11
    75 8 PAGEIOLATCH_EX NULL NULL
    75 9 CXPACKET 75 11
    75 9 CXPACKET 75 11
    75 12 CXPACKET 75 11
    75 12 PAGEIOLATCH_EX NULL NULL
    75 0 PAGEIOLATCH_EX NULL NULL
    75 3 CXPACKET 75 11
    75 7 PAGEIOLATCH_EX NULL NULL
    75 10 PAGEIOLATCH_EX NULL NULL
    75 10 CXPACKET 75 11
    75 11 CXPACKET 75 11
    75 11 CXPACKET 75 8

    Could you please check above details and suggest me whether there is any problem with wait times or not.

    Thanks,
    Siva Prasad

    Like

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority

  6. when running this query on our servers, it only returns the query itself. there is nothing else shown. we are using a sql server std 2005 edition system.

    Like

  7. Pingback: SQL SERVER – 28 Links for Learning SQL Wait Stats from Beginning | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s