Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Suspended process with ASYNC_NETWORK_IO wait type

Posted on 2008-10-13
3
Medium Priority
?
6,340 Views
Last Modified: 2008-10-28
I am using and Access front end to connect to a SQL Serve 2005 database using linked odbc tables. This database was upscaled some months ago from a multi-user Access 2003 database. I have noticed many suspended processes with ASYNC_NETWORK_IO wait type in SQL activity monitor.

The SQL statement of one of these processes is:
SELECT "CustomerID" ,"CompanyName"  FROM "dbo"."Customers" ORDER BY "dbo"."Customers"."CustomerID"

The CustomerID column contains a numeric index, and CompanyName a text string, with about 12,000 records in the table. The server has a gigabit nic.

I was looking in the direction of a network bottleneck due to the wait type until I did observed the following: The process gets suspended almost immediately after opening the access front end on a single client machine; Executing the T-sql command locally on the server executes almost immediately.

Can someone confirm what causes result in suspended processes with this wait type? When does the decision get made to suspend the process? Where should I go from here?
0
Comment
Question by:m3tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 22709037
ASYNC_NETWORK_IO means sql is waiting on a network.
The data must be loaded into memory on the client. This will definitely be a problem because most client desktops are not that powerful.

Consider updating your sqlserver indexes and make sure all statistics are up to date.
0
 
LVL 2

Author Comment

by:m3tech
ID: 22712926
Could you forsee the client machines running low on memory and dropping the transaction and that manifesting as an ASYNC_NETWORK_IO wait?

Could you point me toward some documentation on updating sqlserver indexes and "all statistics"? I'll dig for the information myself, but would appreciate being pointed in the right direction. I am an experienced tech (general break-fix), but have just recently been asked to take over database development/maintenance and am quite new to troubleshooting a custom databse of this nature.
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 2000 total points
ID: 22718807
As you have numeric index on customer id table you need to do the following
With both SQL 2000 and SQL 2005 you can get the fragmentation information by using the DBCC SHOWCONTIG command.  In addition, you can use the dynamic management view sys.dm_db_index_physical_stats in SQL Server 2005.  These commands are great, but you really need to collect the information and then analyze the data to determine which indexes should be rebuilt versus which indexes should be defragmented.

To rebuild or defrag indexes you can use the DBCC DBEREINDEX or DBCC INDEXDEFRAG statements.  In addition, you can use the ALTER INDEX REBUILD/REORGANIZE statement for SQL 2005.


LOOK FOR sys.dm_db_index_physical_stats IN BOOKS ONLINE TO KNOW WHEN TO REORGANIZE OR REBUILD INDEX
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most of the applications these days are on Cloud. Cloud is ubiquitous with many service providers in the market. Since it has many benefits such as cost reduction, software updates, remote access, disaster recovery and much more.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question