single select statement Running Slow due to wait times

Question

Hi,

I will explain my problem briefly.

Let us assume that my SQL server box is A and I am using SQL server 2012,desktop and connected to Ethernet. Queries run fast on this.

I am the admin to another server instance which is B. So I used to connect to server B on my machine and writes the queries. Previously it was good and returns 11lakh records in 5secs. But now I got a problem that it takes 10min time to run that.

When I used to connect to B using remote desktop connection from my box and wrote queries on SSMS and it works fine there. I thought that its a network problem so I gave that server B access to some one in my office and tried on their laptops through Wi-Fi and again same problem.

Yesterday night in my home I checked it and its works fine(that's our local Wi-Fi ). 

I am getting Async Network IO wait type. I checked the NIC settings and its 100MBPS full duplex.

Anyone could please drive me to the exact solution. I appreciated your solutions.

Thanks

Answer 1

Hello,

Async_Netowrk_IO means that it is waiting for the client to accept the data. This is either caused by a connecting with a very large latency factor or the client isn't processing the results fast enough. you'll have to look into both of those items as they are both outside of SQL Server. If nothing can be done, I would suggest writing your queries to return the minimum amount of data needed (as in don't use select *, make sure there is a where clause, etc) so that the client won't be bogged down processing and the latency factor can be minimized.

-Sean

---

Sean Gallardy, MCC | Blog | Twitter

Answer 2

Thanks for the reply Sean.

1) When I am connecting from my home I am able to get the data quickly. what does this means??

2) I gave access to my friend and he is also having the same problem in his machine through Wi-Fi Network.

My understanding from above is when I am in Corp net queries are running slow and in my home I am out of corpnet and getting there. What do you think??

Thanks

Raaz 

Answer 3

Can anyone help me please...please drive me to the nearest solution...

Answer 4

Hello Raaz,

If I understand you correctly, when you're ont he local network everything is fine but if you access it over the internet it's slow?

Unfortunately you'll have to do some work and digging to figure out what the root cause of the issue is, there is no single stop for every issue that involves this wait type.

-Sean

---

Sean Gallardy, MCC | Blog | Twitter

Answer 5

Please read the below link: Please let us know what are you trying to do connecting the servers.

http://beyondrelational.com/modules/24/syndicated/509/posts/12828/study-of-waitstatspart-5-asyncnetworkio.aspx

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 6

Ok Here is the problem.

When I am connecting to the server in office which is in domain network the queries are running slow and the query is normal select statement which contains 5000 rows and it takes 15secs for it. I saw the client statistics and client processing time is 14013 and wait time on server is 375. Everyone in my office had the same problem with that server. After connecting to that server when I am trying to write a query usually after writing                "select * from t " we used to get the table names in that database starting with t but I am not seeing any table names there.

when I used to connect to the server from home it takes 0 seconds to pull the data and I am seeing the table names while writing the query. 

I am able to pull the data from other servers in office.

Thanks