Highlighted
Enthusiast
Enthusiast

4KB Alignment Ratio

Microsoft SQL Server 2014 (SP2) 12.0.5000.0 (X64) Enterprise Edition

Microsoft Windows Server 2012 R2 Standard x64

VMware ESXi, 6.5.0, 8935087

Dell EMC VxRail P470F

We've been trying to understand the 4KB Alignment Ratio of all the VMDKs of an MS SQL Server. We've been lately seeing read/write latency issues on this DB VM and have been trying to understand the I/O behavior of SQL Data, Log and TempDB VMDKs of this VM. One thing that captured our attention was the 4KB Alignment ratio (especially the Log and TempDB) and also the difference of 4KB Alignment ratio between these 4 VMDKs. Do they indicate a problem at the disk subsystem level?

2018-10-16_17-52-22.jpg

SQL Data (E:\)

2018-10-17_17-02-35.jpg

SQL Data1 (F:\)

2018-10-17_17-02-58.jpg

SQL Logs (G:\)

2018-10-17_17-03-20.jpg

TempDB (H:)

2018-10-17_17-03-40.jpg

512n format of this VM

2018-10-17_16-25-15.jpg

VM storage policy for this particular VM

2018-10-16_17-59-16.jpg

0 Kudos
3 Replies
Highlighted
Contributor
Contributor

See this thread: understanding the graphs

From what I understand, those graphs show a healthy alignment ratio for all drives, and logs will roll and rotate so I'm not terribly surprised if that data isn't entirely aligned at a block level either.

Do you have other heavy IO VMs on the same VxRail host(s)? Also what does the network utilization on the backend look like?

0 Kudos
Highlighted
Enthusiast
Enthusiast

Yes, I came across that thread. Thank you.

There are some DB VMs that have heavy workloads on this vSAN Cluster, that have similar IO behavior. According to Network Insight, there's nothing suspicious when it comes to utilization and/or throughput. I don't understand why wouldn't the log rotation just align. Is there way to find out the physical sector size of an SSD in esxcli? I tried with "esxcfg-info" but it doesn't give that info or I don't see it.

Just in case you want to look at all the graphs of DB, Log and TempDB, here it is,

IOInsight.zip - Google Drive

0 Kudos
Highlighted
Enthusiast
Enthusiast

Thanks to Storage Hub, I was able to find the physical and logical sectors size.

2018-10-22_14-31-53.png

0 Kudos