SAS still has problems with SQL file init?

[Andrew_346]

I posted a thread almost a year ago concerning this issue, and now I'm back with more.

Previous problem was with performance when restoring a 170GB SQL database to 4 data files spread out over 3 MSA50, 10x72GB 10k RPM SAS arrays vs the same # of disks of U320 15k drives in MSA30 arrays. Controllers were P600s and SA6404s, respectively.

At that time, it was taking over 3hrs to restore this database, almost all of that time spent on the "file initialization" that SQL performs when creating data files. It creates the file and writes zeros to the disk to completely clean the space. The same restore on the U320 SCSI box took less than one hour.

Now, the MSA70 and the 15k SFF SAS drives are out. I'm building a new DB server (DL585G2) and PCIe is really my only option, so we opted to purchase the P800 controllers, 2 MSA70 boxes loaded with 15k SFF SAS. (side note: seeing 50 drives in 4U is a sight to behold!)

Running a similar restore (now 180GB) it took slightly less than 3hrs, whereas a similarly configured U320 server takes 1hr.

The same problem is seen if you do a simple CREATE DATABASE and define 1 large datafile. I tested this by creating a 10GB data file on both the new SAS DB server and another server with the same configuration of disks, just U320 15k. On the SAS box, 20 minutes to create a 10GB data file. On the SCSI box, 3.5 minutes.

Everything about the new disks, the P800 controllers, PCIe (4x) says that the SAS arrays should be above and beyond U320 SCSI.

Even when SQL finishes the file initialization, the actual populating of data into the data files is SO MUCH faster with the SAS drives. That tells me that the SAS are indeed faster, but is there something about the serial architecture that the SQL file initialization has a problem with?

Anyhow, I've got a case open with storage support and my sales rep has escalated this up the support tier. Hopefully we can get some SAS engineers (along with a possible MS SQL tech) to help figure this out.

I have no doubt at this point that SAS will actually run our OLTP DB better, but it's this major kink in our disaster recovery plan that has me pissed off.

TIA
-AJ

[Andrew_346]

It has come to my attention that this system was not running the 6.2.0.64 SAS/SATA driver I assumed I had installed from the CD that came with the P800s...

For this system, it definitely needed this driver, as it is the first release to have support for the P800.

However, the improvement in performance (restored the 180GB DB in 23 minutes) was too big of a jump for me to leave this alone.

On my other two systems with P600 controllers and 10k SAS arrays, I was seeing similar horrible performance just on SQL file initialization... I had not upgraded these systems to the 6.2 driver yet, mostly because the release notes ONLY stated that it added support for the P800. It wouldn't hurt to install it on systems with the P600 so I did some before/after tests.

On a DL585G1 with 4cores, P600 and an array of 4x72GB 10k SAS, I created a 10GB SQL database datafile. Initially it took 17m47s. I upgraded from the 5.10 to the 6.2 driver and the same database creation took 3m26s.

HP obviously is not fully disclosing what they fixed in this 6.2 driver.

Either way, anyone running SAS controllers needs to be running the 6.2 SAS/SATA driver for optimum performance.

With this problem corrected, the 15k SAS drives and blowing my 15k U320 SCSI out of the water.