SQL Server Performance

High ASYNC_NETWORK_IO in SQL 2005

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by AndersL, Dec 19, 2006.

  1. AndersL New Member

    Hi
    At 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
  2. joechang New Member

    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?
  3. AndersL New Member

    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?
  4. joechang New Member

    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
  5. satya Moderator

  6. alzdba Member

    Let's hope you ran db-maintenance after your migration.
    -- dbcc dbreindex (all objects)
    -- dbcc updateusage (0) with count_rows
    -- sp_updatestats
  7. AndersL New Member

    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.

Share This Page