Disk Performance Hands On: Correction

<div><p><img align="right" alt="homer-doh" border="0" height="213" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtuApkoQ0B6x4gpUjq-Nn99jGRsIJVRvquq47tOgTroUUNIYjCaWs_dsgwN8YZxYe1DfbGoMHzX612v5hi3EkNklVCHPpCGMRuTMZlwENBo9yW-vW3htyMB4iAk9APY8fYRcZcdH-M070m/?imgmax=800" style="border-right: 0px; border-top: 0px; display: inline; margin-left: 0px; border-left: 0px; margin-right: 0px; border-bottom: 0px" title="homer-doh" width="150" /> DOH! I made a mistake. Somehow I had it in my head that for OLTP databases SQL Server read data 64 KB at a time. While I was working on the next post for the series I asked a question on Twitter about transaction log write sizes. <a href="http://statisticsio.com/" target="_blank">Jason Massie</a>, a.k.a. <a href="https://twitter.com/statisticsio" target="_blank">@statisticsio</a>, pointed me to <a href="http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx" target="_blank">this post</a> from the SQL Customer Advisory Team. While I was reading it I noticed a small problem – OLTP data reads are done 8 KB at a time. Then I came across <a href="http://sqlblog.com/blogs/linchi_shea/archive/2008/03/29/performance-impact-of-controller-cache-sql-server-oltp-workloads.aspx" target="_blank">Linchi Shea’s post on OLTP workloads</a> which confirmed it. The icing on the cake was when I opened perfmon on one of my servers and looked at the <strong>PhysicalDisk\Avg. Disk Bytes/Read</strong> counter. There it was, plain as day – 8 KB reads. </p> <p></p> <p></p> <p>The good news is that the there was only a minor change in the outcome. For RAID 10 the optimal configuration came out to be a 64 KB RAID stripe (instead of a 128 KB stripe as I previously posted), 64 KB offset, and 64 KB allocation unit size; for RAID 5 the argument could be made for a 128 KB RAID stripe, but the 64 KB offset and 64 KB allocation unit size are still the clear winners. I have updated the text and images in the original posts to reflect the right read size. So fortunately, relatively no harm done (except to my ego).</p> </div>

DOH! I made a mistake. Somehow I had it in my head that for OLTP databases SQL Server read data 64 KB at a time. While I was working on the next post for the series I asked a question on Twitter about transaction log write sizes. Jason Massie, a.k.a. @statisticsio, pointed me to this post from the SQL Customer Advisory Team. While I was reading it I noticed a small problem – OLTP data reads are done 8 KB at a time. Then I came across Linchi Shea’s post on OLTP workloads which confirmed it. The icing on the cake was when I opened perfmon on one of my servers and looked at the PhysicalDisk\Avg. Disk Bytes/Read counter. There it was, plain as day – 8 KB reads.

The good news is that the there was only a minor change in the outcome. For RAID 10 the optimal configuration came out to be a 64 KB RAID stripe (instead of a 128 KB stripe as I previously posted), 64 KB offset, and 64 KB allocation unit size; for RAID 5 the argument could be made for a 128 KB RAID stripe, but the 64 KB offset and 64 KB allocation unit size are still the clear winners. I have updated the text and images in the original posts to reflect the right read size. So fortunately, relatively no harm done (except to my ego).

Share This

• Twitter
• Google+
• Linked In
• Facebook

About Kendal

Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.

Related Posts

Best Practices Performance SQL Server 2005 SQL Server 2008