3 Replies Latest reply on Oct 22, 2018 5:48 AM by winsolo

    4KB Alignment Ratio

    winsolo Enthusiast

      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:\)



      TempDB (H:)


      512n format of this VM



      VM storage policy for this particular VM


        • 1. Re: 4KB Alignment Ratio
          roxxor Lurker

          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?

          • 2. Re: 4KB Alignment Ratio
            winsolo 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

            • 3. Re: 4KB Alignment Ratio
              winsolo Enthusiast

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