The goal of the data storage structure is to improve performance by reducing   enough of the I/O necessary to retrieve data. Adaptive Server supports two basic storage structures:

Linked pages (used for data) and B-tree structures with pointers up, down, and across levels (used for indexes).

Both of these structure types take different physical forms if you opt to use the Data–Only Locking (DOL) schemes available since version 11.9.2; evaluating the use and costs of DOL is also outside of the scope of this book. We will assume that tables and indexes are using the installation default of the All Pages Locking (APL) scheme.

ASE provides for configuration of the “page utilization percent” to allow Adaptive Server to allocate new extents to a table or index rather than search the OAM page chain to find available pages. Page utilization percent is the ratio of used pages to allocated pages; the default value is 95. When the ratio of used pages to allocated pages exceeds the page utilization percent, Adaptive Server simply allocates a new extent to the table or index. This saves a sometimes exhaustive search of OAM pages to find allocated but unused pages. As databases get large (tens to hundreds of gigabytes),this is a performance benefit, as the server does not need to walk all of the extents to find free space.

Lowering the page utilization percent can speed up processes performing a large amount of insert activity on a table, but it will increase the number of pages allocated to the table.

USAGE

sp_configure ‘page utilization percent’, 85

OAM and GAM Pages

Extents are important because they provide allocation and deallocation of space to a table or index. All eight pages of an extent are assigned to the same table or index, although they may not all be in use right now. A special exception applies to the first extent of an allocation unit, where only seven pages are available for allocation to objects.

While the allocation page tracks the use of space for a given region of the database, the object allocation map page determines where in a database a given object is assigned space. OAM pages contain various details about a table or index, but we are most concerned now with the allocation page entries; if an object is assigned any amount of space in an allocation unit (from the minimum of one extent to the maximum of 32 extents), the OAM for the object will contain a pointer to that unit’s allocation page. Reading the OAM page for an object will provide a list of allocation pages, which will each provide pointers to the particular used pages for the object. These scans can also be used to find pages belonging to an object that are currently unused.

The diagram above reflects the default 2k data page. ASE allocates at the extent level and stores at the page level. Every page has a header, which contains information about the page (page type, locking–specific details, available space, and other details) and the body, which contains data. Here we’re diagramming a data page. If we are locked with the default (and pre-11.9.2 only) locking mechanism, allpages, we have a 32-byte header and 2016 useable bytes on a page. If you are locking DOL, then we have a 46-byte header and 2004 bytes available on the page.

Note the word “useable;” for APL tables, we can’t actually have a 2016-byte row because when we log the row on a log page, we require an additional 54 bytes of overhead. This would split the row across pages, which is not permitted. Also note that for all data pages, there is a series of 2–byte pointers at the end of

Additional information you need to know about pages:

The sp_estspace stored procedure exists to allow you to estimate how much space your table will need for storage. In order to use this procedure to the best effect, you need to first create the tables and indexes so that the procedure can read the structures and sizes from the system tables for its calculations.

In the above figure, we are sizing the table pt_tx_CiamountNCamount. We are instructing the server to size this table for 1,000,000 rows, assuming a fillfactor of 75 percent. The fillfactor indicates that the pages will be initially set to approximately 75 percent full to allow for some growth; a smaller fillfactor value will allow more rows to be added before additional pages need to be assigned but will require many more initial pages to hold the same number of rows.

For an existing table, use the stored procedure sp_spaceused or dbcc checktable.

Table size may also be calculated by hand, but as these calculations are also estimates, using these tools is far simpler and likely to be as accurate or more accurate.

A table scan is a sequential read of every data page in a table. Table scans are used if there is no appropriate index or if an index would slow down the process. The time required for a table scan is directly proportional to the size of the table in pages.

To get exact logical counts, set “statistics I/O” on prior to running a query.

It is useful to be able to accurately measure the page access speed of your server.

Here is one technique:

For a 1,333,334-page table:

TABLE SCAN PERFORMANCE

Faster drives (Solid State Accelerators, for example) or faster disk access methods might help (raw partitions may be 40-50% faster than file systems on a UNIX box).

We’ve talked all around indexes, so we must finally describe them. Indexes are storage structures separate from the data pages. Their benefits include:

All ASE indexes are B-Tree Structures.

There may be many intermediate levels, depending on key width, row count, and type of index. Different design books count levels from different directions; it is the number of levels that is important.

Index Types (APL):

create table tab1 (

col1 int

col2 int

col3 char(700))

“Unable to index” does not mean that you cannot use these columns in search clauses. Even though a column is non-indexable (as in col3, above), you can still create statistics for it. Also, if you include the column in a where clause, it will be evaluated during optimization.

