Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given a certain kind of wait, how do you find which queries are causing those waits safely on a production SQL 2008 R2 Server?

In this particular case I am wondering about async_network_io.

share|improve this question

2 Answers 2

up vote 23 down vote accepted

My favorite way to check these is to use Adam Machanic's excellent sp_WhoIsActive stored proc. Here's a video on how to use it and a link to download the code:

http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

The catch is that you have to run it periodically to check for results. If you want this data to be gathered for you periodically, check out Kendra Little's tutorial on capturing sp_WhoIsActive results to a table:

http://www.littlekendra.com/2011/02/01/whoisactive/

Finally, if you want something to fire whenever a query waits for async_network_io, you can use a new tool called Extended Events. It's like debug points inside the SQL Server engine where you can make magic happen. Frankly, it's a little painful to use right now in 2008.

share|improve this answer

'async_wait_io' is not a wait type. Possible ASYNC% wait types are:

  • ASYNC_IO_COMPLETION
  • ASYNC_NETWORK_IO
  • ASYNC_DISKPOOL_LOCK

A few good links for wait types:

My go to query for finding current waits:

SELECT  req.session_id
       ,blocking_session_id
       ,ses.host_name
       ,DB_NAME(req.database_id) AS DB_NAME
       ,ses.login_name
       ,req.status
       ,req.command
       ,req.start_time
       ,req.cpu_time
       ,req.total_elapsed_time / 1000.0 AS total_elapsed_time
       ,req.command
       ,req.wait_type
       ,sqltext.text
FROM    sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
JOIN    sys.dm_exec_sessions ses
        ON ses.session_id = req.session_id
WHERE req.wait_type IS NOT NULL
--WHERE req.wait_type = '?'
share|improve this answer
    
Sorry I meant async_network_io. –  Kyle Brandt Mar 4 '11 at 21:41
    
Other very good articles: Lose wait stats with Brent O and Buck Woody and Wait stats. They are very worthwhile! –  Marian Mar 4 '11 at 21:51
    
@Marian: Brent was showing me some stuff today but ran out of time before we got to this one, maybe that article will be just as good :-) –  Kyle Brandt Mar 4 '11 at 21:54
    
@Kyle you've got Brent to show you stuff and still ask us? :) You should chain him to the chair in your office.. dry his brain out.. and then let him go ;). And don't forget to let us know of the results! –  Marian Mar 4 '11 at 21:59
    
@Marian: Yup very lucky to have his help. The thing is though if I get an answer here then everyone else on the Internet gets the answer as well :-) –  Kyle Brandt Mar 4 '11 at 22:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.