Sybase Guru

Rule

1. Primary and Unique Key constraints are automatically indexed by the database when the constraint is enabled and cannot be indexed separately.
2. Foreign Key columns should only be indexed where they improve performance.
3. Additional non-unique indexes must only be created for specific performance reasons on the basis of demonstrated need.
4. Redundant Indexes must be removed.
5. Two indexes must not share the same leading edge, i.e. The same column(s) as the first part(s) of the index.
6. Indexes must be named <table name>_<index type><seq> where <index type> is: ‘PK’, ‘AK’, ‘FK’ or ‘IK’ and <table name> is truncated to 25 characters.  Note: ‘PK’ refers to a Primary Key index,  ‘FK’ refers to a Foreign Key index, ‘AK’ refers to an Alternate Key index,  ‘IK’ refers to a non-unique index.
7. Indexed columns must be specified in order of decreasing selectivity, i.e. the first column in the index should have the highest number of distinct values. Note: This is to enhance performance.
8. Columns with only a few distinct values relative to the total number of records must not be indexed. Bitmap Indexes should be used only when there is little or no update activity and tables have low cardinality.  Note: A serial scan through the table is faster.

Guidelines

1. A further consideration to the ordering of columns in indexes is that it may be possible to cover a number of  indexing requirements through only one index.
2. The indexing of frequently updated columns should be carefully considered as the cost of maintaining the index may outweigh the benefits of the index.  Note: If a column is frequently updated then having an index on it will impact performance because the index will also need to be maintained and this may not be acceptable.

Standards to create the constraints

Rule

1. Each table must have one or more unique identifiers. At least one must be implemented as a primary key.
2. The unique identifier must have true business meaning where possible, and not simply be a generated sequence number. A surrogate key may be used where performance considerations would warrant it.
3. If relationships exist between tables then they should be enforced using a foreign key constraint (unless triggers are being used to enforce advanced RI)
4. Check constraints must be used to restrict the allowable values of columns for appropriate business rules where the list of values is relatively small.

Guidlines

1. Primary keys constraints should be named PK_<table name>
2. Alternate/unique constraint should be named AK_<table name>_<seq>
3. Foreign keys constraints should be named FK_<table name>_<seq>
4. Default constraints should be named DF_<constraint name>
5. Check constraints must be named CC_<constraint name>

Standard to create the columns:

1. The column name should be of the form XX<Abbr/Name><suffix> (XXX represents the abbreviation of the Table Name.It should be a meaningful abbreviation of the table name to which the column belongs.<suffix> is a meaningful suffix).
2. Column name should  not be  longer than 30  Alphanumeric characters.
3. Column name (<Abbr/Name> only) should be same throughout an  application even though the field may be present in more than one table.

The following suffixes can be used for better understanding.

Standards to create the tables:

1. The table name should be the same as the logical entity name or should bear some significance to the data stored therein.
2. The table must have a primary key that uniquely identifies each row.
3. The column names will be same as logical attribute name.
4. Columns must be defined as NOT NULL wherever possible.
5. Table names should be no more than 30 alphanumeric characters.

For Examples creating the tables : AccountMaster , CashBalance , TransactionDetail

Guidelines to create the tables:

1. Keep table names to around 20 characters. This will prevent the truncation of table name in the naming of other objects that contain the table name as part of the object name. e.g. Indexes
2. Columns may only be denormalized to improve performance, but this should be very carefully balanced against the additional update that may be required.

 General Naming Conventions

1.  Names must be a maximum of 30 characters long.
2. Unless absolutely necessary only alphanumeric characters and underscores should be used.
3. Each word in a name must be separated by an underscore.
4. Names are not to be considered case-sensitive.
5. A name cannot be a Sybase SQL Server Reserved Word or Keyword. (Please refer to the Sybase Adaptive Server Reference manual for a full list)
6. A database object name must be unique across the database

A default installation of Sybase ASE has a small tempdb located on the master device. Almost all ASE implementations need a much larger temporary database to handle sorts and worktables and therefore DBA’s need to increase tempdb.

This document gives some recommendations how this could be done and describes various techniques to guarantee maximum availability of tempdb.

Content
• About Segments
• Prevention of a full logsegment
• Prevention of a full segment for data
• Separation of data and log segments
• Using the dsync option
• Moving tempdb off the master device
• Summary of the recommendations

About Segments
Tempdb is basically just another database within the server and has three segments: ’system’ for system tables like sysobjects and syscolumns, ‘default’ to store objects such as tables and ‘logsegment’ for the transaction log (syslogs table). This type of segmentation, no matter the size of the database, has an undefined space for the transaction log; the only limitation is the available size within the database. The following script illustrates that this can lead to nasty problems.

create table #a(a char(100) not null)
go
declare @a int

Running the script populates table #a and the transaction log at the same time, until tempdb is full. Then the log gets automatically truncated by ASE, allowing for more rows to be inserted in the table until tempdb is full again. This cycle repeats itself a number of times until tempdb is filled up to the point that even the transaction log cannot be truncated anymore. At that point the ASE errorlog will show messages like  “1 task(s) are sleeping waiting for space to become available in the log segment for database tempdb”.

When you log on to ASE to resolve this problem and you run an sp_who, you will get “Failed to allocate disk space for a work table in database ‘tempdb’. You may be able to free up space by using the DUMP TRANsaction command, or you may want to extend the size of the database by using the ALTER DATABASE command.”.

