SQL Server backup performance (We're not using Nimble VSS snapshots)

[hduong71]

Thanks for the additional info.

The network latency for 4 Gbit FC is 1/4 compared to 1Gbe. The latency of 10Gbit Ethernet is 1/10th that of Gbe.

Normally it doesn't matter since we now live in a multithreaded world which would parallelize the I/O request. But with single threaded there is no parallelization.

But with single threaded, you send 1 I/O and wait, the transit time from port to port can now be significant. With multithreading it's not an issue since you're doing more work collectively.

[julez66]

Gotcha, so it's going to be a network latency issue, not a network bandwidth issue.  So if we make a transition from 1GbE connectivity to 10GbE connectivity on our servers we should be golden and actually see a lower backup time (single threaded) than we were seeing previously.  Hopefully we can try this out in the near future since our Nimble already has the 10GbE controllers, we just need to finish the connectivity on our servers.  In the mean time I'll keep this thread open, we hope to test this soon.

[rugby0134]

I would also try to turn SQL client side compression off for the dump file, and then turn file system compression on for the dump volume.  For logs and dump volumes you want to turn Cache off, but not compression. The Array get's better write performance when compression is on. Since the Nimble is going to compress the data in the array, you should see a higher MB/s transfer rate, and the same if not better compression on your storage.

[julez66]

I verified with our DBA that we were already doing this, I was pretty sure we were, but just wanted to verify again, he said that is correct.
The rest is just the Nimble performance policy for SQL logs, which we are using for the appropriate volumes, along with using MBP for log/database/etc... volume layout.
All that being said I think that just leaves us with the suggestion above of trying 10GbE which we hope to do soon and see a significant performance increase.

[aspnerd82]

Two comments...

1. Why are you using 4k formatted drives? That is not optimal/best practice for SQL server at all!

2. How many VLFs do you have in your database which is giving you issues?

[julez66]

1. We're using 64K, I think that was Dave who was using 4K.

2. Not sure about the VLF as overall performance was slower for the backup with all databases.

The temporary band-aid was that we are using multiple backup files (which we weren't doing at before, but probably should have been) until we can migrate 100% to 10GbE.  The belief from Nimble support is that we are being bottle-necked by our 1GbE connectivity which is probably exacerbated by the fact that we're not using the maximum number of paths since we're going from 1GbE servers to a 10GbE controller.  Like it's a queue problem or something of that nature.

Unfortunately until we make it to 10GbE end-to-end I don't think we can really say that it fixed it, but it's also my belief that the 1GbE to 10GbE is causing trouble with snapshot duration also.  Personal recommendation and I by no means am a Nimble engineer or anything.  But I think if you have 1GbE servers get a 1GbE controller until you can actually go to 10GbE end to end.  Either that or make sure you have additional NICs to use in your servers to make use of the maximum number of paths supported.