Microsoft KB Archive/39154

INF: SQL Server Disk-Space Management, Part 2 ID Number: Q39154

1.00 1.10 1.11 4.20 OS/2

Summary:

This is part 2 of the SQL Server disk-space management discussion. Part 1 of the discussion described how SQL Server acquires disk space and how it creates databases and assigns them groups of pages.

Part 2 below covers the segmentation of global page-number space and the relationship of the “virtual device number” (VDEVNO) and low and high global page numbers to the database device.

More Information:

The address space for database global page numbers is segmented by interpreting the 32-bit global page number as two separate components. The high-order 8 bits is the VDEVNO and the remaining 24 bits is the relative page number from the beginning of that device. This is similar to segmented addressing schemes in operating systems that use a segment number and offset within the segment. The following are examples:

0x01000003 is page 3 on device 1 0x02000003 is page 3 on device 2

This is why virtual device number 1 has a starting page number of 16777216 (0x01000000 = 16777216).

The segmentation scheme allows database devices in the middle of the global page space to be expanded without affecting the page addresses of existing data. For example, device 1 could be initially allocated as 2 megabytes (giving it a starting page number of 16777216 and an ending page number of 16778240). Then, device 2 is added, also 2 megabytes in size (33554432 to 33555456). Later, it becomes necessary to increase the size of device 1. There is no problem because each device has a page space of 16 million pages reserved for it. This does mean that each database device is limited to 32 gigabytes (16 million pages times 2048 bytes per page).

The discontinuous global page space is mapped into a contiguous local page space for each database by the entries in SYSUSAGES. Each entry associates a contiguous block of global pages (not necessarily an entire database device) with a range of local pages for a particular database. This is done by recording the starting global page number, the starting local page number, and the number of pages.

The local page space completely insulates each database from the complexity of the discontinuous global page-numbering system and allows each database to refer to its pages as though they were consecutively numbered starting with 0. Databases are expanded by adding contiguous blocks of pages to the end of local page space.