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. The syntax is:
sp_placeobject segname, objname
The following command causes all further disk allocation for the mytab table to take place on bigseg:
sp_placeobject bigseg, mytab
sp_placeobject does not move an object from one database device to another. Whatever pages have been allocated on the first device remain allocated; whatever data was written to the first device remains on the device. sp_placeobject affects only future space allocations.
To completely move
a table, you can drop its clustered index (if one exists), and create
or re-create a clustered index on the desired segment. To completely
move a nonclustered index, drop the index and re-create it on the
new segment. See “Creating clustered indexes on segments” for instructions on moving a
table.
After you have used sp_placeobject, executing dbcc checkalloc causes the following message to appear 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.
Performance can be improved for high-volume, multiuser applications when large tables are split across segments that are located on separate disk controllers.
The order of steps is quite important at certain stages. In particular, you must create the clustered index before you place the table is placed on the second segment.
Figure 23-4 summarizes the process of splitting a table across two segments on a server using 2K logical page size:
Figure 23-4: Splitting a large table across two segments
Begin by using the master database.
Initialize the devices with disk init.
Assign both devices to the mydata database with alter database.
Change to the mydata database by entering the use database command.
Create three segments. The first two should each point to one of the new devices. Extend the scope of the third segment so that it labels both devices.
Drop the system and default segments from both devices.
Create the table and its clustered index on the first segment.
Load half of the table’s data onto the first segment.
Use sp_placeobject to cause all further allocations of disk space to occur on the second segment.
Load the remaining data onto the second segment.
Use sp_placeobject again to place the table on the segment that spans both devices.
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 at some point.
