Tell me more ×
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.
up vote 4 down vote favorite

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

2 Answers

up vote 4 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
up vote 0 down vote

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.