Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a SQL Server 2005 server that is serving up 20 websites, one of which is our custom CMS, and a few other micro sites. I am new to this and just started looking at the databases here.

The problem is that we are having issues with the SQL Server slowing down after a few days which causes us to restart the service. I did some stats with the SQL Profiler but after review I didn't see anything that stuck out. This is why I am here now. I looked around online and came across ASYNC_NETWORK_IO. So, I am trying to follow this track now.

This is what I've done:

I cleared out the wait stats table.

DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR')

Results for the last 15 minutes:

ASYNC_NETWORK_IO    46798     34953     812     1953
CMEMTHREAD          28300     42328     1187    6437
CXPACKET            361571    7048515   38984   48859

From what I've read ASYNC_NETWORK_IO has to do with SQL waiting for the client application.

2 questions:

1) Is my assumption correct that these numbers seem high for 15 minutes of monitoring?

2) Where do I go from here? What should be my next step?

I'm trying to learn how this stuff works and I understand this is something I'm going to have to keep digging in to.

A push in the right direction would be great.

share|improve this question
add comment

1 Answer

Reboot the offending client. Simple.

ASYNC_NETWORK_IO causes the server to slow down because the client can't consume data quick enough: the network buffers fill up.

FYI: CXPACKET is expected with parallelism. Not sure of current state but IIRC excess of there can be caused by hyperthreading.

Have you rebooted the client yet...? :-)

share|improve this answer
    
@gbn - I'm a bit new to this. Since this are web sites what would the client be? If this is a web site that is the offender then would tracking down the application code and processes be next? –  Tony Jun 15 '11 at 21:45
    
By client I mean web servers, sorry. The error isn't your app on there, but an os /driver error. A reboot is needed –  gbn Jun 15 '11 at 22:05
1  
Just throwing this out there... If I have an web page that that is bringing back all data from a table - just say 4Gb of data (something big) and the end user is running a slow machine and has to wait for this data would that bottleneck be noticed in ASYNC_NETWORK_IO?? There are a few web pages that are poorly written and are on the list to get fixed. –  Tony Jun 16 '11 at 17:04
1  
@Tony: most likely yes. That's a shit load of data. –  gbn Jun 16 '11 at 17:06
2  
page load on a user's PC is different to the web server reading data from the DB server. The web server is causing the issue most likely. Do you have some, say, Excel users loading too much data? From sys.sysprocesses you'll be able to see which client is causing it. –  gbn Jun 16 '11 at 17:37
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.