Sunday, April 19, 2009

How to eat ASYNC_NETWORK_IO ?

Last time I was visiting our client the person showed me one process which was in suspended status for a long time. Using Adam's great script(http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx) I identified the SELECT statement which had ASYNC_NETWORK_IO wait type. MS said that this type occurs on network writes when the task is blocked behind the network and we need to verify that the client is processing data from SQL Server. The problem was that SELECT statement retrievs more than 50.000 rows , then the application manipulates (FOR ..NEXT loop) with that data and finally only 200 rows that a client had seen at the end. Well,thanks to SQL Server 2005 we changed the statement to accept a parameter for TOP clause and this wait type has gone. We have not seen a great improvment of the application as we did not expect it but it is another way to identify poor written queries.

Wednesday, April 1, 2009

MVP Award for 2009 year.

I've been given the MVP award for another year. I hope you have found this blog and my posts in public forums useful and that helped you out to resolve some problems.