Backup Exec SQL Transaction Logs - High Physical Disk sec/Read (perfmon)

[alexslv]

Dear All,

I hope somebody will be able to help us on the following issue:

We perform SQL Transaction Logs backup every two hours following full backup @ 8pm daily.

For some reason during backup of SQL Transaction Logs our Disk performance is degrading dramatically. We are monitoring it with “PerfMon“ tool and it shows the following:

Avg. Physical Disk sec/Read jumps up to 140ms and stays constant during entire backup job

Last 24 hours graphical view - http://goo.gl/WxkFR

Avg. Physical Disk Queue Length jumps up to 150 requests and also stays constant there until backup job finishes

Last 24 hour graphical view - http://goo.gl/AwW75

Under normal operation our SQL server very occasionally experience higher than 50ms Disk sec/Read spikes and normally stays below 20ms throughout the day, which is exactly as per Microsoft recommendations.

Can anybody help us to understand as what is considered to be normal disk performance during SQL transaction logs backup and whether we should start to panic at this point?

Our SQL h/w setup:

Dell PE 2650

4x 73GB SCSI ULTRA320 (10, 000RPM) configured as hardware-based RAID 5

+ Software we use:

Backup Exec 2010 R2 with all latest patches

Remote Agent for SQL – latest version

If you do need any more details – please let me know.

Thank you for your help,

Alex

[CraigV · Accepted solution]

Hi there,

If you're following MS's best practice on SQL, then the database should be on RAID5, and the logs on RAID1. The reason for this being that writes to the logs on RAID1 would be faster than on RAID5.

That said, it also depends on the speed of your disks. 10K drives would be slower than 15K leading to less IOPs, leading to lower disk performance. Also, if you're doing full backups every 2 hours, this can hamper your performance.

I would keep an eye on the performance of SQL, but if it is within the limits currently of what MS have set as a standard, I wouldn't get too worried. As long as during the backups, and after them running the queue lengths don't stay excessively high, it should be OK. Once users complain about slow application speeds, that's the time to start investigating further with SQL and your hardware.

Laters!