Performance Policy for SQL 2008 R2 on VMFS5

[jloehr128]

I have a question about which performance policy (specifically to do with block size) that should be used for SQL 2008 R2 Data/log volumes when these volumes are vmdk's within a VMFS5 volume on a Nimble storage array.

• Virtualized SQL 2008 r2 server (Server 2008 R2 x64)
  • OS Drive on NTFS/VMDK (default 4KB allocation unit size <> VMFS5 (1MB blocksize) <> VM Nimble Volume (VMware ESX5 (4KB block) Performance Policy)
  • SQL data on NTFS/VMDK (64KB allocation unit size) <> VMFS5 (1MB block size) <> SQL-Data Nimble Volume (?)
  • SQL logs on NTFS/VMDK (64KB allocation unit size) <> VMFS5 (1MB block size0 <> SQL-Logs Nimble Volume (?)

Should the performance policy for the data and logs be set to SQL Server / SQL Server logs (8KB/4KB block size respectively), or to the VMware ESX5 performance policy since they are on top of a VMFS volume? Is using the 64KB allocation unit size when formatting the ntfs volume still recommended when using a virtual environment in this way? Thanks!

[wen35]

Hi Jordan, great question.  Though VMFS allocates blocks in 1MB chunks - it does not break guest OS blocks down to 4KB chunks, the allocation unit size dictates how big the IO size is.  Either performance policy would do just fine - the SQL Data one would be slightly better than ESX one as the amount of metadata we need to maintain is less, due to large volume block size.  What you don't want to do is setting the NTFS allocation unit size of 4KB, and use larger block size on the Nimble side. 

In your specific case, it's good to use SQL-Data perf policy & SQL-logs policy, given you have set the NTFS allocation unit size of 64KB.

-wen

[jloehr128]

Thanks Wen,

So in general the performance policy block size should be the same or smaller than the NTFS allocation unit size? In that case since I'm using 64KB allocation unit sizes on the NTFS volumes, would there be any benefit to creating a performance policy on the Nimble array with 32KB (the largest) block sizes instead of the 8KB setting on the default SQL Server performance policy? Thanks!

[wen35 · Accepted solution]

Hi Jordan, i should have been more clear in my previous response - the actual writes from application would actually dictate the block size of the writes.  Allocation unit size is the biggest block size the app could write for a given block - for random DB I/O, it is safest to use smaller block size.  As for transaction log volume, typical writes come in larger block size, so you could try an experiment with larger than 8KB volume block size and see if there's any performance difference.  The safest rule of thumb is always have smallest block size possible for random I/O on the array side. 

BTW, do you have infosight access?  we could take a look at your array stats and see if everything is running optimally.