July 14, 2015 at 2:55 pm
I'm looking to understand how an in memory insert using local data can be dependent on the network. I have extensive waits and the bottleneck is the network.
According to this msdn article BULK INSERT is using "In Memory" protocol (not the network) to insert data.
The database and source data being inserted are on SSD Stripe sets (qty 6 SATA-III OCZ Vertex 3's).
Any ideas on why these inserts are network bound?
July 14, 2015 at 4:07 pm
ASYNC_NETWORK_IO waits are rather unfortunately named. They don't necessarily indicate that the network is the bottleneck. Basically, when a client issues a query, SQL Server places the result set to be returned to the client in an output buffer, sends the result set to the client, and waits for the client to acknowledge it has consumed the result set.
While the network is one piece that can cause a delay in that process, it's somewhat more common for the problem to be inefficient processing of the results by the client.
An easy way to see this caused by a client application with no network involvement is to run a SELECT * against some large table using Results to Grid in SSMS. Running that using SSMS on the server housing the instance will still rack up tremendous ASYNC_NETWORK_IO waits, because SSMS is not so fantastic at consuming those results.
Having said that, that example also illustrates a common issue underlying ASYNC_NETWORK_IO waits, which is sending far too large a result set to a client application.
In the case of a BULK INSERT it's a little more unusual (in my experience, at least) but apparently that can show up as a significant part of the waits under some conditions.
Check out this article: http://henkvandervalk.com/speeding-up-ssis-bulk-inserts-into-sql-server. Towards the end as other bottlenecks are removed, ASYNC_NETWORK_IO starts to bubble up as a significant percentage of the waits. Adam Machanic in his comment on the article speculates as to the possible cause, but it doesn't seem anybody tested to confirm.
There are other indications of the same thing sprinkled across the web (like here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ed2c2580-c42a-4d78-aa60-02872ac2ce18/asyncnetworkio-qyery-running-slow), but nothing quite so authoritative as a reproducible test.
At any rate, you can rest assured it's probably not the network. It's more likely a component waiting for a batch to be filled, although I'd like to be able to reproduce that in a test. If I get some time I might even try to confirm that, but right now I have a lot of tests/demos in my queue; I've fallen pretty behind.
Hopefully this at least clears up the bit about the (lack of) network involvement.
Cheers!
July 14, 2015 at 4:56 pm
ok, thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply