Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 SQL 2012 backup? SAN?

Author  Topic 

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2013-08-21 : 12:49:54
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

yelouati
Starting Member

10 Posts

Posted - 2013-08-24 : 00:00:01
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
   

- Advertisement -