Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Tuesday, February 12, 2008

From Slava Ok's blog, but I'm so tired of seeing good information on blogs "disappear" when I need to reference back to it, so I've had to copy it to my own blog, Slava, thankyou, link to Original: http://blogs.msdn.com/slavao/archive/2006/11/14/sqlosdmv-s-continue.aspx 

 

One can run lots of interesting queries using this view.  You can even use this view to perform deadlock detection that is not resolvable by deadlock monitor, DM. For example if you have tasks waiting on external resources such as extended stored procedures and blocking others from running. This type of deadlock DM can’t detect but you can!

 

  1. Q. How many tasks are currently waiting? 

select count(*)

from sys.dm_os_waiting_tasks

 

This query will give you an idea of how many tasks are waiting in the system. You can use this information to understand blocking characteristics of your load

 

  1. Q. How many tasks that assigned to a worker (thread/fiber) are waiting?

 

select count(*)

from sys.dm_os_waiting_tasks

where wait_type <> 'THREADPOOL’

 

This query shows how many threads are actively running in the system. Latter on I will show how to find out if number of threads can be increased

 

  1. What are the tasks waiting on?

 select wait_type,

      count (*)

from sys.dm_os_waiting_tasks

group by wait_type

order by count (*) desc

 

One can use this query to investigate possible bottlenceks of an active load. This query groups tasks by wait type – it can’t be directly use to identify the actual bottlenecks on the system. The query gives you an idea about the wait characteristics of your load

 

  1. Q. Does my load have an active resource bottleneck?

You can answer this question by looking at the resource address that tasks are blocked on.  Keep in mind that not all wait types have resource associated with them.

 

select resource_address,

      count (*)

from sys.dm_os_waiting_tasks

WHERE resource_address <> 0

group by resource_address

order by count (*) desc

 

  1. Q: Is my system can be possibly bottlenecked on I/O?

You can answer this question by looking at the wait type of tasks waiting on specifically you are interested in IO waits

 

select *

from sys.dm_os_waiting_tasks

where wait_duration_ms > 20 AND

      wait_type LIKE '%PAGEIOLATCH%'

 

You might want to change 20ms base on your I/O subsystem

 

  1. Q: Does my load have long waiting chains?

 This information is particular interesting to understand if a single tasks, for example one that generated long I/O, blocks others. If this happens you will have a way to improve your scalability by figuring how to remove or minimize chain length.

 

WITH TaskChain (

waiting_task_address,

blocking_task_address,

ChainId,

Level)

AS

(

-- Anchor member definition: use self join so that we output

-- Only tasks that blocking others and remove dupliates

 SELECT DISTINCT

      A.waiting_task_address,

      A.blocking_task_address,

      A.waiting_task_address As ChainId,

    0 AS Level

FROM

      sys.dm_os_waiting_tasks as A

JOIN

      sys.dm_os_waiting_tasks as B

ON

      A.waiting_task_address = B.blocking_task_address

WHERE

      A.blocking_task_address IS NULL

UNION ALL

-- Recursive member definition: Get to the next level waiting

-- tasks

SELECT

      A.waiting_task_address,

      A.blocking_task_address,

      B.ChainId,

      Level + 1

from

      sys.dm_os_waiting_tasks AS A

JOIN

      TaskChain AS B

ON

      B.waiting_task_address = A.blocking_task_address

)

select

      waiting_task_address,

      blocking_task_address,

      ChainId,

      Level 

from

      TaskChain

order by

      ChainId

 

If there are no chains, your load is not CPU bound and you see long waits on THREADPOOL, you might improve your throughput by increasing a number of threads in the system.

 

Keep in mind that you can extend this query to perform your own deadlock detection.

 

You can also find out more information about each individual wait_type here

http://msdn2.microsoft.com/en-us/library/ms179984.aspx

Tuesday, February 12, 2008 9:46:41 AM (Central Standard Time, UTC-06:00) |  | #
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
Default Trace - Heavy load, turn it...
SQL 2008 R2 License / Cost = Open S...
Door Installation
Recent Posts
Archive
September, 2019 (1)
August, 2019 (1)
May, 2019 (1)
April, 2019 (1)
March, 2019 (1)
February, 2019 (1)
January, 2019 (1)
December, 2018 (1)
November, 2018 (3)
October, 2018 (1)
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll