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.
| 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 blahdbTO DISK = 'NULL' WITH FORMAT, COMPRESSION,BLOCKSIZE = 65536,BUFFERCOUNT = 1000,MAXTRANSFERSIZE=2097152If 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 MCMSQL 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? |
 |
|
|
|
|
|
SQL 2012 backup? SAN?