HPE GreenLake Administration
- Community Home
- >
- Storage
- >
- Entry Storage Systems
- >
- Disk Enclosures
- >
- SAS still has problems with SQL file init?
Disk Enclosures
1828490
Members
2424
Online
109978
Solutions
Forums
Categories
Company
Local Language
back
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Forums
Discussions
Discussions
Discussions
Forums
Discussions
back
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Blogs
Information
Community
Resources
Community Language
Language
Forums
Blogs
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-02-2007 04:11 AM
02-02-2007 04:11 AM
SAS still has problems with SQL file init?
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
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
1 REPLY 1
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-06-2007 03:26 AM
02-06-2007 03:26 AM
Re: SAS still has problems with SQL file init?
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.
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.
The opinions expressed above are the personal opinions of the authors, not of Hewlett Packard Enterprise. By using this site, you accept the Terms of Use and Rules of Participation.
Company
Support
Events and news
Customer resources
© Copyright 2025 Hewlett Packard Enterprise Development LP