SQL Server Performance Forum – Threads Archive
High ASYNC_NETWORK_IO in SQL 2005
HiAt a site the 120GB DB has moved from an old server (SQL 2000) to a new Blade Server with SQL Server 2005, 4 CPU, 4GB RAM and a SAN with lots of space. The database have been separated on several physical/logical disks. This should be a dream of performance improvement but it turn out the other way around. We measured CPU, RAM and IO capacity. CPU (avg 20-30%) and RAM looked good but one of the disks where running high, but expected. When measuring wait types , see
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx, the value ASYNC_NETWORK_IO was 10 times higher than NETWORK_IO in SQL Server 2000. This resulted in processes hanging for minutes, 10 minutes and even more. And of course blocks, deadlocks and so on. I realised something is wrong with the network or something similar but my knowledge stops there. When we checked the application server, who runs maybe 400 batch reports everyday against the DBserver, it gave strange results when running DOS-command netstat (netstat on DB-server gave maybe 2-3 ports with TIME_WAIT). Almost every port between 1024 and 5000 had status TIME_WAIT meaning it couldn’t connect to DBserver until a port became free. Now some registry values for Windows 2003 has been changed and it runs better but still ASYNC_NETWORK_IO is ten times higher than before. And lots of TIME_WAIT on application server. http://msdn.microsoft.com/library/d…/en-us/randz/protocol/tcp_time-wait_delay.asp
The values now are 30 seconds and maxports are set too 10,000. How can I improve/decrease the value of ASYNC_NETWORK_IO or TIME_WAIT? AndersL
i think it is a serious mistake to use a blade for db apps
blades just do not have the io bandwidth
who ever recommended it should be disqualified from this type of work blades are fine a web servers, which only need 2 network ports, but not serious storage io bandwidth
how is the storage connected to the blade, network or FC?
what is the blade model, and what is the network driver/chip?
usually default works fine
i take it this was not a problem on the old server?
Hi
Thanks for the response. The customer can’t really change server right now. How can the configuration be improved for this customer without replacing the BladeCenter? They’re using fibre to the storage. Appserver:
IBM BladeCenter HS20
NIC:Broadcom NetXtreme Gigabit Ethernet 8.48.0.0 DBServer:
IBM eServer BladeCenter HS40
NIC:Intel(R) PRO/1000 MB Server Connection 8.6.12.1 What’s best practice for this solution?
i am stunned to hear that ibm changed the network chip between comparable generation systems (?)
assuming network delays were not a problem before this system
and based on very limited information
i would ask if some one enabled large packets in on the network cards in these systems, if so, turn it off otherwise, run profiler, look for the queries that have high duration,
is the duration much larger than CPU,
is there a correlation between the Dur/CPU ratio, and the amount of data returned,
ie, some expensive queries may return only a small amount of data
I believe the ASYNC_NETWORK_IO problem is getting corrected in SP2 which is in CTP at present and you may test to schedule job that performs a DBCC FREEPROCCACHE command. Review information frohttp://msdn2.microsoft.com/en-us/library/ms174283.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Let’s hope you ran db-maintenance after your migration.
— dbcc dbreindex (all objects)
— dbcc updateusage (0) with count_rows
— sp_updatestats
All the DBCC commands mentioned above (plus some additional) and sp_updatestats have been runned. Update statitistics, check integrity and rebuild index when fragmentation is more than 3%, runs every day. What’s the problem with ASYNC_NETWORK_IO? I would like to know more and why DBCC FREEPROCACHE should run on a regulary basis.
]]>