Databases (Sybase ASE Performance Analyst)

From RapidSQL
Jump to: navigation, search

Go Up to Sybase ASE Performance Analyst Statistics

In addition to a Home page, the Databases category of Sybase ASE Performance Analyst includes the following tabbed pages:

Home

The Database performance category view displays the following vital Sybase ASE database statistics:

Server Analysis Pane

The following statistic is used on the Performance Analyst for Sybase ASE Databases Page to succinctly communicate the general overall performance levels of database specific metrics:

The following statistics, available on this pane, are duplicates of statistics available on the Key Ratio Analysis Pane of the Home View (Sybase ASE Performance Analyst) page:

Sample Query Response Time

Performance Analyst allows you to define a query that is periodically run and timed by the product. The sample query helps you gauge the response time effectiveness of the Sybase ASE server.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Server Analysis Pane

Metrics

Because you control the complexity of the sample query, you must determine what is a “valid” response time for the query. Once you have determined that, you can create an alarm in Performance Analyst that will alert you whenever the sample query falls outside of its acceptable baseline.

Bottleneck Analysis Pane

The following statistics are used on the Performance Analyst for Sybase ASE Databases page to succinctly communicate the general overall performance levels of database specific metrics:

The following statistics, available on this pane, are duplicates of statistics available on the Bottleneck Analysis Pane of the Home View (Sybase ASE Performance Analyst) page:

Databases with Suspect Pages

Suspect objects normally indicate an internal problem in a server. They can also indicate that physical damage has occurred to part of a database.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Suspect objects have no place in a production database. If you identify any suspect objects, you should take immediate action.

Correction

If the suspect object is an index, you could try dropping and recreating it, or use the DBCC REINDEX command. Other damaged objects can complete rebuilding the database. A suspect database can be a difficult thing to recover from quickly. There are times when the cache of the database is suspect and not the database itself. Stopping and starting the Sybase ASE server can verify if this is the case. If the database itself is actually damaged, there could be a true recovery situation. The suspect database can be dropped using the DBCC DBREPAIR DROPDB command. You would then need to recreate the database and perform a recovery operation using the most recent database dump.

Engine Configuration Limit

An engine can exceed its limit for outstanding asynchronous disk I/O requests based on the maximum number of asynchronous I/Os per engine parameter.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

You can change this limit with the maximum asynchronous I/Os per engine configuration parameter.

Index Maintenance from Deletes

Indexes can considerably speed data access operations. The trade-off, however, is that indexes require maintenance whenever indexed columns are added to, modified, or removed in the parent table. The index maintenance from insert/updates metric provides a count of how many index maintenance operations have occurred from insert or update operations.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Some index maintenance activity is to be expected (unless you are operating in a read-only environment), but excessive index maintenance can quickly become a bottleneck in heavy OLTP environments.

Correction

If you observe a lot of index maintenance activity, you should begin a periodic review of your indexing schemes to ensure that all indexes currently maintained on the system are used and necessary. If you are using Sybase ASE 12.5.03 or later (and have installed the monitoring tables), you can use Performance Analysts object I/O drill-down view to diagnose which tables and indexes are the targets of heavy maintenance activity.

Index Maintenance from Insert/Updates

Indexes can considerably speed data access operations. The trade-off, however, is that indexes require maintenance whenever indexed columns are added to, modified, or removed in the parent table. The index maintenance from insert/updates metric provides a count of how many index maintenance operations have occurred from insert or update operations.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Some index maintenance activity is to be expected (unless you are operating in a read-only environment), however excessive index maintenance can quickly become a bottleneck in heavy OLTP environments.

Correction

If you observe a lot of index maintenance activity, you should begin a periodic review of your indexing schemes to ensure that all indexes currently maintained on the system are used and necessary. If you are using Sybase ASE 12.5.03 or higher (and have installed the monitoring tables), you can use Performance Analysts object I/O drill down view to diagnose which tables and indexes are the targets of heavy maintenance activity.

Operating System Limit

When a Sybase ASE server begins to experience I/O delays, the result caSybase ASEn be a very dissatisfied user community. When such problems occur, you should investigate the Sybase ASE or operating system limits. It could be that I/O operations are being blocked by one or both.

The Operating System Limit statistic shows nonzero numbers if Sybase ASE detects that the limit for asynchronous I/Os has been exceeded.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

You should be concerned if you consistently see numbers above zero.

Correction

In most UNIX operating systems, there is a kernel parameter that limits the number of asynchronous I/Os that can occur at one time. If you continue to see nonzero numbers for this statistic, you should look into raising this limit.

Page Splits

When data is inserted or updated in a table, Sybase ASE may reorganize the data storage in the table's index pages. When an index page becomes full, but a DML operation demands room on that page, Sybase ASE moves half the rows to a new page to accommodate the request. This reorganization is known as a page split.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Performance for DML actions can be impaired from page split operations. In addition, more index pages can make for longer index scan times.

