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
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:
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:
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.
'async_wait_io' is not a wait type. Possible ASYNC% wait types are:
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 = '?'