m3tech
asked on
Suspended process with ASYNC_NETWORK_IO wait type
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"."Custome
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?
The SQL statement of one of these processes is:
SELECT "CustomerID" ,"CompanyName" FROM "dbo"."Customers" ORDER BY "dbo"."Customers"."Custome
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.