MS SQL 2005, automatically kill processes

Question

we are running a program which creates 4-5 processes on our MS SQL Server per instance. When the number of processes surpasses 230 the program stops working and crashes every few minutes(The sql server logs say "All schedulers on node0 appear to be deadlocked due to a large number of worker threads waiting on ASYNC_NETWORK_IO. Process utilisation: 0%"). Our solution when this happens is to just kill the processes on the sql server, which seams not to cause any other problems but we would like to solve this problem permanently. How can we fix the root problem? Or if this is something only the developer of the problematic application can do, what possibilities do we have to create a workarround(e.g. a timeout for those processes?)

Answer 1

See if this helps: http://blogs.msdn.com/b/psssql/archive/2008/09/03/how-it-works-17888-all-schedulers-on-node-appear-deadlocked.aspx

Answer 2

If your program creates 5 connections on the SQL instance and you are seeing 230 busy workers on the instance, that implies at least 46 copies of the program. Are those all running on the same workstation? IIRC, the default number of workers is 255 and it is likely you are bumping into that. One consequence of that is long login times for clients. Depending on the application, login timeouts could be causing crashes, so could long-running queries.

ASYNC_NETWORK_IO implies that SQL Server is waiting for the client application to read data from the SQL Server. Perhaps you have saturated the network connection on the workstation, or even on the server?

What sort of networking hardware do you have in the server? Is it a multicore server?