How fast can I pull data through a SQL Server client connection?

What is the highest throughput possible? Has anyone seen anything above 25MB/sec?

I've been trying to figure out what is even possible.

I know there are tuning parameters like packet size, and rows per batch and so one, plus there is overhead involved if going over TCP.
I can copy a file at sustained 250MB/sec.
I haven't found any way to pull > 16MB/sec or so through a query.

I got bcp to do about 25MB/sec by saving to a flat file. But no luck on a query that is simply selecting from a large table (53GB).
I've been experimenting on Google Compute instances with various versions of SQL Server, using maxed out machines and SSDs etc. (32 cores, 200GB mem, and so on)

Is there some theoretical maximum SQL Server can handle on one connection? 25MB/sec seems stupidly slow.

Some things to try:

• Wider data sets - if you're using a narrow table (just a few fields), SQL Server may be waiting for the client app to ack the data and request the next set of rows. Since you're doing experimentation, try building a table with just two fields: an identity, and a CHAR(7000) filled with a string.

• Pull data directly from cache - size your VM so that it can hold, say, 32GB of data in the buffer pool (try 64GB memory, for example), read the table up into cache first, and then try to pull it across the network. This isn't a practical long-term fix, obviously - but if you're bottlenecked reading your data files from disk, then it's time to start tuning that.

• Avoid intermediate sorts - try pulling table contents directly with a SELECT rather than joining multiple tables together and/or ordering the result sets. Again, not a practical fix, just showing how you can get higher throughput for bandwidth testing.

• Try your tests locally first - which just helps to rule out that it's not a bottleneck on the app you're using to pull data across the wire, or network settings between VMs. (Plus, there are limits on network throughput on client VM instances, too - make sure you're using a large enoguh client VM.) If the local test runs fast but the network test is slow, try copying a file locally from the client VM to a shared drive on the SQL Server. If that's slow, then there's your gotcha.

• Check your top wait type - use sp_BlitzFirst @ExpertMode = 1 (Github repo) (disclaimer: I'm the author), and look in the Wait Stats section. If you see ASYNC_NETWORK_IO as your top wait type, the client is the bottleneck (or a slow network). If it's PAGEIOLATCH*, then it's waiting to read the data pages from the data file.

StackExchange Database Administrators Q#169129, answer score: 5