Best practices for SQL Server – disk settings

Always make sure you align your partitions with the underlying stripe size. Windows 2008 and above (usually) takes care of this by itself, but always double check your alignment. Failure to align the partition can lead to significant performance degradation.

Format your partitions where you will store SQL Server related files to a NTFS Allocation Size of 64KB. While SQL Server can do I/O in any size from 512 bytes to 8M, a larger NTFS allocation size is beneficial as the larger the allocation unit, the less metadata overhead there is and the larger the maximum file system size. In windows 2019 it even possible (and recommended!) to use allocation units up to 2MB.

https://technet.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx
https://blog.purestorage.com/what-is-sql-servers-io-block-size/


Instant File Initialization is something that always should be turned on. If it not, then every time a file is created it needs to be filled with zeroes. This means a huge hit on the storage system whenever a (data)file is created. With instant file initialization, the pointer is created without the need for zeroing the actual data. This means instant file creation and no I/O effects. This will affect restores too… Note that this only applies to data files, not log files.
Sure, the blocks are not zeroed and hence are vulnerable to reading with a hex editor. But as someone put it in a presentation for SQL Server User Group Sweden: “if someone is reading your SQL Server data files with a hex editor, you are already pwnd”.
From SQL Server 2016 this is doable from the installer, just check the box that says “Grant Perform Volume Maintenance Tasks to SQL Server Database Engine Service” and you’re done.

http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx


Disk layout is always an interesting topic – my preferred setup is as follows:

  • C: – Operating system only
  • D: – SQL Server installation
  • E: – Data files (formatted with 64Kb NTFS Allocation Size)
  • F: – Log files (formatted with 64Kb NTFS Allocation Size)
  • S: – SQL Server system databases & backup directory (formatted with 64Kb NTFS Allocation Size)
  • T: – TempDB (formatted with 64Kb NTFS Allocation Size)

This gives a good spread of I/O over multiple disks. I always advocate SSDs or NVMe drives for all databases, with the fastest storage given to the TEMPDB drive.