5

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.

Improve this question

1 Answer 1

Reset to default
5

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...? :-)

Improve this answer
7
  • @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
    Commented Jun 15, 2011 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
    Commented Jun 15, 2011 at 22:05
  • @gbn - I'll give that a try then take a look at the logs. Also, does the number of the ASYNC_NETWORK_IO waits seem high for 15 minutes?
    – Tony
    Commented Jun 16, 2011 at 14:08
  • 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
    Commented Jun 16, 2011 at 17:04
  • 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
    Commented Jun 16, 2011 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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