By default, The windows Disk partition is not setup to run SQL Server optimally.but It’s easy to set it up , if you take care of it at the beginning. in order words ,you can optimized it when you create a new Disk and before you can use it for data writing.
if you are on SAN Storage just tell your Storage admin to create and format your SQL Disk with a 1024 KB starting offset, and a 64 KB block size.
By default in Windows, if you create and format a new partition it will format with 4K block size which is not optimized for SQL Server installation. Usually As per best practices, we create different partition for SQL Server installation (which should be on faster storage) rather than installing it on the windows partition (which normally not of faster storage) and that is why we dont have to need to change windows partition block size.
Lets see how we configure Windows as per best practice (SQL Server on Faster disk) and how we create and format SQL Server data partition with desired offset and block size to make it optimize for SQL Server installation.
NOTE: Your SQL Data and Log Drives Need a 1024Kb Starting offset, and a 64Kb Block Size.
How to Check the “Disk Block Size”
Run the below command in to any Disk partition.which you want to check either it is optimize for SQL Server installation. i ran this command in my laptop “D” Drive to check its block size.
FSutil FSinfo NTFSinfo D:
All you’re looking for is Bytes Per Cluster. by default in windows it is 4096 bytes as shown in above screen. to check whether it is optimize for SQL server installation.
Formula “Bytes Per Cluster/Starting offset”
Lets apply the above formula in default windows partition values “4096 bytes / 1024 = 4KB”, which tells us it is not optimize for SQL Server installation. rather it is formatted with default block size (4K).
How to create VM as per Best practices for SQL Server.
lets see how we can create a VM for SQL Server installation. our first goal is to put SQL server Data and Log Partition on Faster storage and windows could be stayed on low end storage.
1-SQL Server VM Disk Configurations
Below is the properties of VM. which is used for SQL Server.
The first disk is on “7k Storage” which is used for OS installation and its SCSI Controller type (SAS) is the default one.
The 2nd disk is on “15k Storage” which is used for SQL Data and Logs installation and its SCSI Controller type is Paravirtual. which is used for High I/O disks as per VMWare best practices.
Now we see the layout of our SQL Server VM. lets install the OS on Firsts Disk (C) at (7K) and create a New Partition (D) after the installation of OS on (15K) for SQL Server installation.
2-Create Data Disk Partition
Use disk part to create a new partition.
List the existing Disks
DISKPART>list disk
DISKPART>select disk 1
Where one is our data disk as you can see in list disk it has 49 GB free space out of 50(which means its totally free)
DISKPART>create partition primary align=1024
DISKPART>assign latter=d
Now disk has been create, if you go to “Administrative Tools ->computer management -> Disk management”. you can see a new Disk (D) here. lets format it with 64K block Size. while disk formatting choose Allocation Unit Size (64K) as shown below.
Here is final layout of our SQL Server VM Disks.
3-Verify the Block Size and starting offset
Lets verify the starting offset and block size of newly created (D) Drive for SQL Server Installation.
Run the below command to verify it.
FSutil FSinfo NTFSinfo D:
Lets apply the formula “65536 bytes / 1024 = 64KB”, which tells us it is optimize for SQL Server installation.now install the SQL Server on this partition.
NOTE: Windows Server 2008 partition alignment defaults to 1024 KB (that is, 1,048,576 bytes). This value provides a durable solution. It correlates well with common stripe unit sizes such as 64 KB, 128 KB, and 256 KB.
you can find it using following power shell command.
powershell.exe “get-wmiobject win32_diskpartition | select systemname, name, index, startingoffset | format-table -autosize”
we are only interested in the Partition #0 for each disk, because that’s the partition at the start of the disk (which contains the partition offset).
TIP:
By default VM VMDK are on the same datastore. but as we configured above to keep our SQL data and OS on separate storage. its is possible when storage vMotion happens, both disks are ends up on the same datastore. to avoid this and keep the way we configured it. Use VMware vSphere Storage DRS vmdk anti affinity rules.
VIRTUALIZATION BLOG 
My Database files ,Database log files, tempdb drives are defaulted to 4096 bytes, would you recommened to format the drives to 64Kilobytes for better performance?
Yes as per recommendation it should be, if possible. This is usually when you planned to install DB (SQL) in your system then you keep these guidelines in mind.
In Your opinion is it better to put “Instance root directory” on the same storage which is used for OS installation? Or use for this, the storage designated to “database directory” and “database log directory”. Simplifying, install all SQL database engine services and other features on the same storage which is used for “data root directory”?
it depends, if you have enterprise storage with auto tiering than it does not matter. storage layer will automatically optimize it as per the need.otherwise data directory would be on faster storage. reset of them its your own choice. i am not a SQL server or storage guru. i would also suggest to check Microsoft recommendations.