Special considerations

When you create a database in Adaptive Server, you can assign its storage to one or more data storage devices (see Chapter 7, “Initializing Database Devices in System Administration Guide: Volume 1). Information about these devices is stored in master.dbo.sysdevices. Declare which device to use for the database, and how much of each device this database uses. A database can occupy all available space on the device, or other databases can share space on the device, or any combination of the two. Segments (logical groupings of storage within a database) allow you to keep some data logically or physically separate from other data. For example, to aid in disaster recovery, Sybase strongly recommends that you physically separate the transaction log from other data within a database.

Logical and physical data groupings can help your database perform better. For example, you can reserve part of the database for a data set that you know will grow much larger over time by assigning this data set, and no other, a particular segment. You can also physically separate heavily used indexes from their data to help prevent disk “thrashing,” which slows down read and write response times.

NoteFor Adaptive Server, devices provide a logical map of a database to physical storage, while segments provide a logical map of database objects to devices. To achieve your space allocation goals, it is important that you understand the interplay between these logical layers.

Each database can have up to 32 named segments. Adaptive Server creates and uses three of these segments:

You can store user tables in the system segment, but the logsegment is reserved entirely for the log.

Adaptive Server keeps track of the various pieces of each database in master.dbo.sysusages. Each entry in sysusages describes one fragment of a database. Fragments are a contiguous group of logical pages, all on the same device, that permit storage for the same group of segments. Fragments are also known as “disk pieces.”

Because of the way Adaptive Server allocates and maintains database space, these disk fragments are even multiples of 256 logical pages, which is one allocation unit. When you decide how large to make a device, consider the number of allocation units that are required, since the device size should be evenly divisible by the allocation unit size (256 times the logical page size). If it is not, the space at the end of that device is wasted, since Adaptive Server cannot allocate it. For example, if your server uses a 16K page, then one allocation unit is 4MB (16K times 256). If you create a device of 103MB on that server, the last 3 MB cannot be allocated and are wasted.

NoteThe master device is an exception to this rule. The master device is the first device you create when you install a new server. Adaptive Server reserves 8K of space at the beginning of the master device for a configuration area that is not part of any database. Take this space into account when you create your master device. 8K is 0.0078125MB (about .008MB). You will waste the least space in your master device if, for example, you specify 200.008MB, as its size, rather than 200MB.

A database cannot be larger than 2,147,483,648 pages. The logical page size determines the number of bytes: using a 2K page, it is 4 terabytes, on a 16K page Adaptive Server, it is 32 terabytes.

You can divide the storage for your databases between devices in any way you want. The theoretical limit for the number of disk fragments per database is 8,388,688. However, the practical limit depends on the Adaptive Server memory configuration. To use a database, Adaptive Server must hold the database’s storage description in memory. This includes a description of the database’s “disk map,” which includes all the disk fragments you have assigned storage to for the database. In practice, a database’s storage complexity is limited by the amount of memory configured for Adaptive Server, and is not normally a problem.

However, databases with disk maps that contain thousands of disk fragments may pay a penalty in performance. When Adaptive Server needs to read or write a page, it converts the page’s logical page number to a location on disk by looking it up in the disk map. Although this lookup is fast, it does take time, and the amount of time gets longer as you add more disk fragments to the map.