Page History

...

• Overview
• Database options
• Replication Agent configuration parameter

About getting an overview configuration, performance, and Space Allocation information for a selected database

The Overview displays configuration, performance, and Space Allocation information for the selected database, as follows:Properties    Displays

• Properties. Displays general database details, such as status, owner, and log segment free space.
• In

...

• Sybase. Displays resource consumption of the selected database during selected time frame, as collected by the Precise for Sybase Collector agent.
• Space

...

• Allocation. Displays total free space and total used space (in MB) during the selected time frame, of all the segments in the selected database, as collected by the Collect Space Utilization process.
• Data vs. log.

...

• Displays total free space and total used space (in MB) during the selected time frame, of all the segments in the selected database, as collected by the Collect Space Utilization process, and displayed according to segment usage as data only, log only and data and log segments.

About viewing Database options

The Database Options view displays database options, such as abort tran on log full and trunc. log on chkpt, as follows:

• Database

...

• options. Displays the current value of the database options.
• In

...

• Sybase. Displays resource consumption information of the selected database during the selected time frame.

About the Replication Agent configuration parameter view

This table describes the Replication Agent configuration parameters for the specified database. This view is only enabled for published databases. The Replication Agent Configuration Parameters in the Main area can be accessed as follows:

1. In the Objects tab in Precise for Sybase, select the desired database in the Association area.
2. Click Overview in the Main area, and then click Replication Agent Configuration Parameters. The Replication Agent Configuration Parameters appear in the Main area.

...

Table 7-1 Replication Agent Configuration Parameters headers

...

...

...

...

...

About the Database entity in the Association area

You can view the entities associated with the selected Database entity in the Association area. It is possible to associate to the Database entity from the Instance entity.

...

• Overview
• Optdiag
• Table partitions

About getting an overview of table entities

The Overview displays general table properties, along with space utilization, and number of rows in the selected table during the selected time frame. It is divided in the following areas:Properties    Displays

• Properties. Displays general table details, such as, number of columns, data cache, and whether or not the table uses a clustered index.
• Space

...

• Allocation. Displays the amount of free space and used space (in MB) in the selected table during the selected time frame, and specifies whether the space is populated by an index or data.
  The values may be incorrect due to out-of-date space usage information. To update the values, use the DBCC FLUSHSTATS command to flush the most up-to-date information regarding the objects size from memory and run UPDATE STATISTICS to recalculate the statistics. You can also use the DBCC CHECKSTORAGE command to verify and recalculate the space usage information.

...

• Rows. Displays the number of rows in the selected table during the selected time frame, as collected by the Collect Space Utilization process.

About viewing data storage and fragmentation information

The Optdiag view displays data storage and fragmentation information for the selected table. The following information is displayed:

• Optdiag. Optdiag displays the following information:
  • Pages. Displays the number of data pages.
  • Empty Pages. The number of empty data pages. A non-zero count, indicates fragmentation, which can be resolved with reorg.
  • Rows. The total number of data rows.
  • Number of forwarded records. When there is not enough free space to write an updated record in its page, Sybase allocates the updated record to another page and instructs the old record to point to the new location. The number of forwarded records, is the number of records allocated to another page, since there was no free space in the original page. Accessing forwarded records adversely affects performance, because additional I/O is performed. Check the number of forwarded records in your table or index in relation to the total number of records. If the ratio is high, consider reorganizing the table or index to achieve a better performance. To reorganize data-only-locked tables, use the reorg command. This number is always zero for allpages-locked (APL) tables.
  • Deleted Rows. The number of committed deletes, whose space has not yet been reclaimed. An increase in this number means increase in fragmentation as well. This can be resolved using reorg.
  • Extents. Displays the number of extents used to store the data pages.
  • OAM + Allocation Page Counts. The sum of Object Allocation Map (OAM) pages plus allocation pages for the table. An Object Allocation Map (OAM) shows how the Adaptive Server allocates space for objects. Each allocation page for an object has an entry in the OAM pages for that table or index. There is at least one OAM page per table.
  • First Extent Data Pages. The number of pages that appear on the first extent in an allocation unit, and consequently share the extent with an allocation page. This number is useful for I/O estimation.
  • Min Record Size. Indicates the minimum record size in the data pages.
  • Max Record Size. Indicates the maximum record size in the data pages.
  • Avg Record Size. Indicates the average record size in the data pages.
