Copying files–Slowly and Responsibly

I’ve been working with some clients recently who needed to copy large database backups between environments and learnt a couple of things I’ll now share.

Copying Files Slowly

In one instance we had to copy the backup files over a shared network link so wanted to stop the copy monopolising the limited bandwidth.  Our first attempt was to use normal xcopy but out of hours when the link wasn’t in use and while the copy uses most of the bandwidth like we expected it would it was also affecting the performance of the server itself.  The server’s 100Mb NIC utilisation was pegged at 98% and the ASYNC_NETWORK_IO wait stat count in SQL Server was climbing by the second, making the database server’s responsiveness to their web application poor.  As a result, that option was ruled out.

We then considered robocopy knowing it has an Inter-Packet Gap setting which how it uses network bandwidth.  Although this parameter had some influence over the amount of bandwidth it uses it proved difficult to limit it to a large amount of bandwidth.  Reading around it seems as though the option was designed for <1Mb WAN links rather than the 100Mb backbone we were using.  In the interest of time I had to search for another option, but the IPG setting is something I want to look into further as robocopy is good for scripting copies.

In the end the solution came from Google search that found a small, simple and free GUI based tool called SuperCopier (link). Amongst its copy options is a slider tool which allows you to accurately dictate how much bandwidth it uses.  While you have to remember to convert MB into Mb the bandwidth option did what it said it would do and for us limited the copy to 80Mb/s.

image

 

Copying Files Responsibly

Another requirement I had was to copy files as quickly as possible from the server’s SAN storage to a portable USB drive and as part of some research into the best tool for the job the client found this MSDN blog article (link) about copying large files over slow links.

This article was written from the angle of making the copy work as fast as possible but also discussed an area I’d never read about before – un-buffered I/O operations.

When Windows copies files normally it uses the system cache on the assumption that the file is probably going to be read again soon so caching it is a good move.  This can be really annoying if we know the data is never going to be read again, and if we’d rather always keep the memory for something else, like for SQL Server.

As an example, I copied an 8GB file around my laptop and you can see the effect it had on system memory during the copy:

image

image

When I’m copying large files to or from a production database server the last thing I want to do is cause large amounts of memory re-allocation or memory pressure because of that seemingly low priority copy.  The MSDN article the client discussed a new XCOPY parameter in Windows Server 2008 R2 – /J.  Using the /J parameter will perform a file copy using the un-buffered Windows file copy APIs.  As a result, the file bypasses the Windows caches and the size of the system cache doesn’t have the potential to grow.

When I did the same copy as above but using the /J parameter things looked very different:

image

image

Incidentally, for the file I was copying the copy took the same amount of time, although I’ve yet to test larger files to see if it degrades the copy time.

 

What I’m not saying is that copying large files using the standard buffered I/O APIs is bad, Windows and SQL Server have good memory management processes to avoid such things typically being a threat to SQL Server’s performance.  What I do like though is knowing I have the ability to perform large file copies and remove the risk of Windows deciding to bloat the system cache un-necessarily.

Advertisements

2 thoughts on “Copying files–Slowly and Responsibly

    1. gavinpayneuk Post author

      Hi Mark, thanks for the comment – its good to get someone’s view on what for me was a new area. Normally I’d just use xcopy and shout at it to go faster! It’s funny in a way that the answer to copy large database backups around comes from an Exchange tool, although I suspect that’s what the Exchange team were doing and hence they made a tool for it.

      Thanks for the recommendation for eseutil.exe.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s