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?
SQL Data (E:\)
SQL Data1 (F:\)
SQL Logs (G:\)
512n format of this VM
VM storage policy for this particular VM
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?
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,