VSPHERE 6.5 U2 : VM MS-SQL server : partitionning : thin or static ?

[CERKYR]

Hello,

Is there some mandatory configurations to configure a new virtual server (W2K16 Datacenter) used to run MS-SQL (2016 Express SP2), about the partitionning ?

The partitionning must be static or thin (dynamic) for the SQL Database and Logs ?

Regards,

[compwizpro]

Are you referring to thin vs thick provisioning for VMDKs?  I believe any storage vendor will tell you to go thick provisioning eager-zero to be on the safe side for performance.  However with all flash arrays and VAAI, the performance hit of having to allocate new blocks is next to nothing.  It all depends on how write I/O intensive your workloads are.  I believe VMware published a report some years ago explaining how the performance difference between thin and thick is nearly negligible.

I am in the process of evaluating this myself since I want to take advantage of UNMAP at the guest and storage array level which requires thin provisioned VMDKs to work.

Besides storage performance, there are additional things to consider when using THIN disks such as additional datastore space management so keep that in mind as well.

[CERKYR]

Then, I try to clarify your ideas :

1 + THIN and STATIC have the same performances on SQL Server Database (more or less)

2 + THIN is better to manage more space disc

3 + If the SQL Database have many many I/O, a static partitionning is better.

Is it correct ?

Regards,

[compwizpro]

That is correct.  YMMV for SQL servers based on the workload and as I mentioned, for higher IO SQL servers, it might not be worth the space savings to take a potential performance hit.  You might want to use microsofts SQLIO utility to make sure you are achieving adequate performance based on your workload requirements.

[CERKYR]

Thanks,

I also p^lan to try this :

TechNet Diskspd Utility: A Robust Storage Testing Tool (superseding SQLIO)