Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

On Windows 2008 R2 running SQL Server 2008 R2 how imporatant is the NTFS allocation unit size on DISK IO performance. It appears to me that server admin who built the few servers for a mission critical app left NTFS allocation unit size (cluster size) to default 4 KB instead of 64 KB. SQL server is already installed.

Does it worth to take pain -- to uninstall SQL -- format the drive with 64 KB cluster size and reinstall SQL server?

share|improve this question
    
On a sidenote; To benefit from a 64k block size, you need trace your chunks all the way through your I/O subsystem, to make sure the 64KB chunks are not split up into smaller chunks somewhere along the way. But I guess you already knew that. –  Roy Nov 16 '12 at 15:46

3 Answers 3

up vote 6 down vote accepted

You shouldn't need to uninstall/install: your data and log files should be on separate disk arrays/SAN Luns from the binaries.

Saying that, 64k NFTS cluster is highly recommended all over the place.
SQL Server does IO in extents which is 8x8k pages = 64k, basically.

For actual numbers on performance differences, I can only find this http://tk.azurewebsites.net/2012/08/ (Azure, but still SQL Server)

share|improve this answer
    
+1 True though system databases might be a little more cumbersome to move after the fact. If downtime can be afforded now I'd rather set the right alloc size everywhere and redistribute system dbs appropriately. –  Aaron Bertrand Nov 16 '12 at 17:25

Microsoft also has a nice TechNet article, Disk Partition Alignment Best Practices for SQL Server, that discusses Disk partition alignment as it relates to SQL Server 2008. In most cases, 64KB is the best choice. Best recommendation would be to make sure your partitions are aligned, test your I/O performance and if Latency and other specs are not to the level of performance that this mission critical app needs, plan for down-time and fix it right!

share|improve this answer
    
The latest gallery templates for Azure VMs with SQL Server 2012/2014 have 64KB stripe size for the OLTP template and 256KB for the datawarehouse (DW) one: tinyurl.com/p32eghd –  wBob Oct 7 at 9:51

since you only get to do it absolutely right once before it goes to production, I'd say so but it depends on your usage patterns. If you're not already live with the system I would grab the config file which acts as a unattended install file if you reference it during your reinstall. That should make the reinstall much easier and consistent.

http://mycodelog.com/2010/09/28/sqlsilent/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.