Comments (23)

  1. TDS is a streaming protocol for data after the initial handshake and the like. TDS packets will automatically be split at the MTU frame limit minus protocol overhead and reassembled on the other end. If you are pushing lots of data around then increasing the TDS packet size can be helpful. If you can set jumbo frames on your network that will also help since you lose less data to protocol overhead, ACK latency and can match a larger TDS packet to the larger TCP payload size. You sacrifice performance on the low chatty end of things where you are working with a single return row or small number of rows as latency will be greater.

    Again, as with anything like this testing is 100% needed.

    Good write up!

    1. Thanks Wes! And thanks for the explanation you provided.

  2. […] Network Packet Size: to Fiddle With or Not to Fiddle With – Robert L. Davis(Blog|Twitter) discusses. […]

  3. Hi,
    If you wanted to fiddle with network packet size in SQL, i.e. increase it to 8096, do you also need to consider it might overwhelm the mem-to-leave area in 32-bit on SQL version prior to SQL 2012?

    1. Hi Julia. No, this does not change how anything is stored in memory. It won’t cause your external memory to be over-whelmed.

  4. Saw your tweet…no I did not attend any of your SQL rally stuff in Dallas.

    1. My apologies then. I must have had you confused with someone else.

  5. […] Robert Davis – Network Packet Size: to Fiddle With or Not to Fiddle With […]

  6. Thank you for providing this insight! Indeed not many people know how to work with this server option!

    1. You’re welcome! Glad you find it useful.

  7. Robert,

    Looking at your reply to Julia,

    I thought if you increase the network packet size beyond default value of 4KB , it requires memory allocation outside buffer pool prior to SQL 2012.

    http://blogs.msdn.com/b/sqlsakthi/archive/2011/11/10/large-network-packet-size-and-memory-pressure-in-sql-server.aspx

    1. Hi Chirag. Thanks for posting the link. I’ll do some investigation into that. I am a little troubled by the blog post though for a couple of reasons:

      1. He provided no actual evidence that it was the cause of the problem. He seems to be guessing and posting nothing that would validate if that was actually it.

      2. He clearly does not fully understand the Mem-to-leave setting (-g). Mem-to-leave is reserved at startup time only. It is not enforced. So even setting a larger MTL would not ensure that 1700 connections later it is still available.

  8. This is an awesome post. More stuff I don’t know and need to learn. 🙂

    1. Thanks! 🙂

  9. Robert,

    If the max MTU is only 1500 would it make since to reduce the Network Packet Size so there is less packet splitting on the network or would this cause more overhead on the server?

    Thanks, Brent

    1. Only change the network packet size if you know for a fact it will improve performance. Test it with a smaller value and see if you see improvements. I am highly doubtful you will see any, but testing is never a wrong thing to do.

  10. I have seen installers using SQLCmd with -a 18000 parameter not running into issues with Out-of-the-box SQLServer installations at around 80% of the time. Only the highly secure IT environments are not letting the installer successfully complete. Even the -a 4096 failed. Only when we finally removed the -a 18000 parameter completely, the installer carrying the sqlcmd line succeeds. am dealing with SQLServer 2008 R2

    I don’t understand the above observations as to why
    1) -a 18000 works for some
    2) -a 4096 parameter fails for some where removing the parameter entirely works

    Any light that you can share on the above would be appreciated ?

    1. Some firewalls and IPsec (IP security) can be configured to block packets that contain certain text patterns.

  11. Hello!
    Just stumbled across this tonight.
    The KBa is for SQL Server 2005, 2008 and it mentions that packet sizes larger than 8060 bytes cause an allocation from a region that uses a size of 384 mb :-). I think that’s a roundabout way of saying that the allocation comes from memtoleave on 32 bit systems.
    http://support.microsoft.com/kb/903002
    SQL Server 2012 documentation still refers to this KBa, and still references the 8060 bytes max as best practice.
    http://technet.microsoft.com/en-us/library/bb402885.aspx
    However, I think that on a 64 bit system it’s likely a moot point, and increased packet size when warranted by workload and accommodated by the network is likely beneficial with little drawback due to SQL Server or Windows memory management.

    1. Great info! Thanks for sharing. I agree with your assessment about 64 bit SQL.

  12. […] talked previously about network pipelines and how to get the most out of a fat network in the post Network Packet Size: to Fiddle With or Not to Fiddle With. If you need to move large amounts of data and have a network infrastructure that can support a […]

  13. […] Network – I have never messed with these properties. SQL Soldier To fiddle with or not to fiddle with […]

Leave a Reply to SQLSoldierCancel reply