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
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 (https://blog.sqlauthority.com)
15 Comments. Leave new
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.
Have you tried using a Linked Server?
Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “(null)”.
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?
The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached.
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……..
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
my results are blank when I run this (0 row(s) affected)
I have the same problem. Blank result.
why a running query also has wait_type ??
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.
I think you don’t have any workload running on the server. That is the reason you are getting just this query.
Hi Pinal,
When I am Executing This Query Some TIme Iam Not getting All The Queries Running in my Server.If It Is giving the Result It just pointing out the only Particular Database
Why It is not showing all the databases
Can you let me know if you tried to run any workload on some other database and see the output? This will return for all databases because we have no filters attached in the above query.
I’m like a year too late but i think this indicates you have no waiting tasks