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

    4KB Alignment Ratio

    winsolo Novice

      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

        • 1. Re: 4KB Alignment Ratio
          roxxor Lurker
          vExpert

          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 Novice

            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 Novice

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

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