SQL Server Storage: Checking Volumes Block Sizes

  • Article

Overview

The following article was previously created as a blog post on http://felipelauffer.wordpress.com/2016/03/31/sql-server-storage-como-visualizar-o-tamanho-dos-blocos-de-todos-os-volumes/ and some parts were modified to best view in Wiki. 

Sometimes when installing a new instance of SQL Server or even just to review an environment we may have to check all the volumes of our servers to get the block sizes of each one for SQL Server performance reasons or just to review the storage infrastructure and configurations for SQL Server.

IMPORTANT: Always remember to format your volumes with block size of 64K for better performance of SQL Server.

This article presents an simple alternative to get the formatted block size of all the volumes in the server.

Instead running the command FSUTIL FSINFO NTFSINFO C: for each volume and looking at the Bytes per cluster line, you just have to run one script presented in this article.

Checking Block Sizes

To check the volumes block sizes just open a new PowerShell window running as administrator and type the following script:

$wmiQuery = "SELECT Name, Label, Blocksize FROM Win32_Volume WHERE FileSystem='NTFS'"
Get-WmiObject -Query $wmiQuery -ComputerName '.' | Sort-Object Name  | Select-Object Name, Label, Blocksize

You can download this script at the TechNet gallery in the link below:
https://gallery.technet.microsoft.com/PowerShell-Get-Volumes-ac89376b

Results

After running the script of the session above you will get the list of all volumes and their block sizes.

See Also