• Derived Statistics. Derived Statistics displays the following information:
  • Average Extents Ratio. Ratio of the size of the data to the size that can be stored in all pages allocated to the table. Gives an estimation of how full each page really is. Should be as close to 1.0 as possible. This statistic is only displayed in versions prior to version 12.5.0.3.
  • Data Page Cluster Ratio. This is a measure of how well data pages are clustered within extents. The closer this number is to 1.0, the better. When the cluster ratio is low, then performance may be affected because it would take more I/O calls to read the table pages. This derived statistic is only displayed in version 12.5.0.3 and above.
• Table Definition Related Values. Table Definition Related Values displays the following information:
  • Max Rows Per Page. Value of max_rows_per_page table parameter. Setting a maximum number of rows per page can reduce contention for allpages-locked tables and indexes, as fewer rows are placed on each page.
  • Expected Row Size. Value of the exp_row_size table definition parameter. Relevant for data-only-locked tables. Useful when an application allows rows that contain null values or short variable-length character fields to be inserted, and these rows grow in length with subsequent updates. The major purpose of setting an expected row size is to reduce row forwarding.
  • Reserved Page Gap. Value of reservepagegap table definition parameter. The reservepagegap space management property is used to reserve empty pages for expansion, when additional pages need to be allocated. Setting this parameter can reduce the frequency of maintenance activities, such as record rebuild and re-creating indexes.
• Extent Fragmentation (%). The extent fragmentation indicates how well the extents, which contain the data pages, are physically contiguous on the disk.

...

This number is not relevant to heaps because physical order has no importance in the data pages.

...

About viewing data on table partitions

The Table Partitions view displays size and distribution of table storage over all of its partitions. The following information is displayed:

• Avg. Partition

...

• Pages. Displays the average number of pages in each partition.
• Min. Partition

...

• Pages. Displays the number of pages in the smallest partition table.
• Max. Partition

...

• Pages. Displays the number of pages in the largest partition table.
• Partition Pages Ratio (Max/Avg)

...

• . The ratio between the largest partitions to the average partition size. A number that is much bigger than 1.0 is an indication that the table rows are not well balanced between the partitions.
• Partition

...

• size. Visual representation of the way table storage is spread among all partitions.

Anchor
AbouttheIndexentity AbouttheIndexentity

About the Index entity

The Index entity displays general details regarding space utilization for the selected index. The following views are available:

• Overview
• Optdiag
• Statistics

About getting an overview of index entities

The Overview displays the following information:Properties    Displays

• Properties. Displays general details on the index such as number of keys and whether the index is clustered or unique.
• Space

...

• Allocation. Displays the amount of free space and used space (in MB) in the selected index during the selected time frame.
  The values may be incorrect due to out-of-date space usage information. To update the values, use

...

• the DBCC FLUSHSTATS command to flush the most up-to-date information regarding the objects size from memory and run UPDATE STATISTICS to recalculate the statistics. You can also use the DBCC CHECKSTORAGE command to verify and recalculate the space usage information.

...

• Rows. Displays the number of rows in the underlying table during the selected time frame, as collected by the Collection Space Utilization process.

Anchor
Aboutviewingdatastorageandfragmentationinformationfortheselectedindex Aboutviewingdatastorageandfragmentationinformationfortheselectedindex

About viewing data storage and fragmentation information for the selected index

The Optdiag view displays data storage and fragmentation information for the selected index. The following information is displayed:Pages    Displays

• Pages. Displays the number of index pages at leaf level. For a clustered index on the APL table, this is the same as the value displayed for the table.
• Empty

...

• Pages. The number of empty leaf pages in the index. Any empty pages are counted towards the total page count by the optimizer and are a waste both of space and page count. If the number increases, consider dropping and recreating the index or running reorg.
• Data Page CR

...

• Count. This number is used by Sybase to derive the data page cluster ratio, when accessing the table with this index. All CR counts are a measure of the additional I/O required, over a perfect clustering scenario. The server uses CR counts to calculate various cluster ratios that can be obtained by running the optdiag utility.
• Index Page CR

