Example of Using Segments

Segments provide a flexible way to assign objects to a particular database device. For example, you can add a database device to a database, and then improve your system performance by assigning a particular high-use table to that database device. To improve performance, no other database objects should use the new segment.

The preceding topics presented the individual tasks performed to manage segments. The following example puts together some of these individual tasks to show you how to create a segment and remove all other segment mappings from the database device:

Create a new database called mydata with one database device for objects and another for the transaction log:
```
create database mydata on bigdevice = 4 log on logdev = 2
```
Switch to mydata, and run sp_helpdb to view the segments the database contains:
```
use mydata
<execute>
sp_helpdb mydata
```

name	db_size	owner	dbid	created	status
-----	---------	-----	-------	---------	-------
mydata	6 MB	SA	4	May 5 1995	no options set

device_fragments	size	usage
-------------------	------------	--------------
bigdevice	4 MB	data only
logdev	2 MB	log only

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

Like all newly created databases, mydata has segments named DEFAULT, SYSTEM, and LOGSEGMENT. Since the CREATE DATABASE statement used LOG ON, the transaction log segment is mapped to its own device (logdev). The DEFAULT and SYSTEM segments are both mapped to bigdevice.

Expand the space allocated for mydata on the database devices, and run sp_helpdb again to display the entries for the added space:
```
use master 
<execute>
alter database mydata on bigdevice = 2, logdev = 1 
<execute>
use mydata
<execute>

sp_helpdb mydata
```

name	db_size	owner	dbid	created	status
--------	---------	------	------	-----------	------------
mydata	9 MB	SA	4	May 5 1995	no options set

device_fragments	size	usage
----------------	------	-----------------------
bigdevice	2 MB	data only
bigdevice	4 MB	data only
logdev	1 MB	log only
logdev	2 MB	log only

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

When additional fragments of disk space are allocated to a database on a device, the segments from the other fragments of the database device are mapped to the new fragments. Segments are mapped to the entire amount of space on a device used by the database, not just to the space fragments. If fragments are added, all segments are mapped to those fragments.

When a new database device is used (one that's not already in use by the database), the SYSTEM and DEFAULT segments are automatically mapped to the new device. Step 4 allocates a new database device that has not been used by mydata.

Allocate space for mydata on a new database device, and then run sp_helpdb again:

use master
<execute>    
alter database mydata on newdevice = 3 
<execute>

use mydata 
<execute>

sp_helpdb mydata

name	db_size	owner	dbid	created	status
------	--------	-----	---------	----------	-------------
mydata	12 MB	SA	4	Dec 2 1991	no options set

device_fragments	size	usage
----------------------	-----	------------
bigdevice	2 MB	data only
bigdevice	4 MB	data only
logdev	1 MB	log only
logdev	2 MB	log only
newdevice	3 MB	data only

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

The DEFAULT and SYSTEM segments are mapped to the new space. In some cases, you might want to use the new space as default storage space for CREATE TABLE or CREATE INDEX statements. However, if you are adding space to assign a table or index to a specific segment (and therefore to specific devices), you will want to add your own segment and then drop the SYSTEM and DEFAULT segments.

Create a new segment, NEW_SPACE, on NEWDEVICE:
```
sp_addsegment new_space, newdevice
```
Here is just the portion of the sp_helpdb report that has changed (the portion that lists segment mapping):

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

Note that the DEFAULT and SYSTEM segments are still mapped to NEWDEVICE. If you are planning to use NEW_SPACE to store a specific user table or index for improved performance and you want to ensure that other user and system objects are not stored on the database device you just created, use sp_dropsegment to drop the DEFAULT and SYSTEM segments.

Drop the SYSTEM and DEFAULT segments:

sp_dropsegment system, newdevice 
<execute>    
sp_dropsegment 'default', newdevice

The quotation marks around DEFAULT are needed because it is a 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

Now only NEW_SPACE is mapped to NEWDEVICE. Users who create objects can use ON NEW_SPACE to place a table or index on the database device that corresponds to that segment (NEWDEVICE). Since the DEFAULT segment is not using that database device, users who create tables and indexes without using the ON clause will not be placing them on NEWDEVICE.

If the ALTER DATABASE statement is used on NEWDEVICE again, the new fragment acquires the same segment mapping as the existing fragment of that database device ¾ the NEW_SPACE segment only.

At this point, if you use CREATE TABLE and name NEW_SPACE as the segment, you will get results such as the following from sp_help and sp_helpsegment:

create table mytable (c1 int, c2 datetime) on new_space 

sp_help mytable
Name      Owner         Type           When_created   
--------  ------------- -------------  --------------------   
mytable   dbo           user table     May 05 1995   3:21PM


Data_located_on_segment  
----------------------    
new_space                


Column_name  Type      Length  Prec   Scale  Nullable
-----------  ------    ------  -----  -----  ------------
c1           int       4                     yes   
c2           datetime  8                     yes


Identity                       Seed      Increment
-----------------------------  --------  -----------
No identity column defined     (null)    (null)

Object does not have any indexes.

constraint_type    constraint_name    constraint_keys
-----------------  -----------------  -----------------

FOREIGN KEYS
No defined keys for this object.


sp_helpsegment new_space

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


device         size   
---------      --------------
newdevice      3MB


table_name   index_name  indid 
-----------  ----------- -------   
mytable      mytable     0

For more information about the system stored procedures and Transact-SQL statements used in this example, see the Microsoft SQL Server Transact-SQL Reference.