SQL Server Performance

Async_network_io problem

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by kpsledge, May 7, 2007.

  1. kpsledge New Member

    Hi all. I have come across a problem that I can't answer or find a reason for. We recently migrated to a active/passive cluster and added a storage array to our SQL 2k5 database. Since doing this, we have started experiencing a Async_Network_IO problem that didn't exist prior to the migration. The select is returning approx. 2500 rows or 9 fields to cache them into a program. Before the move, the pull took about 25seconds, now the same program takes 15 mins to return the data.

    Server/Database background.
    Servers:
    -Dual-Dual Core XEON at 3.0GHz per core
    -4GB PC5300 RAM.
    -System Drives are 3 72GB SAS Drives
    -2 Public Gigabit NICs
    -1 Private Gigabit NIC for cluster and backups
    -Windows 2003 Ent. SP2
    -SQL Server 2005 Std (9.0.3050)

    Storage array is a MSA500 with 14 drives.
    2 - Raid 1 - Quorum
    2 - Raid 1 - System Tables, TempDB and Log Files
    3 - Raid 5 - Production DBs << Problem DB exists here.
    4 - Raid 5 - Process Data
    3 - Raid 5 - Future use

    This Database is in simple Recovery mood and stats are not updated automaticly. Tables are indexed and other than this one pull from a single table we have no problems that we can see. A side note that I noticed is that after clustering, when I add or change any of the login's, I see a delay in opening the properties for the logins.

    Any help you guys can give would be greatly appreciated.

    Thanks.
  2. alzdba Member

    just my 2ct
    - did you perform sqlmaintenance after migration ?
    (rebuild all indexes / dbcc updateusage / sp_updatestats !)

    - can this be related to W2K3 sp2 ?
  3. satya Moderator

    Run sys.dm_os_wait_stats and see the results too.
    http://www.simple-talk.com/sql/performance/sql-server-wait-events-taking-the-guesswork-out-of-performance-profiling/ a good one to go about.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. kpsledge New Member

    Indexes and Stats were all updated after the migration. Win2k3 SP2 was on this server prior to the migration and it ran fine.

    Checked the wait_stats and they look normal.

    For some reason it is just this one query, nothing else and it is just a simple select from a single table with 9 fields and 2500 rows. Even other queries hitting the same database at the same time are executing fine. We are still looking at the code and hopefully we will find and answer.

    Thanks for the suggestions though.
  5. alzdba Member

    If you want to, you can post the ddl and the query
  6. satya Moderator

    Any reason why the stats are not updated automatically?
    Try creating a plan guide for the query that is executed on this troubled table only using SP_CREATE_PLAN_GUIDE statement.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. joechang New Member

  8. kpsledge New Member

    We finally found the issue. The Network card was having problems talking at 100/full duplex. It had set itself down to 100/Half dup. Changed the card out and all the problems cleared up. Thing that confused me was this was fine prior to the migration but could have been brought on by a reboot of the system. The System that was having problems was an ES40 Alpha server running Open VMS.

    Thanks to all that made suggestions.

Share This Page