Databases (Sybase ASE Performance Analyst)
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:
Contents
- 1 Home
- 2 Server Analysis Pane
- 3 Sample Query Response Time
- 4 Bottleneck Analysis Pane
- 5 Databases with Suspect Pages
- 6 Engine Configuration Limit
- 7 Index Maintenance from Deletes
- 8 Index Maintenance from Insert/Updates
- 9 Operating System Limit
- 10 Page Splits
- 11 Server Configuration Limit
- 12 Backup Analysis Pane
- 13 Backup Server Status
- 14 Last Transaction Log Dump
- 15 Engine Analysis Pane
- 16 Engine Busy %
- 17 Maximum Engines
- 18 Workload Analysis Pane
- 19 Workload Analysis
- 20 Overview Tab
- 21 Database Size Analysis
- 22 Database Object Analysis
- 23 Database User Analysis
- 24 Database Summary
- 25 Objects Tab
- 26 Database Object Detail
- 27 Suspect Objects
- 28 Cache Bindings
- 29 Configuration Tab
- 30 Database Configuration
- 31 Server Configuration
- 32 Engines Tab
- 33 Database I/O
Home
The Database performance category view displays the following vital Sybase ASE database statistics:
- Server Analysis Pane
- Bottleneck Analysis Pane
- Backup Analysis Pane
- Engine Analysis Pane
- Workload Analysis Pane
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:
- Databases with Suspect Pages
- Engine Configuration Limit
- Index Maintenance from Deletes
- Index Maintenance from Insert/Updates
- Operating System Limit
- Page Splits
- Server Configuration Limit
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 |