SQL Server 2019 drive block size formatting vSAN

Question

SQL Server 2019 drive block size formatting vSAN

Gary Paquette 0

Hi - on the MSFT web site for performance tuning it mentions that one should format the drive at 64K block size.

VMware has an article saying the you should NOT use anything but 4K if on a vSAN, so which one is it?

It is expensive to make these changes in a large business dependent on SQL Server.

We tried 64K and latency was high, tried 4K and latency got much better.

It is difficult for us to decide what is correct as you are the vendor?

HELP!

LiHongMSFT-4306 31,566 Reputation points

2023-02-16T07:07:41.96+00:00

Hi @Gary Paquette

Is this issue solved now? If you have any questions, please feel free to share with us.

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thanks for your understanding!
Zsoldier 1 Reputation point Microsoft Employee

2024-11-19T21:32:06.38+00:00

https://knowledge.broadcom.com/external/article?legacyId=2150019

Snippet from above KB. This applies for VSAN running in OSA mode. Unsure if ESA is different in this aspect.

There are three ways to resolve this issue:

If your goal is to get maximum IOPs, you must choose a small IO size (ideally 4K). With a larger IO size, the system may be bottlenecked on the IO throughput that the vSAN disk group can handle; therefore, a smaller IO size ensures the system delivers the highest IOs per second.

If your goal is to get the minimum latency, you must choose a small IO size (ideally smaller than 64K). An IO request with IO size larger than 64K is split into multiple IO requests at the vSAN DOM Client layer; hence, there is additional latency in processing multiple requests. In the specific case where dedupe is enabled in an all-flash vSAN cluster, you must choose an IO size of 4K to achieve minimum latency because dedupe operates at the granularity of a 4K IO size.

If your goal is to get the maximum throughput, you must pick a large IO size (ideally 256K). With a larger IO size, the system may be bottlenecked on the IOs per second that a vSAN disk group can handle; therefore, a larger IO size ensures the system delivers the highest throughput at the expense of IOPS.

Please adjust the IO size according to the desired goal of performance evaluation. Keep in mind that vSAN is optimized for 64K and smaller I/O sizes; configuring an I/O size >64K will result in increased guest I/O latency.

3 answers

Your answer

LiHongMSFT-4306 31,566 Reputation points

2023-02-16T07:07:41.96+00:00

Hi @Gary Paquette

Is this issue solved now? If you have any questions, please feel free to share with us.

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thanks for your understanding!
Zsoldier 1 Reputation point Microsoft Employee

2024-11-19T21:32:06.38+00:00

https://knowledge.broadcom.com/external/article?legacyId=2150019

Snippet from above KB. This applies for VSAN running in OSA mode. Unsure if ESA is different in this aspect.

There are three ways to resolve this issue:

If your goal is to get maximum IOPs, you must choose a small IO size (ideally 4K). With a larger IO size, the system may be bottlenecked on the IO throughput that the vSAN disk group can handle; therefore, a smaller IO size ensures the system delivers the highest IOs per second.

If your goal is to get the minimum latency, you must choose a small IO size (ideally smaller than 64K). An IO request with IO size larger than 64K is split into multiple IO requests at the vSAN DOM Client layer; hence, there is additional latency in processing multiple requests. In the specific case where dedupe is enabled in an all-flash vSAN cluster, you must choose an IO size of 4K to achieve minimum latency because dedupe operates at the granularity of a 4K IO size.

If your goal is to get the maximum throughput, you must pick a large IO size (ideally 256K). With a larger IO size, the system may be bottlenecked on the IOs per second that a vSAN disk group can handle; therefore, a larger IO size ensures the system delivers the highest throughput at the expense of IOPS.

Please adjust the IO size according to the desired goal of performance evaluation. Keep in mind that vSAN is optimized for 64K and smaller I/O sizes; configuring an I/O size >64K will result in increased guest I/O latency.

Answer 1

LiHongMSFT-4306 31,566

Hi @Gary Paquette

Not familiar with vSAN, but since it is suggested to use 4K on vSAN and you did have a test that 4K works better, then just format the driver at 4K.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Gary Paquette 0 Reputation points

2023-02-09T17:00:39.2666667+00:00

Not seeing a big change. Latency is very high in MSDB and another vendor DB.

Trying to understand the science on why MSFT recommends 64KB and VMware says 4KB if vSAN. For me to just do it because a document with little information says to does not help me.

Though thank you for your input!

Answer 2

Olaf Helper 47,436

SQL Server stores data in pages = 8 KB and reads extents = 8 pages = 64 kb; that's which a sector size of 64 kb is suggested.

See Pages and extents architecture guide

VMware has an article saying the you should NOT use anything but 4K if on a vSAN,

They may mean for the system drive, but not for a data drive.

Gary Paquette 0 Reputation points

2023-02-09T17:03:44.6033333+00:00

That would make sense - though the VMware document is to vague and getting an answer from the tech team is non-existent :-)

Here is the link I am referring to:

https://kb.vmware.com/s/article/83134

Answer 3

Erland Sommarskog 121.4K MVP Volunteer Moderator

Here is the link I am referring to: https://kb.vmware.com/s/article/83134

I don't read that document as recommending a 4K block size. They recommend a 4K-aligned block size. So 64K is good, and they give that as an example.

Gary Paquette 0 Reputation points

2023-02-14T23:57:00.8033333+00:00

I guess that would make sense - I will reach out to VMware and let you all know. Appreciate the help!

Share via

SQL Server 2019 drive block size formatting vSAN

3 answers

Your answer