About Me

Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group for 11 years, from January 2009 to 2020.

Monday, February 27, 2017

Configure Disk Allocation Unit Size to Maximize IOPS

When configuring a disk volume for use with SQL Server, it is best to configure the Allocation Unit Size appropriately to maximize IOPS (I/O operations/second).

o    Set to NTFS 64 KB for volumes containing mostly SQL data and log files.
o    Do not use default of 4096 bytes.
o    This is because SQL stores data in 8 K pages, and there are 8 pages/extent. Therefore 64 KB/extent. This will reduce the number of IOPS.


After a new volume has been added, go to Computer Management, then Disk Management.


















Posted by at No comments:
Subscribe to: Posts (Atom)