async_network_io high on reading data from SQL 2012 instance
-
Hi,
after hearing some colleagues from development complain about "slow database" I started some investigations and noticed constantly report a high level of network_io waits over TCP/IP.
Server Configuration: [SQL2012TEST]
2x 6-core CPUs, HT enabled, 96GB of Ram, 10GbE (MTU 1500), X-cable 1 GbE (MTU 9000), RAID1, RAID5, JBOD and SSD drive configurations.
SQL2012 SP1 11.0.3368
Data Source Table: [dbo].[TestData] (bigint, datetime2(7), bigint, tinyint, float, int, int)
Table: 106'847'023 rows @ ~42bytes/row, 7 columns, total bytes: 4'705'978'766
Initial test:
running bcp from my workstation (1Gbe Network)
bcp "dbo.TestData" out nul -n -T -SSQL2012TEST -dTESTDATA
Total Duration: 330sec
Avg MBytes/Rows per sec: 12.9 / 324'000
Repeating the same test with a simple SqlDataReader implementation shows even worse results.
Total Duration: 398sec
Avg MBytes/Rows per sec: 10.7 / 267'000
I can rule out disk issues as BCP is writing to NUL and SqlDataReader writes to BinaryWriter.Null. I can rule out network issues (so far), NtTTcp reports consistent throughput of 905MBit with almost zero errors/retransmits.
The XE sessions (sqlos.waitinfo) tell me that there is only one wait-type: (ASYNC_)NETWORK_IO. Accumulated total wait time for the SPID running bcp
bcp: 160 sec
SqlDataReader: 220 sec
Well, I am wondering, what is the issue here? What is causing the high wait time, deserialization of the TDS stream?
-
ASYNC_NETWORK_IO means that SQL Server is waiting for the client to process the data, so the delay is somewhere on the line. For a baseline, I would the BCP operation on the server. You may also want to run the operation on a copy of the database on your server.
Is there any encryption set up?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
-
Hi Erland, thanks for your thoughts. That was actually my first reaction: let's run the same bcp command on the server itself.
The effect is: BCP defaults to Shared Memory protocol, the accumulated wait time goes down to <2 secs but the number of wait events shoots up through the clouds (580'000 vs 17'000), the throughput in rows per second does not increase significantly.
Back to TCP/IP protocol ... the wait count goes down but the wait time goes up to nearly the same amount as I had over the network (using my workstation as client).
Then I wrote the SqlDataReader based application to see if the effect is the same and it is with about the same proportions on duration, throughput and accumulated wait time.
The server is not under memory/cpu/io pressure. Data is served warm from buffer pool, the number of pages in the buffer pool for the database is 95% and - converted to bytes - equals the size in bytes of the native-format bcp file.
Would you have any further ideas on what to check?
Thanks a lot
Frank
-
forgot to mention .. there is no encryption set up in any way, neither on network level nor TDE nor anything else.
-
When you ran bcp, did you direct output to nul? I made some tests at home, and I see a distinct difference; something like a 40% reduction in time.
I also found that I got better execution times when I had BCP on my laptop, with the database on my desktop. When running local on the desktop, my throughput was about as same as yours, around 12.5 MB/sec for a 500 GB table that I cooked up.
A .Net client comes with its own overhead. I think you should try writing an ODBC client that uses the BCP API. I think in this case you might be able to suppress the "1000 rows successfully copied", which takes quite some time to produce and slows down the process.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
-
Hi again, have been away a few days.
bcp "table" out nul -n -S192.168.100.230,1433 -T -dTEST -a8000
should direct the data output to nul, right?
Today I have repeated the full test harness again, running on the server directly, data going to nul or disk with different packet sizes and different protocols. And doing bcp on a remote workstation in the same network.
I am linking screenshots of the test results below.
Running BCP directly on the server
http://blog.mengengeruest.info/WP/?attachment_id=377
Running BCP from workstation on same network
http://blog.mengengeruest.info/WP/?attachment_id=379
I also included the wait count and accumulated wait time from XE data collection during the BCP operation as well as network protocol and whether I am writing to NUL or DISK.
As you see from the results for running BCP on the server, the throughput is still quite low. It can be tweaked up a notch by different packet size settings.
9 to 13 MB/s is not really much (I would be happy to see some ~90MB/s) . Is that as good as it gets, even on top notch hardware? I mean I cannot see any blocker:
- using native BCP format (-n option), no "char" conversion
- using Shared Memory as connection protocol
- redirecting to NUL or disks (one could argue that filesystem cache / unbuffered random writing pattern vs. sequential writing could be a factor)
- all data is present in buffer cache, nothing has to be fetched from disk again
- sqlserver is not under memory pressure nor cpu pressure
- the query execution plan is only a plain table scan, no sorting, no joining
If I just copy the 4GB bcp output file between the two network hosts I get an 99MB/s average.
My colleagues already mentioned that "database is shit" and they would rather want go with file-based approach.
Is that something I should address with Microsoft SQLCAT directly?
Kind regards, Frank
-
ffoerster (8/27/2013)
bcp "table" out nul -n -S192.168.100.230,1433 -T -dTEST -a8000should direct the data output to nul, right?
Yes, but you should also add
> nul
to the command line. Since you run this from a harness, you may not have noticed, but BCP produces a progress message after every 1000 rows, and that is quite expensive. Or at least I found so when I ran it from a command-line window.
My colleagues already mentioned that "database is shit" and they would rather want go with file-based approach.
I cannot comment on that since I don't know what your application is all about.
But an RDBMS gives some things you don't get with file-based storage. Transactional integrity, for example.
What is the ultimate goal with these copy operations? Since you use native format, I assume you want to copy data to another server?
Is that something I should address with Microsoft SQLCAT directly?
I don't know which contact you have with SQLCAT, but yes, if you have the budget, it can be worthwhile to investigate.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply