In this post, we’re going to focus on read-ahead reads. For more details on the varying IO sizes SQL Server can use check out this post, What is SQL Server’s IO Block Size?. The size of IOs issued by SQL Server is variable based on the operation performed, IOs can be anywhere from 512 bytes up to 8MB. But there’s more…let’s keep digging into that concept… SQL Server Issues IO in Variable Sizes This used to be a big deal when disks would spin because the head would have to move and the platter had to rotate to the appropriate sector on disk and then read a range of pages in one disk transaction. The primary reason why SQL Server cares about NTFS Allocation Unit Size is when a 64KB Extent is created and written to disk, a 64KB NTFS Allocation Unit guarantees that all eight pages inside the Extent are physically written into one NTFS AU and are physically adjacent on disk. The NTFS Allocation unit is the unit of allocation for blocks/clusters in the file system and is composed of ranges of pages or sectors on a disk. SQL Server does not perform all IOs in Extent sized IOs. It is often thought that SQL Server performs all disk IO in Extent size operations and that’s why 64KB NTFS Allocation Units are considered best practice. An Extent is a 64KB data structure representing a contiguous sequence of eight 8KB pages. Inside of SQL Server, there’s also a data structure used for allocation, the Extent. Why Does SQL Server Care About NTFS Allocation Unit Size? Image Source: “Modern Operating Systems” 4th Edition. And 64KB is considered best practice for SQL Server…but why? Let’s keep digging… The block allocator of the file system will try to ensure blocks are physically adjacent on the disk and groups them together in runs.Ī 4KB NTFS Allocation Units size is considered best practice on general-purpose file systems. The MFT data structure tracks which blocks make up a file. As the file grows, more blocks/clusters are allocated to represent the file. Since a block is a unit of allocation, if a file is between 1 byte and the file system’s allocation unit size, it will take up exactly one block/cluster on the file system. By default, it is 4KB and can be as large as 2MB. On NTFS, this is referred to as the NTFS Allocation Unit Size and is a configurable attribute of the file system. ![]() A block/cluster is also the atomic allocation unit from a file system and has a configurable size. A block, also referred to as a cluster in Windows, is an abstraction over one or more physical structures (sectors or pages depending on the media) presented by the underlying disk. In Figure 1, you can see an MTF record has several sections describing the metadata about the file and pointers to blocks that make up the file. How NTFS Stores Data on DiskĪ Master File Table (MFT) is the data structure that describes files and directories on NTFS. This blog post shows you how NTFS stores data, what the NTFS Allocation Unit means, and how SQL Server performs IOs of variable size.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |