SQL Server->> Sparse file (sparse files)

Sparse file (sparse files) is not an attribute of SQL Server. It belongs to an attribute of the NTFS file system of Windows. If the data in a large file contains a large amount of "0 data" (which should be viewed from the binary), such a file can be called a sparse file. If you look at this file from a two-dimensional diagram, you will find that the file is like many holes, and that is the origin of "sparse". The problem with this kind of file is wasted space. For example, if you are using VMware workstatation to create a virtual machine, initializing the disk size to 40G,VM will inevitably generate one or more. vmdk files for the virtual machine. If the file system really allocates 40G of physical disk space to the virtual machine disk files, it is actually a big waste because it doesn't work so much at all. There are several ways to solve the problem. The first is to allocate the entire full size space, then write 0 of the unused space, and then compress the file size with NTFS compression technology. While this approach solves the problem of wasted space, there is a cost to the process of compressing and extracting files from the cache before they are written to disk, which is costly for a system or application in which a file operation is busy, such as frequent compression and decompression operations.

The practice of processing this file in NTFS is to allocate space only if the file's content area block is not a large amount of 0 data. When the contents of the cache are checked out of 0 of chunks of data before they are written to disk, NTFS does not write this part of the data to disk, but maintains a list of its own, recording the location of the 0 data. In this way, when the read file operation occurs and reads to 0 data, NTFS only needs to return a small portion of the 0 data to the buffer for the read operation, without having to write the 0 data to the cache completely.

Reference documents:
Sparse Files

What is Sparse Files and why should I care as SQL Server DBA?

SQL Server->> Sparse file (sparse files)