...

• Count. This number is used by Sybase to derive the index page cluster ratio. It is shown for dol-clustered and non-clustered indexes only.
• Data Row CR

...

• Count. A measure of how well clustered data rows are on data pages, in relation to rows in the leaf level of this index.
  This value indicates how many I/O should be done to read row sequentially from the leaf of the index. This is important since it is not always true that for every read of a data row from the leaf of a non-clustered index, one I/O would have to be done from the data pages. This is because sequential rows may be on the same data page. This value now allows the optimizer to estimate how efficient a non-clustered access will be.
  It is shown for dol-clustered and non-clustered indexes only.
• First Extent Leaf

...

• Pages. This is the number of leaf-pages that appear on the first extent in an allocation unit, and consequently share the extent with an allocation page. This number is useful for I/O estimations.
• Number of

...

• Extents. Number of extents used to store the pages in the leaf level of the index.
• Index

...

• Height. Number of levels in the index.
• Min. Record Size (Bytes)

...

• . Indicates the minimum record size in the index page.
• Max Record Size (Bytes)

...

• . Indicates the maximum record size in the index pages.
• Avg. Record Size (Bytes)

...

• . Indicates the average record size in the index pages.
• Max Non-Leaf Record Size (Bytes)

...

• . Indicates the maximum record size in the intermediate pages of the index.
• Fill Factor (%)

...

• . The value for the fill factor of a table/index, set at table definition or using the sp_chgattribute command. It specifies how full Adaptive Server makes each page when it creates a new index on existing data. The fill factor percentage is relevant only when the index is created. As the data changes, the pages are not maintained at any particular level of fullness.

About viewing derived statistics

The items in the following table are only displayed for instances in version 12.5.0.3 and above.

• Updated

...

• statistics. The last date where update statistics was run on the table.
• Data page cluster

...

• ratio. This is a measure of the clustering of data pages in extents. The figure takes into account the type of table; the ratio for DOL tables measures clustering with respect to the order of index rows, whereas the ratio for APL tables measures clustering with respect to the order of the page chain. The data page cluster ratio is used to estimate the cost of large I/O. The higher this ratio, the more efficient large I/O will be
• Index page cluster

...

• ratio. This ratio measures clustering of index leaf pages in extents with respect to the leaf page chain. It is used to estimate the cost of large I/O.
• Data row cluster

...

• ratio. This is a measure of the clustering of data rows on pages with respect to the index key order. This ratio helps in estimating the cost of using a non-clustered index, or a clustered index on a DOL table.

About viewing clustered index details

This section applies to a clustered index on an APL table. The counters shown are the same as those shown at table level. This section is only shown if the index is a clustered index.

About viewing density and selectivity statistics

The Statistics view displays density and selectivity statistics for each prefix subset of the selected index keys. Histograms for the index columns can be seen at the relevant column statistics view.

The following information is displayed:Columns    The

• Columns. The left-based subset of key columns in the index (including the first column alone).
• Statistics

...

• Updated. Date the index was created, date that update statistics was last run, or date that optdiag was last used to change statistics
• Range Cell

...

• Density. A measure of the density of values in the column, after values in frequency count cells has been eliminated. Basically, this is the density of all values that are not highly duplicated. This value is used to estimate the cost of an equality search arguments when the value falls into a range cell. Indicates the number of rows sampled for the statistics calculation.
• Total

...

• Density. A measure of the duplicate values in all rows of the column, no matter what cell type they fall into. This value is used to estimate the number of rows that will be returned for a join on this column. It is also used as the selectivity value when the value of an equality search arguments is not known at run time.
• Range

...

• Selectivity. Prints the default value of .33, unless the value has been updated using the optdiag input mode. This is the value used for range queries if the open ended range search argument (<, <=, >, >=) is not known at optimize time.
• In Between

...

• Selectivity. Prints the default value of .25, unless the value has been updated using the optdiag input mode. This is the value used for range queries if the closed range search argument (between, col >= X and col <= Y) is not known at optimize time.

Index entity Association area

You can view general details on the entities associated with the selected Index entity in the Association area. It is possible to associate to the Index entity from the following entities: Database, Table, Column, User, Segment, and Data Cache.

...