SQL 2000 / RAID configuration / NTFS cluster size

[Former Member]

What is the optimal NTFS cluster size for SQL 2000 / SAP 4.7?

I have read on Microsoft's website to format the disk where the SQL database will be stored in 64kb, other areas have mentioned 8kb blocks. Because SQL server writes in 8kb and 64kb blocks.

What RAID configurations is optimal for SAP 4.7 and SQL server 2000?

My system's performs 60% random reads, 38% sequential reads, and 2% writes.

I currently have the data stored on four RAID5 disk sets.

Database performance is slow especially when users go into the variant configuration.

[Former Member]

Hello Chris,

Most documentation states that the Windows NTFS block size should be 64k.

I would be very interested to hear from other community members regarding their recommendations.

For troubleshooting IO problems I recommend 521750 FAQ: SQL Server 2000 I/O performance

Thanks

N.P.C

[Former Member]

Hi,

As part of the migration of our SAP Production System from an HP EVA 5000 to an HP EVA 8000 last year we conducted extensive I/O testing using the SQLIO utility to determine the optimal configuration.

These were our key findings:

There was no performance difference between VRAID1 and VRAID5 configuration for reads and VRAID1 showed only a very minor performance improvement for writes. The I/O profile our our production system is 95% reads and 5% writes. Given the additional capacity benefits that can be realised we therefore chose VRAID5.

Background disk levelling did not have a significant impact on I/O performance.

Spreading data over multiple drives/LUNs improves I/O performance. For large systems don’t put all the database data files on a single drive/LUN. Spread the data files over multiple drives/LUNs and map these to the HBA cards. See below.

Be aware that the maximum size of a SCSI LUN is 2TB. In an MSCS configuration you cannot get around this using dynamic disks.

For best I/O performance multiple HBAs should always be configured in an active/active configuration (either automatically or via manual load-balancing)

64KB is the recommended NTFS AU size for MS SQL as it writes 8KB pages in extents of 8 pages

Use Windows Storport drivers.

Following the implementation of these finings on the EVA8000 system we have seen excellent I/O performance with sub 1ms I/O response time on a 2.5TB R/3 database running SQL Server 2000 (recently upgraded to SQL 2005).

I would be happy to provide further details on any of these points if anyone is interested and I have a full set of test data and scripts which I can also share.

Thanks,

Chris