Using SQL Server Segments

A segment is a named collection of disk pieces. It is a subset of one or more database devices that is available to a particular database. Using segments can increase SQL Server performance by giving you more control over where you place specific database objects to optimize the space available on a device. Specific segments can be allocated to specific tables or indexes.

Important With Windows NT-based servers, there are several ways to spread a database over multiple disks. Segments are one way, but the use of segments introduces administrative complexity and the possibility for error. In most cases, hardware-based RAID or Windows NT software-based RAID are recommended instead. You can achieve essentially the same performance advantages as segments by using RAID and letting the disk subsystem distribute the I/O load among the disks.

Each SQL Server database can use up to 32 segments. Selective use of segments can increase the performance of SQL Server by improving disk access time for read and write operations. For example, it is possible to split a table and its index(es) onto separate segments. This approach can be very effective in improving performance, but it requires a detailed understanding of how data and indexes are arranged and accessed.

While data striping (using Windows NT or a disk array) guarantees that the data is spread over all devices, when using SQL Server segments, this can occur only when the database is full and the data accessed is distributed across the segments.

The following illustration shows the relationship between segments, databases, and database devices.

Although you can have more than one segment on a database device, it isn't advised, because any objects that you place on those segments will compete with each other for space. It is best to specify one segment per device.

These are typical uses for segments:

Placing a table on one physical device and its nonclustered indexes on a different physical device.
Splitting a large, heavily-used table across database devices on two separate disk controllers.
Storing data for text and image columns on separate physical devices.
Placing a transaction log on a separate device so it can be dumped.

When a database is created, the following segments are automatically created for the database:



Segment name	Function


SYSTEM	Stores the system tables.
LOGSEGMENT	Stores the transaction log.
DEFAULT	Stores all other database objects ¾ unless you create additional segments. If you don't specify a segment when creating the table or by using sp_placeobject, the database objects are stored on this segment.

Additional segments can be added to a database after database devices have been allocated and assigned to the database.

When you add, extend, or delete a segment, you should also dump the master database.

Caution When you restore a database with the LOAD DATABASE statement, the segments and device fragments must be set up in the same way as the database that was dumped.

For more information, choose one of the following topics:

Adding Segments

Creating Database Objects on Segments

Extending Segments

Adding Objects to Another Segment

Displaying Information About Segments

Dropping Segments

Example of Using Segments

Example of Splitting a Table Across Segments