Query Publisher Queries and ASYNC_NETWORK_IO issue

We are using Scribe (v to synchronize a SQL Server 2008 database with Microsoft Dynamics CRM 2011 Online, and seeing some unfortunate behavior on the SQL server side. We have query publishers set up for each table and are using the snapshot for comparison and only publishing changes (including delete messages).

The problem we are seeing is a large amount of these queries with status SUSPENDED with reason: ASYNC_NETWORK_IO. These queries take 1-2 seconds to execute on our SQL server, but when run under scribe, take from 1-20 minutes. We were able to reduce some of the worst offenders from 20 minutes down to ~5 minutes by reducing the # of columns selected in the queries. Most of those queries are down to 1 column now (just processing deletes), but because they return ~60,000 rows, they still take ~ 5 minutes.

The root cause of this issue seems to be the method scribe is using to pull the records from our database. It seems to pull a record, process it, then pull another. It would be better, for our purposes, if scribe were able to pull the entire record set, release the database connection, then do its processing. Is there an option in scribe or in the SQL server plugin to scribe to enable this sort of behavior? (It would be akin to using DataAdapter.Fill vs ExecuteReader in C# code).

Posted by Jenny S. on May 22 2012 5:42PM { U3007 , F8 , T2601 , C8507 }

(1) Answer