Muller Jannie

About myself and everyone else.

waiting around

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.

So.


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.

Advertisements

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

Information

This entry was posted on April 4, 2012 by in SQL Server and tagged .
%d bloggers like this: