If your index is not performing as expected, you may want to consider the following actions:

• Reorganize composite indexes.
• Increase the page size.

These measures are aimed at increasing index selectivity and index fan-out, as explained below.

Index selectivity

Index selectivity refers to the ability of an index to locate a desired index entry without having to read additional data.

If selectivity is low, additional information must be retrieved from the table page that the index references. These retrievals are called full compares, and they have a negative effect on index performance.

The FullCompare property function keeps track of the number of full compares that have occurred. You can also monitor this statistic using the Sybase Central Performance monitor or the Windows Performance Monitor.

In addition, the number of full compares is provided in the graphical plan with statistics. For more information, see Common statistics used in the plan.

For more information about the FullCompare function, see Database properties.

Index structure and index fan-out

Indexes are organized in several levels, like a tree. The first page of an index, called the root page, branches into one or more pages at the next level, and each of those pages branch again, until the lowest level of the index is reached. These lowest level index pages are called leaf pages. To locate a specific row, an index with n levels requires n reads for index pages and one read for the data page containing the actual row. In general, fewer than n reads from disk are needed, since index pages that are used frequently tend to be stored in cache.

The index fan-out is the number of index entries stored on a page. An index with a higher fan-out may have fewer levels than an index with a lower fan-out. Therefore, higher index fan-out generally means better index performance. Choosing the correct page size for your database can improve index fan-out. See Table and page sizes.

You can see the number of levels in an index by using the sa_index_levels system procedure. See sa_index_levels system procedure.