SQL Server: OS Error 665 (File System Limitation) and Linux

I have previously tested and blogged about the NTFS, sparse, file system limitation error 665: https://blogs.msdn.microsoft.com/psssql/2015/06/10/operating-system-error-665-file-system-limitation-not-just-for-dbcc-anymore/ when running DBCC or using Snapshot databases with SQL Server.

Recently a customer asked me if they would encounter the same limitation if they moved to Linux. The answer is not the same limitation and for EXT and XFS my testing and research confirm that a sparse file on Linux may be able to accommodate more fragments than on NTFS.

This paper details XFS storage format and structures: http://ftp.ntu.edu.tw/linux/utils/fs/xfs/docs/xfs_filesystem_structure.pdf – Specifically the theoretical limits table section. In theory an XFS file can allocate 2 to the 52nd number of blocks so if 1/2 of those are holes then 2 to the 26th is the possible fragment count the file could achieve. Since SQL Server writes to sparse files using the SQL Server extent size (64KB) the 4K blocks used with the iNode tracking should not achieve max fragmentation.

Examples
You can experiment with spare files on Linux using the following examples.

Create a regular file using IO block sizes of 4096 bytes. http://man7.org/linux/man-pages/man1/dd.1.html
dd if=/dev/zero of=test.dat obs=4096 count=100

Using the stat utility you can view the block allocations (100 written blocks with 4 blocks of overhead.)
stat test.dat
Size: 51200 Blocks: 104 IO Block: 4096 regular file
Device: 801h/2049d Inode: 5524017 Links: 1

You can use the fallocate utility to replace zeros in the file with sparse holes. Notice that the file size does not change only the blocks allocated to the file.
fallocate -d test.dat
stat test.dat
Size: 51200 Blocks: 8 IO Block: 4096 regular file
Device: 801h/2049d Inode: 5524017 Links: 1

The filefrag utility is another handy utility for viewing the storage layout.
filefrag -v test.dat

The overall result is a net increase in the possible fragments a file could achieve on Linux XFS over Windows NTFS. However, fragmentation is often an easy way to reduce I/O performance so be cautious of what you ask for.

– Bob Dorr