4

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.

2
  • Be wary of file copies... they all work differently and generally won't help you too much in figuring out anything important (IMHO). blogs.technet.microsoft.com/josebda/2014/08/18/… – Sean Gallardy - Retired User Apr 4 '17 at 19:59
  • Understood -- in this case though I think everything I'm querying is fully in memory so the issue is just pulling it from SQL Server. I'll try some of Brent's suggestions below – Steve Durham Apr 4 '17 at 20:10
4

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.
3
  • Thanks -- I have found your articles, through the Google pdf I think. I've tried with very large instances, running EE with 205GB for example, but right now running with 29GB. SQL server actually isn't using all of that when I run this query. I do multiple runs and the 2nd is a bit faster so I think the data is in cache. I'll chew on the suggestions thanks. – Steve Durham Apr 4 '17 at 20:04
  • I'll try your counter check, and the larger row check (yes the rows are rather short in this table). It is slow on the actual machine, using TCP, or named pipes or sharedmem. – Steve Durham Apr 4 '17 at 20:07
  • I think it is in cache, I run a query that requires touching every single row first, then I try the select. I'm not sorting when I test (have tried though and not much effect). Running it local is slow too. I'll try the wider data, and your counters. – Steve Durham Apr 4 '17 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.