Your first task is to kill off the process that causes the problem, but how can you know which process to kill if you even can’t run an sp_who? This problem can be solved with the lct_admin function. In the format lct_admin(“abort”,0,) you can kill sessions that are waiting on a log suspend. So you do a:

select @a = 1

while @a > 0
begin
insert into #a values(“get full”)
end
go

select lct_admin(“abort”,0,2) –- 2 is dbid for tempdb.

When you execute the lct_admin function the session is killed but tempdb is still full. In fact it’s so full that the table #a cannot be dropped because this action must also be logged in the transaction log of tempdb. Besides a reboot of the server you would have no other option than to increase tempdb with just a bit more space for the logsegment.

alter database tempdb log on <some_device> = <number of Mb’s>

This extends tempdb and makes it possible to drop table #a and to truncate the transaction log.

In a real-life situation this scenario could cause significant problems for users.

Prevention of a full logsegment
One of the database options that can be set with the sp_dboption stored procedure can be used to prevent this. When you do:

sp_dboption tempdb,”abort tran on log full”,true

(for pre 12.5.1: followed by a checkpoint in tempdb) the transaction that fills up the transaction log in tempdb is automatically aborted by the server.

Default or system segments are full
The default or system segments in tempdb, where the actual data is stored, can also get full, just like any ordinary database. Your query is cancelled with a Msg 1105 “Can’t allocate space for object ‘#a_____00000180017895422′  in database ‘tempdb’ because ‘default’ segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase size of the segment”.

This message can be caused by a query that creates a large table in tempdb, or an internal worktable created by ASE used for sorts, etc.
Potentially, this problem is much worse than a full transaction log since the transaction is cancelled. A full log segment leads to “sleeping” processes until the problem is resolved. However, a full data segment leads to aborted transactions.

Prevention of a full segment for data
The Resource Governor in ASE allows you to deal with these circumstances. You can specify just how much space a session is allowed to consume within tempdb. When the space usage exceeds the specified limit the session is given a warning or is killed. Before using this feature you must configure ASE to use the Resource Governor:

sp_configure “allow resource limits”,1

After a reboot of the server (12.5.1. too) you can use limits:

sp_add_resource_limit “petersap”,null,”at all times”,”tempdb_space”,200

This limit means that the user petersap is allowed to use 200 pages within tempdb. When the limit is exceeded the session receives an error message (Msg 11056) and the query is aborted. Different options for sp_add_resource_limit make it possible to kill the session when the limit is exceeded.
Just how much pages a user should be allowed to use in tempdb depends on your environment.
Things like the size of tempdb, the number of concurrent users and the type of queries should be taken into account when setting the resource limit. When a resource limit for tempdb is crossed it is logged into the Sybase errorlog. This makes it possible to trace how often a limit is exceeded
and by who. With this information the resource limit can be tuned.
When you use multiple temporary databases the limit is enforced on all of these.

Separation of data and log segments
For performance reasons it makes sense to separate the system+default and the logsegment from each other. Not all sites follow this policy. It’s a tradeoff between flexibility to have data and log combined and some increased performance. Since tempdb is a heavily used database its not a
bad idea to invest some time into an investigation of the space requirements.

The following example illustrates how tempdb could be configured with separate devices for the logsegment and the data. The example is based on an initial setting of tempdb on the master device.

First we increase tempdb for the system and data segments:

alter database tempdb on <your data device> = <number of Mb>

Then we extend tempdb for the transaction log:

alter database tempdb log on <your log device> = <number of Mb>

When you have done this and run an ‘sp_helpdb tempdb’ you will see that data and log are still on the same segment. Submit the following to resolve this:

sp_logdevice tempdb, <your log device>

Please note that tempdb should not be increased on the master device.

Using the dsync option
The dsync option for devices allows you to enable/disable I/O buffering to file systems. The option is not available for raw partitions and NT files. To get the maximum possible performance for tempdb use dedicated device files, created with the Sybase disk init command. The files should be
placed on file system, not on raw partitions. Set the dsync option off as in the following example:

disk init name = “tempdb_data”,  size= “500M”,
physname= “/var/sybase/tempdb_data.dat”,
dsync = false

Moving tempdb off the master device
When you have increased tempdb on separate devices you can configure tempdb so that the master device is unused. This increases the performance of tempdb even further. There are various techniques for this, all with their pros and cons but I recommend the following.
Modify sysusages so that segmap will be set to 0 for the master device. In other words, change the segments of tempdb so that the master device is unused. This can be done with the following statements:

sp_configure “allow updates to system tables”,1
go
update master..sysusages
set segmap = 0
where dbid = 2
and lstart = 0
go
sp_configure “allow updates to system tables”,0
go
shutdown -- reboot now!
go

When you use this configuration you should know the recovery procedure just in case one of the devices of tempdb gets corrupted or lost. Start your ASE in single user mode by adding the –m switch to the dataserver options. Then submit the following statements:

update master..sysusages set segmap = 7
where dbid = 2
and lstart = 0
go
delete master..sysusages
where dbid = 2
and lstart > 0
go
shutdown -- reboot now!
go

Remove the –m switch from the dataserver options and restart ASE. Your tempdb is now available with the default allocation on the master device.

Summary of the recommendations
• Set the option “abort tran on log full” for tempdb to on
• Create resource limits
• Place data and log segments on separate devices
• Place tempdb on filesystem with dsync set to false
• Move tempdb off the master device by modifying the segmap attribute