Data Storage in SQL Server


Within the data files, SQL Server stores data in 8KB blocks of contiguous disk space, known as pages . The page is the minimum unit of IO when SQL Server transfers data to and from disk. Each of these data pages has the capacity to store up to 8060 bytes of data (8192 “132 for overhead), and a single row cannot span more than one page. This is an important consideration when calculating disk usage. For example, if a table has an average row size of 4050 bytes, only one row would fit per page, essentially doubling the disk space required to store the data.

In an effort to reduce internal operations and increase IO efficiency, SQL Server, when allocating space to a table or index, allocates space in extents. An extent is eight contiguous pages, or 64KB of storage. There are actually two types of extents. Every table or index is initially allocated space in a mixed extent . As the name implies, mixed extents store pages from more than one object. When an index or table is first created, it is assigned an Index Allocation Map, or IAM, which is used to track space usage for the object, and at least one data page. The IAM and data page are assigned to a mixed extent in an effort to save space, as dedicating an extent to a table with a few small rows would be wasteful . Up to eight initial pages will be assigned this way. When an object requires more than eight pages of storage, all further space will be allocated from uniform extents. Uniform extents store pages for only a single index or table. This allows SQL Server to optimize read and write operations and reduce fragmentation because the data will be stored in units of 64KB (eight pages), as opposed to individual 8KB pages scattered throughout the data file.

For more detailed information on the internal storage structures and how they are managed in SQL Server databases, see Chapter 33, "SQL Server Internals."



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net