SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 SQL 2012 backup? SAN?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLServerDBA_Dan
Aged Yak Warrior

USA
752 Posts

Posted - 08/21/2013 :  12:49:54  Show Profile  Reply with Quote
I'm seeing some strange behavior and need to think this through. I'm testing SQL Litespeed vs the native backup and in my testing I ran into issues with the SAN. It's an AMS2100 with 30 x 15K spindles in a RAID 10 (28 active + 2 hot spare). The server is an HP DL380 with 2 dual core procs and 16 GB or RAM and has Qlogic HBAs.

The backup I'm attempting to do is of a 100GB database and the syntax is as follows:

BACKUP DATABASE blahdb
TO DISK = 'NULL'
WITH FORMAT, COMPRESSION
,BLOCKSIZE = 65536
,BUFFERCOUNT = 1000
,MAXTRANSFERSIZE=2097152


If I run this same command on another server with a 270 GB database the backup will finish in just under 3 minutes and have about 600mb/sec *note that the other server is local disk (10x15k raid 10). On this server I am only able to get about 80mb/sec. Even a vanilla backup seems to throttle the disks. When I look at Hitachi's High Command SAN software, the SAN is basically idle. This would indicate that the IO is stuck somewhere before the SAN. SQLIOSIM seems to be getting up to 500mb/sec from the SAN.

My question is: Does anyone know how the SQL Server backup is structured for reading from the data file? Is it possible that something on the SAN or network could throttle the SQL backup and only the backup?


Thanks!




Daniel, MCITP & half an MCM
SQL Server DBA

Edited by - SQLServerDBA_Dan on 08/21/2013 15:06:15

yelouati
Starting Member

10 Posts

Posted - 08/24/2013 :  00:00:01  Show Profile  Reply with Quote
First things first- Check your wait types during the backup. SQL will generate 1MB blocks and store them on disk. These will fill up you controller/drive throughput whichever is slower. Yes, if sqlio shows 500 MB/s, that makes sense and you should be able to achieve that.

Things to look at first, if using LiteSpeed, how many cpus are you using. That may be the reason. Also, look at the various parameters such as compression level....

Check you SQL log and look for messages such as " # of IO took more than 15 secs" or something like that. Then you know you have a controller/SAN issue.

Check Activity Monitor during backup and look at the I/O latency. Should be < 20 ms. <100 ms at worst.

how many data files does you db have? Only one + 1 log?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000