About myself and everyone else.
We all know about fancy DMV’s in SQL but aside from the pre-written script I don’t think people use it as much as they should to obtain their full value.
the view I’m talking about today it the sys.dm_os_wait_tasks
It’s an indicator for waiting tasks on the system currently executing. The wait times of this is also rolled up into the sys.dm_os_wait_stats. Note that wait stats also contains waits from other views.
I’ve used the view on our system to control the maintenance of indexes. When I start the work on an index I capture the @@spid. Then through the use of a trigger on a loggin table I enable a job that executes my monitoring code for the index. The monitoring code is responsible for accessing the waits table and look in the table if my current SPID is blocking a process. Since most of our traffic on the DB is web based we have a 30 second timeout. This means I have 30 seconds during which I can do maintenance on an index and the site can withstand it. Anything long and the page will time out if give a bad user experience.
I kick off the code in the screenshot to monitor the index maintenance, I have a bit of a window of 10 seconds which is used for catchup. This means as the web page times out at 30 seconds I will only allow 20 seconds for the index build. This way there blocked processes can resolve itself and have 10 seconds to catch up before it times out in most cases this is fine.
This is just one example of how you can use a DMV to help out with the odd DBA tasks.
Super fun! and no more waiting.