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