ID Number: Q36963
1.00 1.10 1.11 4.20
OS/2
Summary:
The following information discusses how SQL Server manages disk space.
This is the part 1 of the discussion.
More Information:
SQL Server acquires disk space from the operating system by allocating
one or more OS/2 disk files. Each of these files can be thought of as a
segment of disk space that is reserved for use by the SQL Server. They
are allocated in increments of 1 megabyte (up to the maximum file size
permitted by the version of OS/2 being used) and are fixed in length.
Together, these disk space segments compose a pool of disk space that
can be assigned to individual databases. If there is not enough space
in the pool to satisfy a particular CREATE DATABASE or ALTER DATABASE
request, another disk segment can be added dynamically with DISK INIT.
A segment may contain several databases and a database may be
allocated on several segments. If disk-space requirements are static,
or if the machine is dedicated, a single large segment is best. If
space requirements cannot be predicted, if the disk is shared by
several applications that compete for space, or if multiple physical
disks (or partitions) exist, multiple segments are best.
A single large segment, allocated when the disk is relatively empty,
will probably perform better than many small segments scattered over
the disk; however, if multiple physical disk drives are available,
spreading the segments over the multiple drives will give better
performance.
The first disk segment contains initialization information and must
exist before the SQL Server can be started. This segment is built
during the installation process and utilities are provided to rebuild
it if necessary (BLDMASTR and the SQL scripts INSTMSTR, INSTMSGS,
INSTMODL, INSTPUBS). This segment contains the Master Database, Model
Database, etc., and may contain user data as well. The default size of
the master disk segment is 10MB on 1.1x servers and 15MB on 4.2 servers,
which is enough for the Master, Model, Temp, and PUBS databases, but
not for user data. Although it is possible to make the master disk
segment large enough for user data (by deleting and rebuilding),
it is better to keep system data on the first segment and put user data
on additional segments.
Subsequent disk space segments are added with DISK INIT, which creates
an OS/2 file of the specified size and adds an entry to the SYSDEVICES
table containing the logical name of this segment, the physical name
by which it is known to the operating system, and the size in 2K
pages.
Each 2K page in the disk space pool is identified by a unique number.
Each disk space segment contains a contiguous sequential range of page
numbers; the corresponding SYSDEVICES entry contains the starting page
number of each segment, which specifies its position in the "global
page space."
A database can only use the disk space that has been previously
assigned to it by the CREATE DATABASE or ALTER DATABASE commands.
These commands add entries to the SYSUSAGES table that identify groups
of pages by specifying the starting page number and number of pages,
as well as the database to which they are assigned. Since each entry
in SYSUSAGES can refer to only a single group of contiguous page
numbers, multiple entries may be required to satisfy a single CREATE
or ALTER DATABASE.
Parameters on the CREATE or ALTER DATABASE commands specify whether
the space is to be allocated from particular disk segments (by logical
name) or from any of the disk segments that have been marked as
"default" segments by the stored procedure "sp_diskdefault".
To find free disk space, SQL Server compares the list of available
segments and sizes (SYSDEVICES) with the list of pieces of those
segments that are already assigned (SYSUSAGES) and finds the smallest
free piece (or pieces) that will satisfy the request.
Once a group of pages is assigned to a database, the group is
available for use by objects within that database. This more detailed
level of disk-space management will be the subject of part 3 of this
discussion. The segmentation of global page numbers and mapping of
global page numbers to local page numbers will be covered in part 2.