SQLWebpedia

Average: Select ratingPoorOkayGoodGreatAwesome

No votes yet

 

Your NIC card configuration settings maybe the cause of slow file copies over your wide area network (WAN). Also, know that Windows Explorer file copy is designed to throttle its network usage, so use Robocopy, Richcopy, FTP, etc instead.

 

Testing MTU size to use requires sending packets that have do not fragment bit set in them. Just find the lowest packet size that doesn't throw the "Packet needs to be fragmented but DF set." error. DF means Don't Fragment.

 

c:\> ping -f -l 1322 -n 1 ash01wdb01

 

Pinging ASH01WDB01.corp.local [10.10.10.17] with 1322 bytes of data:

 

Reply from 10.10.10.17: bytes=1322 time=78ms TTL=125

 

Ping statistics for 10.10.10.17:

    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

    Minimum = 77ms, Maximum = 78ms, Average = 77ms

 

c:\> ping -f -l 1323 -n 1 ash01wdb01

 

Pinging ASH01WDB01.corp.local [10.10.10.17] with 1323 bytes of data:

 

Packet needs to be fragmented but DF set.

 

Ping statistics for 10.10.10.17:

    Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

 

With those results above, we would set our MTU to 1322 to get the biggest payload without the networking gear breaking up the packets. Don't rely on Wireshark or Netmon results as it can't tell if your firewall breaks up the packet into small chunks or not when looking at your server's packet trace.

 

Also, make sure to test all your boxes as there may be a difference in the network hops that greatly affected MTU.

For example:

C:\Server1>ping -f -l 1323 -n 1 sjl01wdb01

 

Pinging sjl01wdb01.prod.dm.local [10.10.10.17] with 1323 bytes of data:

 

Packet needs to be fragmented but DF set.

 

Ping statistics for 10.10.10.17:

    Packets: Sent = 1, Received = 0, Lost = 1 (100% loss),

 

C:\Server1>tracert SJL01WDB01

 

Tracing route to SJL01WDB01.prod.dm.local [10.10.10.17]

over a maximum of 30 hops:

 

  1     3 ms    <1 ms     2 ms  sjl01ar04 [10.10.42.2]

  2    <1 ms    <1 ms    <1 ms  10.10.0.137

  3    <1 ms    <1 ms    <1 ms  10.10.0.38

  4    <1 ms    <1 ms    <1 ms  SJL01WDB01 [10.10.10.17]

 

Trace complete.

 

Output from another server:

C:\Server2>ping -f -l 1400 -n 1 SJL01WDB01

 

Pinging SJL01WDB01.prod.dm.local [10.10.10.17] with 1400 bytes of data:

Reply from 10.10.10.17: bytes=1400 time<1ms TTL=127

 

Ping statistics for 10.10.10.17:

    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

    Minimum = 0ms, Maximum = 0ms, Average = 0ms

 

C:\Server2>tracert SJL01WDB01

 

Tracing route to SJL01WDB01.prod.dm.local [10.10.10.17]

over a maximum of 30 hops:

 

  1     1 ms    <1 ms    <1 ms  10.10.50.1

  2    <1 ms    <1 ms    <1 ms  SJL01WDB01 [10.10.10.17]

 

Trace complete.

 

 

There is a related hotfix for local file copy issues: http://support.microsoft.com/default.aspx?scid=kb;EN-US;920739 for large file (500MB) and greater. This problem occurs when you copy large files locally from a fast disk to a slow disk. For example, this problem can occur when you copy large files from a cluster disk to local system disk.

 

PSSQL team blog has a nice performance implication of the scalable networking pack that shows it can hinder SQL server performance. http://blogs.msdn.com/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx They mention you will need to disable SNP also which in in this Windows Server 2003 patch http://support.microsoft.com/default.aspx?scid=kb;EN-US;948496 or you can disable EnableTCPChimney, EnableRSS and EnableTCPA.

 

I found a good article on disabling TCP offloading at a NIC card manufacturer: http://www.alacritech.com/Support/FAQs/DisableOffload.aspx Test to see if helps for large file transfers and you need Windows 2003 SP2 or higher OS. Reboot is not required for this change.

 

I have attached a vbscript that will update the interfaces window size and MTU (1350). Just for your reference, you could manually find correct NIC interface by using this command line script:

 

reg query HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\Interfaces /f AddressType /s

Output:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\Interfaces\{Some Random GUID Value Here to Identify your NIC}]

"MTU"=dword:00000546

My .reg file I use (Descriptions of options are found in this KB: http://support.microsoft.com/kb/314053):

 

Windows Registry Editor Version 5.00

REM Run this from command line: netsh int ip set chimney disabled

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]

"GlobalMaxTcpWindowSize"=dword:3fffffff

"TcpWindowSize"=dword:0000ffff

"EnablePMTUDiscovery"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters]

"SizReqBuf"=dword:0000ffff

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Netbt\Parameters]

"NodeType"=dword:8

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Netbt\Parameters]

"UseDnsOnlyForNameResolutions"=dword:1

 

For home use, just use this tool: http://www.speedguide.net/downloads.php TCP Optimizer, no installation necessary you just get the .exe to run and it can show you all the settings it is going to change. I don't use it on my servers, just desktops.

Attachment	Size
Interfaces.vbs.txt	1.29 KB