Backup More Than 1GB per Second Using SQL2008 Backup Compression

Tuning the Performance of Backup Compression in SQL Server 2008

Technical Notes — Thu, 24 Apr 2008 07:17:24 GMT

Authors: Mike Ruthruff, Sanjay Mishra Contributor: Steve Schmidt Technical Reviewers: Kevin Farlee, Sunil

VLDB Tips

SQL Server Data Platform @ MTC — Tue, 25 Mar 2008 19:44:53 GMT

Last year we worked with a customer on a proof of concept that involved populating and running some performance

re: Backup More Than 1GB per Second Using SQL2008 Backup Compression

lingzhuz — Wed, 05 Mar 2008 08:56:20 GMT

Today we are configuring for another perf test, so I took advantage of the system idle time to run a uncompressed backup of the 2.9TB database. It took a little over 1 hour` (1 hour and 48 seconds). The backup size=1.88TB. The avg CPU=3.1% (64core). max CPU is 29%.

re: Backup More Than 1GB per Second Using SQL2008 Backup Compression

jchang6 — Tue, 04 Mar 2008 23:24:34 GMT

I am a little puzzled by the CPU numbers,

a couple of years ago, I tested LiteSpeed on the HP rx6600

32 cores could do 2GB/s at 3-4 compression ratio on the TPC-H data set, at full CPU utilization,

so your 48% makes sense

but a backup w/o compression had almost no CPU, <5%,

why was it 30% in your test?

re: Backup More Than 1GB per Second Using SQL2008 Backup Compression

lingzhuz — Tue, 04 Mar 2008 07:10:42 GMT

Hi Colin, we have a whitepaper coming out soon by Sanjay Mishra and Mike Ruthruff. They used HP DL585 and a midrange disk array closer to what you have in mind.

re: Backup More Than 1GB per Second Using SQL2008 Backup Compression

colin.leversuch-roberts — Mon, 03 Mar 2008 22:15:12 GMT

I'm impressed, naturally, but can we have some figures for mere mortals please? My average box is likely to be 4 sockets with either dual or quad cores.It sometimes takes real struggles to get 1gb ethernet let alone 10gb. How about a reasonable number of cores over 4gb fibre to a sensible array, say 14 x 15k spindles in a raid 10 .. that would give me a much better reference point.

That doesn't mean I don't think wow! and well done, it's just I'm unlikely to run into that type of hardware.

re: Backup More Than 1GB per Second Using SQL2008 Backup Compression

lingzhuz — Mon, 03 Mar 2008 05:30:58 GMT

Yes, restore the compressed backup to 2.9TB took 20minutes.

re: Backup More Than 1GB per Second Using SQL2008 Backup Compression

multithreads — Mon, 03 Mar 2008 00:45:35 GMT

Is restore fast as well?

Sreekanth

Backup More Than 1GB per Second Using SQL2008 Backup Compression

Windows Vista News — Sun, 02 Mar 2008 23:00:02 GMT

New post at blogs.msdn.com

re: Backup More Than 1GB per Second Using SQL2008 Backup Compression

lingzhuz — Sun, 02 Mar 2008 22:28:54 GMT

The avg CPU% is 48% for 1.2TB backup compressed (took 20min). To backup the same database without compression took 27min, the CPU% was around 30%. The avg CPU is 37% backing up 2.9TB compressed. I didn't try backup without compression on 2.9TB database. Sanjay Mishra and Mike Ruthruff did an extensive study on backup compression, and a whitepaper will be published very soon.