INF: Partitioning Logical Disks for Use with SQL Server

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

The following information discusses how hard disks should be partitioned to allow for optimal performance of database devices in SQL Server.

MORE INFORMATION

SQL Server acquires disk space from OS/2 when DISK INIT is executed by allocating a normal OS/2 file-system file of the specified size. The smallest increment is 1 megabyte (MB); this is true regardless of the size of the disk partition or file-system cluster size. SQL Server keeps track of how that space is used by the various tables and database objects through "allocation pages," which occur every 256th page. This scheme could allow DISK INIT to allocate an OS/2 file as small as 1/2 MB (256 2K pages); however, the smallest increment is 1 MB.

The allocation pages are used by SQL Server to keep track of how the disk space is being used by the various tables and database objects within SQL Server. As far as OS/2 and the file system are concerned, the space was allocated when the DISK INIT was executed and never changes.

There is no advantage to partitioning the hard disk so that it results in smaller cluster sizes because the space is statically allocated and never changes. The only performance concern is to minimize disk-head movement. Database devices should either be allocated on a newly formatted disk or compacted with a disk-organizer utility to be sure that all of the clusters of the database devices are physically contiguous on the disk. If multiple physical drives are available, the most heavily used data should be allocated on different database devices, which should in turn be placed on different physical drives. Putting different database devices on different logical drives does not improve performance.