Server Configuration Limit

When a Sybase ASE server begins to experience I/O delays, the result can be a very dissatisfied user community. When such problems begin to occur, you should investigate the Sybase ASE or operating system limits. It could be that I/O operations are being blocked by one or both limits.

The Server Configuration Limit statistic shows nonzero numbers if Sybase ASE has exceeded its limit for the number of asynchronous disk I/O requests that can be outstanding for the server at one time.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

You should be concerned if you consistently see numbers above zero.

Correction

If you continue to see nonzero numbers for this statistic, you can raise this limit using sp_configure with the max async I/Os per server parameter.

Backup Analysis Pane

The following statistics are used on the Performance Analyst for Sybase ASE Databases page to succinctly communicate the general overall performance levels of database specific metrics:


Backup Server Status

The Backup Server Status value indicates whether or not the Backup Server is up and running.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Backup Analysis Pane

Last Transaction Log Dump

The Last Transaction Log Dump value represents the last time the transaction was dump for the associated database.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Backup Analysis Pane

Engine Analysis Pane

The following statistics are used on the Performance Analyst for Sybase ASE Databases page to succinctly communicate the general overall performance levels of database specific metrics:

Engine Busy %

The Engine Busy % value represents the percentage of time the Adaptive Server Kernel is busy executing tasks on each Adaptive Server engine (rather than time spent idle).

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Engine Analysis Pane

Metrics

When Adaptive Server has no tasks to process, it enters a loop that regularly checks for network and completed disk I/Os, and tasks in the run queue. Operating system commands to check CPU activity may show high usage for an Adaptive Server engine because they are measuring the looping activity, while Engine Busy % does not include time spent looping it is considered idle time.

Correction

This category can help you decide whether there are too many or too few Adaptive Server engines. Adaptive Server's high scalability is due to tunable mechanisms that avoid resource contention. If values are consistently very high (more than 90%), it is likely that response time and throughput could benefit from an additional engine.

Maximum Engines

The Maximum Engines value represents the maximum number of engines currently connected.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Engine Analysis Pane

Workload Analysis Pane

The following statistic is used on the Performance Analyst for Sybase ASE Databases page to succinctly communicate the general overall performance levels of database specific metrics:

Workload Analysis

The Sybase ASE Server is broken up into many different databases, each of which has its own levels of activity. The Workload Analysis section displays a summary of resource consumption across all databases.

The table below describes the information available in the Workload Analysis section:

Column Description

DB Name

The name of the database.

Connections

The number of connections to the database.

Blocked Users

The number of users currently blocked on the associated database.

Total Memory

The total amount of memory currently being used by the database.

Total I/O

The total amount of I/O currently being registered by the database.

Total CPU

The total amount of cumulative CPU for all processes connected to the database.


Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Home > Engine Analysis Pane

Overview Tab

The following statistics are used on the Performance Analyst for Sybase ASE Databases Detail Overview Page to succinctly communicate the general overall performance levels of database specific metrics:

Database Size Analysis

The Database Size Analysis chart displays each database in terms of its total percentage of size to the total size of all databases on the Sybase ASE Server.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Overview Tab

Database Object Analysis

The Database Object Analysis chart displays each database in terms of its terms of its total percentage of objects to the total objects for all databases on the Sybase ASE Server.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Overview Tab

Database User Analysis

The Database User Analysis chart displays each database in terms of its total percentage of user accounts to the total user accounts of all databases on the Sybase ASE Server.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Overview Tab

Database Summary

This section displays summarized metadata for all defined databases on the Sybase ASE Server.

Note: Right-click any column heading to sort the column(s) in ascending or descending order.

The information available in the Database Summary table is described below:

Column Description

Database

The name of the database.

Created

Gives the month/day/year and hour/minutes/second on which the database was first generated.

Owner

Lets you see who owns the database.

Database Size (MB)

Displays the size of the database in megabytes.

Log Size (MB)

Displays the log size in megabytes.

Tables

Gives the number of tables in the database.

Indexes

Displays the number of indexes in the table.

Users

Lets you see the number of database users.

Last Trans Log Backup

Let you see the last month/day/year and hour/minutes/second on which the transaction logs were most recently backed-up.

Suspect

Lets you see in YES or NO format whether the database is suspect.

Suspect Pages

Lets you see in YES or NO format whether there are any suspect pages in the database.


Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Overview Tab

Metrics

The following items merit attention: Any critical database that shows a last transaction log backup date that is older than the database's required backup needs. Any database that shows a status of offline or suspect.

Objects Tab

he following statistics are used on the Performance Analyst for Sybase ASE Databases Detail Objects Page to succinctly communicate the general overall performance levels of database specific metrics:

Database Object Detail

