Segments

Using Device's and Database's the SA can specify where the database needs to be created.

If the database is on more than one device and an object is created it could be created on any of the available devices

A segment is named collection of one or more devices assigned to a given database ,When a segment is created we can place the table and indexes on the segment .

or

Segment is a label that points to one or more database device .

Each Database has 3 segments

Segmap values are stored in sysusages

• System - 1
• Default - 2
• Log - 4

User Defined Segments

These can be created using the following syntax

sp_addsegment segname,dbname,devname

sp_dropsegment segname,dbname

Before dropping the segment the object on the segment must be dropped or moved to a different segment

Moving the Object to a new segment

Need to be in current database while creating

Syntax : sp_placeobject segname,objname

Viewing Segments

It displays information about the segment and the object on that segment

Syntax : sp_helpsegement <segname>

Using Segments we can improve performance

• Placing heavily used objects on a physical disks dedicated to those objects
• Can Split large tables across several disks
• Can improve the performance by placing
  • a table and its Clustered index on one physical device table and
  • Its NON-Clustered indexes on a second physical devices and
  • transaction log on 3^rd physical device

System tables related to segments

• Out of 4 → 2 will be sysusages and sysdevices will be affected in MASTER DB

                         → 2 will be syssegments and sysindexes will be affected in USER DB

• Database can have a max 32 segments
  • 0,1,2 → System Defined Segments
  • 3 to 32 → User Defined Segments

*** What if there is not enough diskspace ?

→ Extend the segment to another device

*** If Segment gets full ?

→ The segment gets full means that the device of segment is getting full

→ Segment is a piece of device if the server shows segment full i.e. that device is full

Dropping the segments:

By default, the system, default, and logsegment segments for tempdb include its 2MB allocation on the master device.

1> use tempdb

2>go

1>sp_helpsegment

2>go

When you allocate new devices to tempdb, they automatically become part of all three segments. Once you allocate a second device to tempdb, you can drop the master device from the default and logsegment segments. This way, you can be sure that the worktables and other temporary tables in tempdb do not contend with other uses on the master device.

To drop the master device from the segments:

Alter tempdb onto another device, if you have not already done so.

Example: Alter database tempdb on tune3 = 20

Issue a use tempdb command, and then drop the master device from the segments:

sp_dropsegment "default", tempdb, master

go

sp_dropdegment system, tempdb, master

go

sp_dropdegment logsegment, tempdb, master

go

To verify that the default segment no longer includes the master device, issue this command:

select dbid, name, segmap from sysusages, sysdevices

where sysdevices.low <= sysusages.size + vstart

and sysdevices.high >= sysusages.size + vstart -1

and dbid = 2

and (status = 2 or status = 3)

The segmap column should report “1” for any allocations on the master device, indicating that only the system segment still uses the device:

dbid name segmap

------ --------------- -----------

2 master 1

2 tune3 7

Even after droping segments ,they will be stored in MASTER but not used

Some System Stored Procedures

sp_configure this gives entire list of options that were ser by default ASE server

sp_configure “number of devices”,15

go

sp_configure “max memory ”51200 (2K pages for 100M)

go

sp_helpdevice virtual system table is master

sysdatabases contains a row for every newly created database

sysusages contains the segment map value for every newly created database

sysdevices contains row for each and every newly initlized devices

syslogins contains a row for every login created in sybase server

by default two logins

• SA (master database)
• Probe (sybsystem database)

syslogshold contisn the information for the longest holding transaction

sysprocess contains row for every process running on the server

*the process from “ 2-10 ” are the system process ,which cannot be KILLED

select spid,cmd,status,suid from sysprocess

go

sysroles select name from sysobjects where name like “%role” contains information regarding the server

select * from sysloginroles

select * from syssrvroles

*system and user defined roles are there in system roles

System tables present in all databases :

• sysobjects contains row for every object created in the database
• sysusers contains row for every user added to the No of databases
• sysalternates contains row for every user aliased in that database
• sysprotects contains the information regarding the permission on objects with respect to users

By default Database id of

MASTER – 1 TEMPDB – 2 MODEL – 3

To check how many databases created

sp_helpdb

go

To check whether device created or not

sp_helpdevice <device name >

go

To get all devices list

sp_helpdevice

go

To drop a device

sp_dropdevice <devicename>

go

sp_dropdevice device01

go

* we can drop a device if there are no databases or logins

*If we want to drop the device ,after creation of database then we need to drop LOGIN and DATABASE and then DEVICE. Then only we can drop the device created .