NTFS Cluster size - if changing the default cluster size on the disk is a good idea or not?
You might have come across on the web resources that setting the disk to a 64k cluster size (8 * 8KB pages) might achieve the performance. But have you wondered will this really boost the disk performance, see in what scenario such as lots of reads and writes from the database to disk. In this case take help of SYSMON (PERFMON) for Avg. Disk queue length in addition to Disk Queue Avg, Disk Transfers / sec Avg & Disk MB/Sec Avg counters too
In my experience it is not a rule of thumb that if you change cluster size the performance will be increased. You must consider (always) the options such as frequent transaction log backup and writes to the data file will have better balances the trade off between disk defragmentation due to smaller cluster size and wasted space due to a large cluster size. So taking the defragmentation practices and legacy operating system such as cluster sizes of over 4K were not supported on NT and these enhancements are affective since XP & Server 2003 versions. I would like to refer this whitepaper SQL2000 - "The best practice for SQL Server is to choose 64 KB, because this reduces the likelihood of I/Os that span distinct NTFS allocations, which then might result in split I/Os." for further study.
Going into the deeper into the analysis, in general for the volume in question, it would make sense (in theory) to keep the RAID stripe size and NTFS cluster size the same, and to align it with whatever you will need for SQL Server databases and revisiting the point of number of transaction handling or if its a OLAP based application. There is no doubt that on some fstorage systems doesn't make much difference such as database queries are not normalized as per the practices. On the hardware side of considering the spindle-count, plays important role. As the larger the spindle-count, is the better the performance. So better to spend much time on managing the database and less with optimizing the LUNs of storage.
Based on the investment with hardware you can manage all sorts of mixed loads on the system and in addition to the performance optimization on the SQL Server side. In this case for hardware LUNs are the world, if in case the sQL Server is not a dedicated server and if you are running a mixture such as SQL, Exchange or as file server then you should carefully consider in evenly spread across all the spindles across the entire storage frame.
The conclusion is depending on the disk subsystem you have and the raid type used, there could be a HUGE difference seen by you and your testing in different phases of database sizes, it really depends on what you can afford for a disk sub-system (think about money too). Set a baseline for the performance perspective and perform adjustments accordingly with a proper testing. So having high-end storage sub-system doesn't guarantee a "performance" always, make an adjustment considering the database growth, capacity and future compatibility of software. Also I would like to quote the technet related article:
quote:
Windows NT File System (NTFS) Allocation Unit
SCSI Drives: When you format the new drives in Disk Administrator, you should consider an allocation unit, or block size, that will provide optimal performance. Significant performance gains may be obtained by sizing this to a larger value in order to reduce disk I/Os; however, the default value is based on the size of the physical disk. The best practice for SQL Server is to choose 64 KB, because this reduces the likelihood of I/Os that span distinct NTFS allocations, which then might result in split I/Os. Keep in mind that although this information can be useful, the type of storage you are using (and in some cases also your backup software) will drive the format of your disks. If you are changing the block size on an existing system, be sure to run a baseline in your test environment and another after you have tested the changes.
For more information on planning and configuring hardware, see Inside SQL Server 2000 by Kalen Delaney. For specific information on your hardware, refer to the customer service site of your hardware manufacturer.
The sensible strategy of placing data and/or lot files on a disk will be:
- Place database data file on seperate LUN and similarly differentiating log files.
- Take help of hardware tools such as diskpart to partition the LUN.
- During partition creation ensure to set on (128sectors*512b sector)=64K track boundary. (I was told by our Hardware expert that it will reduce number of movments by head when tracks are spinning). Further study reveals that it also reduces cache buffer allocations for trailing data that you may not access once it has been cached.
- Ensure to perform FULL FORMAT of a partition with AU size of 64K size on the volume where database files are placed.
- Additionally, if you have the option of striping your firmware RAID at 256k over 64k or 128k you should opt for 256k because it helps to increase preformance of SQL Servre read-aheads.
- With recommended hardware settings by vendor you will see your most significant i/o gains during backups and restores.
- HBAs - go for pci-express instead of pci or pci-x. the multipath gate architecture of pci-express allows for faster throughput and reduces BUS noise which you'll encounter as you add more pci and pci-x controls.
(if in doubt about above counters consult your hardware vendor or network expert)
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.