Optimizer statistics are various measures of table size (page and row counts, row size, etc.) and data distribution used to determine the most efficient access method for a query (index, table scan, etc.). Different statistics are measured and maintained by different means, some by the server automatically and some by the action of an owner or administrator. Regardless of how you gather statistics, they are maintained only for the first 255 bytes of data in a column. If you use wide columns, any information after the first 255 bytes is considered statistically insignificant by the optimizer.

The data is still logically sorted in index order, but it may not be sorted on the page (this means that clustered indexes aren’t, so we will often refer to them as “placement indexes” instead). The storage structures are identical for both clustered and non-clustered indexes, and there are no longer sibling pointers at non-leaf index levels.

Index pages now have row offset tables to determine the order of keys, since they are not necessarily stored in order on the index page. Ordinarily, a clustered index is identified by Index ID 1; for DOL tables, this value is no longer used. The one DOL CI is identified with 0x200 (decimal 512) in the status2 column of the sysindexes table.

DOL indexes also have some structural changes designed to improve their storage efficiency: Duplicate keys in non–unique indexes are stored only once per index page, and key values on non-leaf pages are reduced to the minimum possible byte length by using suffix compression.

Clustered Indexes are used to direct placement of data rows in sorted order. Rowsmay not be sorted on the page. The data pages are not leaf pages.

• Data modifications are faster.

• Less movement on page.

• DOL index B-Trees are more efficient.

• Retrieval may be slower (more I/O to get a row).

• Large I/O may not be as effective.

• May need 40-50% more storage.

The non-clustered index requires an extra level, as it needs pointers for every row of data

Clustered

Index Detail

(allpages locking)

Index levels point to succeeding index levels. The B-Tree is traversed by following

these pointers.

Non-Clustered

Index Detail

Create index i1

on sales (id asc, purchasedate desc)

This will create an index on the sales table, and if you look frequently for the most recent purchase, you will avoid scanning through older purchases. This can help avoid backward scans, which are a frequent cause of deadlocks.

APL tables with a clustered index will place rows in the table according to the clustered index order. APL heap tables will add rows in the last position of the last pageof the table.

DOL tables without a placement index have new rows added to a designated insert page. Unlike APL tables, we cannot say that this page is at the end of the table, since DOL tables do not maintain page pointers or a page order. The last logical page number (on APL) or insert page number (on DOL) is stored in sysindexes as the root page.

When the last row is removed from an APL data or leaf page, that page is removed from its current page chain and marked as “available” on the allocation page. When rows are removed from the middle of a page, the remaining rows are adjusted upward on the page so that free space is left at the end of the page. Removing rows causes deletions to cascade into all non-clustered indexes that point to rows on that page. Rows that are deleted from DOL tables are marked as logically deleted, but they are not physically removed from the page immediately (some of this space will be recovered with time and some when maintenance commands, like “reorg,” are used). Tables with a placement index have rows added on the appropriate page, if there is room; otherwise, the row will be placed on the nearest available page. DOL leaf-level index pages will split, since the entries must remain in index order.

Data modification statements can have a variety of adverse impacts on performance in regard to indexes. Updates and deletes will frequently cause rows to move, both in the data and in the index. If an update or delete causes an APL row to move, all non-clustered indexes on that table will need to be updated to reflect the new row position. By contrast, a DOL row that moves will not update its index entries, but it will leave behind a forwarding address to the new physical location. Likewise, inserts into an APL data page of a table with a clustered index will frequently cause rows on the page to be moved down, or they will cause the page to split. All non-clustered indexes on the table will need to be updated to reflect the new row position(s).

The default fill factor is set at the server level, but it can be overridden when indexes are created.

Server-wide default:

Overriding at index creation:

create index idx_name on table (column)

with fillfactor=N

In both cases, N is the percentage full, except

• N = 100 Data and Index pages full, except root.

• N = 0 Full data pages, indexes at 75%.

CHOOSING INDEXES

Sometimes, the clustered indexes are created based on the primary key of the tables. In a way, this makes sense. A clustered index tends to be one I/O faster than a non-clustered index for a single-row lookup. So many database design tools automatically make this selection: “If this is the primary way we go after the data, we’ll place the clustered index on this one and save the I/O whenever we retrieve the data.”

However, there are other reasons to choose: Remember, clustered indexes have the data physically sorted in index-order, so that makes it a great choice for any range searches or queries with an order by clause.

Non-clustered indexes are a bit slower and take up much more disk space, but they are the next best alternative to a table scan. For some queries, known as covered queries, non-clustered indexes can be faster than clustered indexes.

SUMMARY

Ninety-five percent of tuning is based upon index selection. Choose your indexes wisely, based upon your understanding of physical storage.