A segment tutorial

The following tutorial shows how to create a user segment and how to remove all other segment mappings from the device. The examples in this section assume a server using 2K logical page sizes.

When you are working with segments and devices, remember that:

The tutorial begins with a new database, created with one device for the database objects and another for the transaction log:

create database mydata on bigdevice = "5M"
     log on logdev = "4M"

Now, if you use mydata, and run sp_helpdb, you see:

sp_helpdb mydata
name       db_size  owner     dbid   created      status
---------- -------- --------- ------ ------------ ---------------
mydata       9.0 MB sa             5 May 27, 2005  no options set

device_fragments  size    usage      created              free kbytes
----------------  ------  ---------- -----------          -----------
bigdevice         5.0 MB  data only  May 25 2005 3:42PM   3650
logdev            4.0 MB  log only   May 25 2005 3:42PM   not applicable
------------------------------------------------------------------------
log only free kbytes = 4078
device                 segment
---------------------- ----------------------
bigdevice              default
bigdevice              system
logdev                 logsegment
(return status = 0)

Like all newly created databases, mydata has the segments named default, system, and logsegment. Because create database used log on, the logsegment is mapped to its own device, logdev, and the default and system segments are both mapped to bigdevice.

If you add space on the same database devices to mydata, and run sp_helpdb again, you see entries for the added fragments:

use master
alter database mydata on bigdevice = "2M"
    log on logdev = "1M"
use mydata 
sp_helpdb mydata
name       db_size  owner     dbid   created       status
---------- -------- --------- ------ ------------  ---------------
mydata     12.0 MB  sa             4 May 25, 2005  no options set

device_fragments  size     usage      created                free kbytes
----------------- -------- ---------- -----------------      -----------
bigdevice         5.0 MB   data only  May 25 2005 3:42PM            2048
logdev            4.0 MB   data only  May 25 2005 3:42PM     not applicable
data only         2.0 MB   log only   May 25 2005 3:55PM            2040
log only          1.0 MB   log only   May 25 2005 3:55PM     not applicable
---------------------------------------------------------------------------
log only free kybytes = 5098
device                 segment
---------------------- ----------------------
bigdevice              default
bigdevice              system
logdev                 logsegment

Always add log space to log space and data space to data space. Adaptive Server instructs you to use with override if you try to allocate a segment that is already in use for data to the log, or vice versa. Remember that segments are mapped to entire devices, and not just to the space fragments. If you change any of the segment assignments on a device, you make the change for all of the fragments.

The following example allocates a new database device that has not been used by mydata:

use master 
alter database mydata on newdevice = 3 
use mydata 
sp_helpdb mydata
name       db_size  owner     dbid   created      status
---------- -------- --------- ------ ------------ ---------------
mydata      15.0 MB sa             5 May 25, 2005  no options set

device_fragments   size      usage       created             free kbytes
------------------ --------- ----------- ------------------  --------------
bigdevice          5.0 MB    data only   May 25 2005 3:42PM            3650
logdev             4.0 MB    log only    May 25 2005 3:42PM  not applicable
bigdevice          2.0 MB    data only   May 25 2005 3:55PM            2040
logdev             1.0 MB    log only    May 25 2005 3:55PM  not applicable
newdevice          3.0 MB    data only   May 26 2005 11:59AM           3060
----------------------------------------------------------------------------
log only free kbytes = 5098
device           segment
---------------- ----------
bigdevice        default
bigdevice        system
logdev           logsegment
newdevice        default
newdevice        system

The following example creates a segment called new_space on newdevice:

sp_addsegment new_space, mydata, newdevice 

Here is the portion of the sp_helpdb report which lists the segment mapping:

device                         segment
---------------------------- ------------------
bigdevice                      default
bigdevice                      system
logdev                         logsegment
newdevice                      default
newdevice                      new_space
newdevice                      system

The default and system segments are still mapped to newdevice. If you are planning to use new_space to store a user table or index for improved performance, and you want to ensure that other user objects are not stored on the device by default, reduce the scope of default and system with sp_dropsegment:

sp_dropsegment system, mydata, newdevice 
sp_dropsegment "default", mydata, newdevice 

You must include the quotes around “default”; it is a Transact-SQL reserved word.

Here is the portion of the sp_helpdb report that shows the segment mapping:

device                         segment
---------------------------- --------------------
bigdevice                      default
bigdevice                      system
logdev                         logsegment
newdevice                      new_space

Only new_space is now mapped to newdevice. Users who create objects can use on new_space to place a table or index on the device that corresponds to that segment. Since the default segment is not pointing to that database device, users who create tables and indexes without using the on clause are not placing them on your specially prepared device.

If you use alter database on newdevice again, the new space fragment acquires the same segment mapping as the existing fragment of that device (that is, the new_space segment only).

At this point, if you use create table and name new_space as the segment, you get results like these from sp_helpsegment:

create table mytabl (c1 int, c2 datetime) 
    on new_space
sp_helpsegment new_space

segment    name                           status
------- ------------------------------    ------
      3    new_space                           0

device           size        free_pages
---------------- ----------- -----------
newdevice        3.0MB             1523

Objects on segment ‘new_space’:

table_name        index_name         indid        partition_name
----------------- ------------------ --------     ----------------
mytabl            mytabl                    0     mytabl_400001425

Objects currently bound to segment ‘new_space’:

table_name      index_name     indid

total_size    total_pages    free_pages  used_pages   reserved_pages
------------- -----------    ----------- -----------   --------------
3.0MB                1536           1523          13                0