This view gives you detailed information for each database. Select the database you want to scrutinize and choose between reviewing tables or indexes. You can also enter a preference to see objects matching at pattern you specify in the text box. The table below describes the information available in the Database Object Detail table:

Column Description

Owner

Displays the name of the table or index owner, depending on the view you selected.

Table/Index Name

Displays the name of the table or index, depending on the view you selected.

Segment Name

Lets you see associated table/index segment.

Locking Scheme

TABLE ONLY: Lets you see the type of locking scheme the table uses.

Last Statistic Date

TABLE ONLY: Lets you see the most recent date and time table statistics were gathered.

Row Count

TABLE ONLY: Lets you see the number of rows in the target table.

Reserved (KB)

Lets you see the amount of space reserved for the target object in kilobytes.

Avg Row Size

TABLE ONLY: Lets you see the average row size for the target table.

Tree Depth

INDEX ONLY: Lets you see the number of layers, or depth, of the index.

Leaf Count

INDEX ONLY: Lets you see the number of leaf-level pages in the index.

Leaf Row Size

INDEX ONLY: Lets you see the row size for each index leaf.


Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Objects Tab

Suspect Objects

Suspect objects normally indicate an internal problem in a server or that physical damage has occurred to part of a database. The table below describes the information available in the Suspect Objects grid:

Column Description

Owner

Displays the name of the table owner.

Table Name

The affected table’s name.

Index Name

The affected index’s name.


Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Objects Tab

Metrics

Suspect objects have no place in a production database. If any suspect objects are identified, you should immediately take action.

Correction

If the suspect object is an index, you could try dropping and re-creating it, or use the DBCC REINDEX command. Other damaged objects can complete rebuilding the database. A suspect database can be a difficult thing to recover from quickly. There are times when the cache of the database is suspect and not the database itself. Stopping and starting the Sybase ASE server can verify if this is the case. If the database itself is actually damaged, there could be a true recovery situation. The suspect database can be dropped using the DBCC DBREPAIR DROPDB command. You would then need to re-create the database and perform a recovery operation using the most recent database dump.

Cache Bindings

The Sybase ASE cache areas are devoted to facilitating the transfer of data and information between clients and the Sybase ASE database. Tables and/or indexes can be assigned to various caches in hopes of speeding up access to table or index information. The Cache Bindings Detail section provides information about the current cache bindings of tables and/or indexes. The table below describes the information available in the Cache Bindings chart:

Column Description

Owner

The object owner’s name.

Table Name

The table’s name.

Index Name

The index’s name.

Cache Binding

The name of the cache associated with the table or index.Databa


Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Objects Tab

Metrics

The objects best suited for being bound to a cache include frequently accessed objects that are oftentimes small in nature. Large tables that are scanned are normally not suitable for being bound to a cache and are best left to being indexed properly.

Configuration Tab

The following statistics are used on the Performance Analyst for Sybase ASE Databases Detail Configuration Page to succinctly communicate the general overall performance levels of database specific metrics:

Database Configuration

The Database Configuration view allows you to see all the settings currently configured parameters and their settings on the associated database. Select a database from the drop-down list to see the parameter/setting information.

Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Configuration Tab

Server Configuration

The Server Configuration view allows you to see all the settings of currently configured parameters on the Sybase ASE Server. Select a database from the drop-down list to see the following information in the Server Configuration grid:

Column Description

Parameter

Lets you see the name of the configured parameter.

Configured Value

Displays the configured value of the parameter.

Run Value

Displays the run value.

Minimum

Displays the minimum configuration value.

Maximum

Displays the maximum configuration value.

Dynamic

Indicates whether or not the parameter is dynamic or not.


Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Configuration Tab

Engines Tab

The following statistic is used on the Performance Analyst for Sybase ASE Databases Detail Engines Page to succinctly communicate the general overall performance levels of database specific metrics:

Database I/O

The Database I/O view on the Engines tab of the Databases Detail view allows you to see statistics about database objects. The table below describes the information available to you about Database I/O:

Column Description

Name

The name of the engine.

Start Date/Time

The date and time when the Sybase ASE engine started.

Stop Date/Time

The date and time when the Sybase ASE engine stopped

OSPID

The operating system process ID.

KPID

The kernel process ID.

Status

The engine’s current status and whether it is connected or not.

Context Switches

The current number of context switches registering on the associated engine.

Current Connections

The current number of connections registering on the associated engine.

CPU Time

The current amount of CPU time consumed by the associated engine.

CPU Time (System)

The current amount of system CPU time.

CPU Time (User)

The current amount of user CPU time.


Location

Sybase ASE Performance Analyst Statistics > Space (Sybase ASE Performance Analyst) > Configuration Tab

Retrieved from "https://docwiki.embarcadero.com/RapidSQL/171/e/index.php?title=Databases_(Sybase_ASE_Performance_Analyst)&oldid=10715"