Every once in a while a seemingly simple question pops up on an email distribution list or Yammer thread that ends up spawning a lively and productive conversation. We had one recently that started out with the question:
For SQL Server running on XtremIO, is there any current guidance we should be giving our customers to best take advantage of the 4K element size on XtremIO?
One of the thoughts behind the question was motivated by the existence of a 4k disk allocation unit (DAU) option when formatting a disk with NTFS. Since a 4K allocation size significantly increases the size of the allocation structures managed by NTFS and the frequency with which allocations are made there better be a good reason for using them. With the relatively large files sizes typical of most SQL Server applications, we aren't worried about wasted space even when using the largest available allocation size of 64K so there is really no good reason to use anything but 64K allocation units for SQL Server. This just opened the flood gates to many more SQL I/O questions that went beyond NTFS allocation unit size and XtremIO. I gathered together a summary of the question and best responses in the table below.
Are SQL Server write sizes independent of DAU size?
How much does the additional overhead of 4K DAU impact
Most of the impact of
Does SQL typically write to the log in 4KB and DBs in
Log writes can be a
SQL Server uses a lazy writer and will try to write whole
The lazy writer is
If an extent in RAM is only partially dirty (various pages
The gather write
Typically SQL write performance is governed by TLOG write
You could have a nice
Another great outcome from this discussion was the sharing of favorite white papers and blog posts. I have collected those links and added them here.
SQL Server Best Practices 2012 http://msdn.microsoft.com/en-us/sqlserver/bb671430 and,
SQL Server Best Practices Article in 2007 http://technet.microsoft.com/en-us/library/cc966412.aspx
There is also an interested blog here from Kendra Little:
The best IO size information I’ve seen came for the SQL CAT team a few years back. I think it’s still relatively accurate, check out the appendix of the presentation from the link below.
For the 10 years I worked at Microsoft I found the SQL Server technical community a constant source of knowledge and challenge that made me a better SQL Server professional. The community at EMC is certainly smaller but no less dedicated to sharing information and promoting technical excellence. I hear the same thing from anyone involved with PASS and local users groups. Let us know how you participate in a SQL Server community and what it means to you. Thanks for reading.