Placing Existing Objects on Segments

sp_placeobject does not remove an object from its allocated segment. However, it causes all further disk allocation for that object to occur on the new segment it specifies.

For example, for all further disk allocation for the mytab table to take place on bigseg, use:

sp_placeobject bigseg, mytab

sp_placeobject does not move an object from one database device to another. Any pages allocated on the first device remain allocated; any data written to the first device remains on the device. sp_placeobject affects only future space allocations.

After you have used sp_placeobject, if you then execute dbcc checkalloc you see this message for each object that is split across segments:

Extent not within segment: Object object_name, indid index_id includes extents on allocation page page_number which is not in segment segment_name.

You can ignore this message.

See the Reference Manual: Procedures.

Split large tables across segments that are located on separate disk controllers to improve performance for high-volume, multiuser applications.

The order of steps is important; in particular, create the clustered index before you place the table on the second segment.

This figure summarizes how to split a table across two segments on a server using a 2K logical page size:

Splitting a Large Table Across Two Segments
Graphic describing the steps required for splitting a table across segments. The steps are listed directly below the graphic.

Graphic describing the steps required for splitting a table across segments. The steps are listed directly below the graphic.

The balance of disk allocation may change over time if the table is updated frequently. To guarantee that the speed advantages are maintained, you may need to drop and re-create the table.