SQL Server 2008 ASYNC_NETWORK_IO

I have a web app that queries a database.  When a certain function of the site is accessed, the user is experiencing 30-45 seconds of timeout before data is displayed from the database.  I have the WhoIsActive stored procedure on the DB.  When I execute the stored procedure while the website is timing out, I see the ASYNC_NETWORK_IO wait.

 If I take the exact query that is being executed, and run it directly on the DB server, the query executes in 1-2 seconds.  

The app and DB server is hosted at a 3rd party provider, but I brought the DB to a local dev box and am not seeing the see results.  The data is displaying on the website in 2-3 seconds as expected.

What can I check on either the app or db server as this appears to be network related?


Any help is greatly appreciated.
sciggsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Quote:
"ASYNC_NETWORK_IO
This is usually where SQL Server is waiting for a client to finish consuming data. It could be that the client has asked for a very large amount of data or just that it’s consuming it reeeeeally slowly because of poor programming – I rarely see this being a network issue. Clients often process one row at a time – called RBAR or Row-By-Agonizing-Row – instead of caching the data on the client and acknowledging to SQL Server immediately."

from: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

is it a large result set, or is the client machine using it's CPU at a 100%. Then it's most likely on the client side of the application. Remember in a 3 tier application, the client = server asking Querys to SQL Server.

Regards Marten
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sciggsAuthor Commented:
The query is returning just under 8000 records, so it's not too terribly large in my opinion.  Watching CPU and RAM usage while the particular query is run, resources remain fairly low (under 40% utilization) and there are not many large spikes.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Those 8000 records. are there by chance any nVarchar, Binary or nText columns returning?

Run a profiler, make sure it includes the showplan (i e excutionplan).
run the app. Now run the Query from SSMS and include actual executionplan.

Compare the two. Are they the same, then the Waits are outside of SQL.
Is memory configured correctly on the sql, so that it doesnt starve the OS.
Remember it's the package, OS + SQL that delivers to the clients.

Those 40% utilization, did you check per processor/core. i e 40% could be one CPU at 80% on a dual core, or on a quadcore, one CPU at 100% = 25% utilization.

Regards Marten
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
:sciggs
Got any updates, answers to my questions, or information regarding status, progress?

Regards Marten
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
sciggs, do you still need help on this question?
0
ZberteocCommented:
One thing you can do is in the app change the connection property to timeout after, let's say, 1 min, 60 secs. The default value for a connection is 30. In this case could be that sometimes the network is overloaded with something, could be your result set or something else, which can delay the retrieval of the result set to go over 30secs.
0
Anthony PerkinsCommented:
Zberteoc,

I think you are confusing ConnectionTimeout with CommandTimeout.  Also, with ADO.NET the default ConnectionTimeout is 15 seconds, The default for CommandTimeout is 30 seconds.  CommandTimeout has to be set in code.
0
ZberteocCommented:
Correct, the command timeout for the connection setup in application. You can change the default for that to be at least 1 min and see if that solves the problem.
0
sciggsAuthor Commented:
We added additional RAM to the web application and that has resolved (or at least masked) the problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.