I had a case the other day where the customer called and told me that he had a bit of a performance problem. Well, I’ve heard that a few times over the years, and asked him to elaborate. He sent me a screenshot of SolarWinds Database Performance Analyzer and, well, it kind of points the finger to one thing:

Um. Yeah. The ASYNC_NETWORK_IO thing. It kind of … sticks out.

Okay, so how to tackle this then? We know what waits are happening, but where do we go from here? Well, It’s time to find out exactly what is generating said waits. And this is where extended events come into play. Again. (Extended events are the coolest thing since sliced bread, there, I’ve said it.)

There are a few scripts I use when doing this and they were written by the venerable John Sterrett (see his blog post from 2013 here). There are a few small gotchas though - ASYNC_NETWORK_IO does not exist as an event ID. It is called NETWORK_IO. Hence the statements for capturing this specific workload looks like this:

DECLARE @WaitTypeTVP AS WaitType;
INSERT INTO @WaitTypeTVP (Name)
VALUES (‘PAGEIOLATCH_EX’), (‘NETWORK_IO’)
EXEC Waits.CaptureStatementsCausingWaits @TVP = @WaitTypeTVP;

Then you settle back and wait for the troublesome queries to crawl out of the woodwork. Little do they know that we’ve set a trap.

Upon emptying the trap after a few minutes, it became apparent that the culprit was one huge, automatically generated(meh) query. Time to get the execution plans for them using sys.dm_exec_query_plan. Trouble is (and this is the second gotcha of the scripts), said TVF requires the plan_handle, not the sql_handle. So we have to do a little digging around to get that - for instance taking a peek in sys.dm_exec_query_stats (which might hold multiple plan_handles per sql_handle, mind you!). Plug said plan_handle into sys.dm_exec_query_plan and hey, presto! The execution plan in all its gory glory.

It was apparent that the queries were able to use neither indexes (hello automatic code generation!) nor had a very good where statement (why don’t we take ALL of our several million rows and send them to the client, yes?). And there you have the problem - bad code that tries to send too much data to the client. Bad code, no cookies for you.

I provided the customer with my findings and he went on his merry way, grumbling and heading for the developers.