Home View (Sybase ASE Performance Analyst)

From DBArtisan
Jump to: navigation, search

Go Up to Sybase ASE Performance Analyst Statistics

The IDERA Performance Analyst Home view lets you review availability and overall performance of all monitored databases from a single window. Statistics on the Home view are organized into the following categories:

Key Ratio Analysis Pane

Database performance analysts typically use one of two methods for examining the performance levels of a database - ratio-based or wait/bottleneck-based. Ratio-based analysis involves examining a number of key database ratios that can be used to indicate how well a database is running. Performance ratios serve as very good roll-up mechanisms for busy DBAs to use for at-a-glance performance analysis. Many DBAs have large database farms to contend with and cannot spend time checking detailed wait-based analysis outputs for each and every database they oversee. Succinctly presented performance ratios can assist in such situations by giving DBAs a few solid indicators that can be quickly scanned to see if any database needs immediate attention.

While there are certainly many opinions as to what rules to follow, there are some standards that should always be adhered to. To start with, many of the formulas that make up ratio-based analysis must be derived from delta measurements instead of cumulative statistics. Many of the global ratios that a DBA will examine come from the master.sysperfinfo performance table. This table maintains a count of various statistics for the server and particular databases since the server was brought up. For servers that are kept up for long periods of time, these values can grow quite large and will impact how a particular ratio that a DBA might be looking at is interpreted. However, if delta statistics are used (taking, for a specified sampling period, the before and after counts of each statistic that make up a ratio), then an accurate and current portrayal of the various ratios can be had.

A final thing to remember about using ratio-based analysis is that, while there are a number of rules of thumb that can be used as starting points to begin the evaluation of database performance, DBAs must determine each SQL Server's individual 'personality' with respect to the key performance ratios. Some hard and fast rules simply do not apply to every database. The danger in using blanket ratio standards is that they can lead the DBA to haphazardly take action, which can at times contribute nothing to the situation, and sometimes even degrade performance.

The following ratios are used on the Performance Analyst Home page to succinctly communicate the general overall performance levels of the monitored database:

Data Cache Hit Ratio

Data read from memory produces user response times many times faster than when that same data is read from disk. The Sybase ASE data cache assists with keeping physical I/Os to an absolute minimum.

The data cache hit ratio is an excellent indicator of how often user requests for data are satisfied through memory as opposed to being physically read from disk. The table below describes the three key counters in Sybase ASE used to arrive at this statistic:

Counter Description

LOGICAL READS

Data read from memory for user requests.

PAGES PER I/O

The number of pages retrieved in a single I/O operation.

PHYSICAL READS

Data read physically from disk.


Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Key Ratio Analysis Pane Sybase ASE Performance Analyst Statistics > Memory View (Sybase ASE Performance Analyst) > Home> Key Ratio Analysis Pane Sybase ASE Performance Analyst Statistics > Memory View (Sybase ASE Performance Analyst) > Data Cache Tab

Metrics

To help ensure excellent performance, keep your cache hit rate in the neighborhood of 90% or greater. Lower amounts can be okay for user ad hoc databases where sporadic, large table scan operations occur. However, anything below this general threshold for normal databases can require tuning attention, and the adjustment of the Sybase ASE memory tuning parameters. If you are using named caches, you can drill down into the cache hit rates for each named cache. This helps you understand which objects/operations are depressing the overall cache hit rate for the server.

Correction

If a problem is found in Sybase ASE servers, versions 11-12, you can increase the amount of the total memory configuration parameter or reduce the percentage of memory allocated to the procedure cache (by default, the data cache assumes any free memory left over after Sybase ASE has met its kernel and procedure cache needs). Take care when reducing the procedure cache, as this could reduce performance in the server as it relates to reading procedures in from disk. For Sybase ASE 12.5, the total memory configuration parameter can again be increased to provide more memory for the data cache (and any named caches), but in 12.5, if you want to reduce the size of the procedure cache, note that it is now configured in terms of literal size instead of a percentage of the overall configured memory.

Once the data cache has been adjusted, monitor Sybase ASE to see if the cache hit rate improves. If it does not, another increase may be necessary and examination of unnecessary large table scan operations. Also, keep a careful eye on the actual machine’s memory limits and swap activity. Increased swap activity can be indicative of too little memory left for the server machine.

Procedure Plan Hit Ratio

The Sybase ASE procedure cache is used to hold the definitions and query plans of stored procedures and triggers. It is used for short-term memory needs like statistics and query plans for parallel queries. When a user executes a stored procedure, Sybase ASE looks in the procedure cache for a query plan to use. If a query plan is available, Sybase ASE places it on the most recently used (MRU) end of the memory chain and the procedure begins to execute. If no execution plan is in memory, or if all copies of the plan are currently being used, the query tree for the procedure is read in again from the data dictionary, optimized, put on the MRU end of the chain, and executed. Note that other operations, like CREATE INDEX, can also use the procedure cache even when no procedure is referenced.

The more often that a procedure’s plan and definition can be referenced in memory, the better the procedure execution time.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Key Ratio Analysis Pane Sybase ASE Performance Analyst Statistics > Memory View (Sybase ASE Performance Analyst) > Home> Key Ratio Analysis Pane Sybase ASE Performance Analyst Statistics > Memory View (Sybase ASE Performance Analyst) > Procedure Cache Tab

Metrics

A high procedure cache hit rate is a desirable thing. You should strive for a hit ratio between 95-100%, with 95% being a good performance benchmark for procedure code reference. Note that when a database is first started, the procedure cache hit rate is not at an optimal level because all code being used is relatively new, and as such, must be read in from disk and placed into the cache. If, however, after a solid hour or two of steady database time, the procedure cache hit rate has not increased to desirable levels, you should look into the possibility of increasing the amount of memory allocated to the cache. You can drill down into the procedure cache to view the procedures currently in memory along with how much memory they are consuming. If there is not enough memory to load a requested procedure, or the maximum number of compiled objects is already in use, Sybase ASE returns an error (normally a 701).

Correction

If a problem is found in Sybase ASE servers, versions 11-12, you can increase the amount of the total memory configuration parameter or increase the percentage of memory allocated to the procedure cache (by default, the data cache assumes any free memory left over after Sybase ASE has met its kernel and procedure cache needs). You should be careful when increasing the procedure cache alone, as this could increase query response times due to more physical I/O being performed. For Sybase ASE 12.5, the total memory configuration parameter can again be increased to provide more memory for the Sybase ASE server, but in 12.5, if you want to increase the size of the procedure cache, note that it is now configured in terms of literal size instead of a percentage of the overall configured memory. Once the procedure cache has been adjusted, monitor Sybase ASE to see if the cache hit rate improves. If it does not, another increase may be necessary. Also, keep a careful eye on the actual machine’s memory limits and swap activity. Increased swap activity can be indicative of too little memory left for the server machine.

Large I/O Hit Rate

Large I/O can be enabled by splitting the default or any named cache into pools. By default, Sybase ASE performs I/O operations based on a 2-KB page size. For queries where pages are stored and accessed in a sequential manner, it is possible to read many more data pages in a single I/O operation. Large I/O can greatly reduce disk access time when the right situations exist. Operations that routinely perform large table scans, access image or text data, do bulk copies, scan the leaf level of nonclustered indexes, or initiate DBCC tasks can benefit from large I/O.

If large I/O has been configured and is being used, you should observe a high percentage of hits (the number of times large I/O could be performed vs. the number of times large I/O requests were denied by the server). If large I/O is not configured, no large I/O activity should be present.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Key Ratio Analysis Pane Sybase ASE Performance Analyst Statistics > Memory View (Sybase ASE Performance Analyst) > Home> Key Ratio Analysis Pane

Metrics

As you might expect, if large I/O is in use, a high hit rate is desirable. You should strive for a hit ratio between 90-100%, with 90% being a good performance benchmark.

Correction

If large I/O is configured, but you see a low hit rate, you should configure more caches for large I/O use.

Clean Buffer Grab Rate

As information is requested from users, buffers are moved into and out of the Sybase ASE data cache. Pages are also modified in the cache (termed dirty buffers) and need to be written out to disk. If Sybase ASE has to wait for a dirty buffer to be written out to disk before a requested buffer is placed into the cache, performance can suffer.

The clean buffer grab rate represents the percentage of time clean buffers were found and referenced in the cache as opposed to Sybase ASE finding dirty buffers.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Key Ratio Analysis Pane Sybase ASE Performance Analyst Statistics > Memory View (Sybase ASE Performance Analyst) > Home> Key Ratio Analysis Pane

Metrics

Ideally, the clean buffer grab rate should stay at or near 100%.

Correction

A poor clean buffer grab rate for either the default or named caches could indicate that the cache size is too small. You can look into adjusting the total memory configuration parameter higher. Monitor the actual machine’s memory limits and swap activity. Increased swap activity can be indicative of too little memory left for the server machine.

CPU Busy

The CPU Busy statistic represents the percent of the sample interval during which the Adaptive Server was busy.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Key Ratio Analysis Pane Sybase ASE Performance Analyst Statistics > Databases (Sybase ASE Performance Analyst) > Home> Server Analysis Pane

Correction

If you find that your CPU Busy value is consistently above 70%, consider adding additional hardware.

I/O Busy

This metric represents the number of clock ticks in the sample interval during which the user task performed I/O operations.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Key Ratio Analysis Pane Sybase ASE Performance Analyst Statistics > I/O (Sybase ASE Performance Analyst) > Home> Bottleneck Analysis Pane Sybase ASE Performance Analyst Statistics > Databases (Sybase ASE Performance Analyst) > Home> Server Analysis Pane

Metrics

High numbers indicate an I/O-intensive process. If idle time is also high, the application could be I/O bound.

Correction

To improve throughput: Assign a higher priority to the application. Bind the application to a lightly loaded engine or engine group. Partition the application’s data onto multiple devices.

Server Idle

This metric represents the number of clock ticks in the sample interval during which the user task was idle.

Metrics

If you see high numbers for idle time and I/O Busy, the application could be I/O-bound.

Correction

To improve throughput:

  • Assign a higher priority to the application.
  • Bind the application to a lightly loaded engine or engine group.
  • Partition the application’s data onto multiple devices.

Bottleneck Analysis Pane

When Sybase ASE Adaptive Server is running, every connected process is either busy doing work or waiting to work. A process that is waiting might mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. And this is where wait-based or bottleneck analysis comes into play. DBAs use this form of performance analysis to determine if perceived bottlenecks in a database are contributing to a performance problem.

Bottleneck analysis is a valid method of measuring performance because it helps a DBA track where a database has been spending its time. If lock contention or heavy table scan activity has been dragging down database performance, a DBA can use bottleneck analysis to confirm the actual root cause. Once one or more wait events or other bottlenecks have been pinpointed as possible performance vampires, the DBA can drill down and oftentimes discover a fair amount of detail about which sessions and objects are causing the problem. The Performance Analyst Home page identifies the top system and session waits that might be currently a cause of lessened performance.

When using bottleneck analysis, a DBA cannot rely only on the information contained in the wait events that Sybase Adaptive Server provides. For example, a database might be suspect or offline. Such a failure will not be reflected in any wait event, but still represents a very real bottleneck to the database. In the same way that a DBA cannot depend on only a few ratios to properly carry out ratio-based performance analysis, an administrator must include other statistical metrics in their overall bottleneck analysis framework to obtain an accurate performance risk assessment. Performance Analyst works to identify bottlenecks in your database that fall outside of pure wait events so you can get a total picture of all stoppages in your system. The Performance Analyst Home page displays space-related bottlenecks as well as other sources of contention/bottlenecks that might be dragging down the overall performance of your system.

The following bottleneck indicators are used on the Performance Analyst Home page to succinctly communicate the general overall performance level of the monitored database:

Deadlocks

A deadlock occurs when two processes have a lock on a separate page or object and each wants to acquire a lock on the other process’ page or object. Each waits for the other to release the necessary lock. Sybase ASE constantly checks for deadlocks and, when found, chooses the transaction that has accumulated the least amount of CPU time and terminates the transaction. The server then rolls back that transaction and issues a notification of the event. The other process gets to move forward.

A deadlock occurs when two processes have a lock on a separate page or object and each wants to acquire a lock on the other process' page or object. Each waits for the other to release the necessary lock. Sybase ASE constantly checks for deadlocks and, when found, chooses the transaction that has accumulated the least amount of CPU time and terminates it (the transaction). The server then rolls back that transaction and issues a notification of the event. The other process gets to move forward.

The deadlock statistic displays the number of current deadlocks in a Sybase ASE server.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Bottleneck Analysis Pane Sybase ASE Performance Analyst Statistics > Users (Sybase ASE Performance Analyst) > Home> Bottleneck Analysis Pane

Metrics

A nonzero number should alert you to the fact that application conflicts are likely being experienced by your user community.

Correction

Well-designed applications can minimize deadlocks by always acquiring locks in the same order. You should always do updates to multiple tables in the same order. Once Sybase ASE discovers a deadlock, it takes action and remedies the situation. IDERA Performance Analyst makes it easier to discover how prevalent deadlock activity is on a system; preventing deadlocks from occurring in the first place is more difficult. Those responsible for writing systems can minimize deadlocks by ensuring that applications acquire locks in the same order. Likewise, you should always do updates and other DML that act on multiple tables in the same order. You can also shrink the amount of time that Sybase ASE waits to check for deadlocks by modifying the deadlock checking period configuration parameter.

Suspect Databases

It can be hard to recover quickly from a suspect database. There are times when the cache of the database is suspect and not the actual database itself. If you stop and start the Sybase ASE server, you can verify if the cache is the culprit. If the database itself is damaged, there could be a true recovery situation.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Bottleneck Analysis Pane Sybase ASE Performance Analyst Statistics > Databases (Sybase ASE Performance Analyst) > Home> Bottleneck Analysis Pane

Correction

The suspect database can be dropped using the DBCC DBREPAIR DROPDB command. You then need to re-create the database and perform a recovery operation using the most recent database dump.

Device I/O Contention

Device I/O Contention reflects the number of times a task or process was put to sleep while waiting for a semaphore for a particular database device.

When a task or process involves physical I/O, Sybase ASE first fills out the block I/O structure and links it to a per engine I/O queue. If two or more Sybase engines request an I/O structure from the same device at the exact same time, the server puts one of them to sleep where it waits for the semaphore it needs.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Bottleneck Analysis Pane Sybase ASE Performance Analyst Statistics > I/O (Sybase ASE Performance Analyst) > Home> Bottleneck Analysis Pane

Correction

If consistent high numbers are viewed for Device I/O Contention, you can try redistributing the tables across devices (that are located on separate hard disks) or by adding devices (again, that are located on separate hard disks) and moving tables and indexes to them.

Modify Conflicts

For some database operations, Sybase ASE uses a specialized protection mechanism to get exclusive access to a page without using actual restrictive page locks. Examples include accessing certain system tables and dirty reads on data pages. These actions need exclusive access to the page in question, even though they do not actually modify it. Modify conflicts record how many times these actions conflicted with other processes that need true database locks.

Tempdb Percent Full

The Tempdb Percent Full statistic shows the percentage of the Tempdb that is full.

Metrics

Tempdb is probably the most important resource in Adaptive Server shared by all applications and processes. It is a server-wide resource used for internal processing tasks like work tables, sorts, reformatting, and index builds.

Correction

Problems can be identified by a consistently high percentage registering or receiving a message indicating that your transactions are being suspended until space is made available in the log. In the short term, try using the “dump tran” DBCC command. If you suspect your application requires a very large tempdb, it should be placed on small fast disks. These disks should have no other data on them.

Note: On some systems, tempdb performs much faster using an operating system file rather than raw devices. This is because an operating system file uses buffering, which is faster than direct writes to a raw device.

Task Context Switches

Task Context Switches summarizes task-switch activity for all engines on SMP servers. You can use this statistic to observe the effect of reconfigurations.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Bottleneck Analysis Pane Sybase ASE Performance Analyst Statistics > Databases (Sybase ASE Performance Analyst) > Home> Bottleneck Analysis Pane

Metrics

You might reconfigure a cache or add memory if tasks appear to block on cache search misses and to be switched out often. Then, check the data to see if tasks tend to be switched out more or less often.

Group Commit Sleeps

For databases needing high throughput, a large log I/O size is very important to prevent disk queuing problems on the transaction log. Group commit sleeps reflect the number of times a task performed a transactional commit operation and was put to sleep by the server until data in the log was written to disk.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Bottleneck Analysis Pane Sybase ASE Performance Analyst Statistics > I/O (Sybase ASE Performance Analyst) > Home> Bottleneck Analysis Pane

Metrics

You should examine group commit sleeps in conjunction with the number of committed transactions (found on the Users performance category view).

Correction

A high count for group commit sleeps is not necessarily a problem if the server's transaction rate is low. If there are a significant number of transactions that result in group commit sleeps, and the log I/O size is greater than 2 KB, a smaller log I/O size can help to reduce commit time by causing more frequent page flushes. Other factors that can affect group commit sleeps are the size of the server run queue and the speed of the hard disk where the log is located.

Blocked Connections

A single blocking user has the potential to stop work for nearly all other processes on a small system, and can cause major headaches on large systems. Although Sybase ASE supports flexible locking mechanisms, blocking lock situations do crop up. Blocks are most often caused by user processes holding exclusive locks and not releasing them via a proper COMMIT frequency.

The blocked statistic displays the number of current processes blocked by other processes.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Bottleneck Analysis Pane Sybase ASE Performance Analyst Statistics > Users (Sybase ASE Performance Analyst) > Home> Bottleneck Analysis Pane

Metrics

While the lock contention rate is a better measure of the overall lock contention situation, if you consistently see positive numbers for the blocked statistic there may be a bottleneck for some processes. You can easily drill down and discover the exact process(es) holding locks that are blocking out other user activity.

Correction

Once you discover a blocking lock situation, you can normally remedy it by issuing a KILL against the offending process. This eliminates the user's stranglehold on the objects the user was accessing, and usually results in other user processes completing in an instant. Discovering the blocked lock situation is made easier by using tools like Performance Analyst, but preventing the blocking lock situation in the first place is tricky. The culprit of blocking lock scenarios is usually the application design, or the SQL being used within the application itself. Properly coding an application to reference database objects in an efficient order, and then using the right SQL to get the job done, is an art. The key to avoiding lock contention is to process user transactions in the quickest and most efficient manner possible - something not always easy to do. By default, all processes wait indefinitely for locks in Sybase ASE. You can change this behavior by modifying the lock wait period configuration parameter, which limits the number of seconds that a process waits for a lock before timing out.

Databases Low on Space

The Databases Low on Space statistic indicates if any databases in the server are approaching low levels of available free space. Although good to know, you need a more detailed listing by database to determine where any actual space shortages exist in the server.

Location

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

Metrics

If any one database begins to approach 90% used space, and is continuing to dynamically grow, you should take action to prevent any future space allocation errors.

Correction

If a database is approaching its limit on space, you can either extend the database onto a new device, or if space exists on the device where the database currently resides, you can allocate more space for the database on the current device.

Logs Low on Space

This statistic indicates if any log for a database in the Sybase ASE server is approaching low levels of available free space. Although good to know, a more detailed listing by database is needed to determine where any actual space shortages exist in the server.

Location

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

Metrics

If a database log’s used space begins to approach 90%, you should take action to prevent any future space allocation errors. If the transaction log runs out of space, no transactions can take place in the database until you free up space in the log.

Correction

If a database log consistently approaches its limit on used space, there are a few actions a DBA can take to prevent a database from freezing. A backup (dump) of the log can be taken: If log backups are not required for disaster recovery, the truncate log on checkpoint option can be set for the database. Setting this option deletes any space devoted to transactions in the log that have already completed when a database checkpoint occurs. You can extend the log onto a new device. Or, if space exists on the device on which the database log currently resides, you can allocate more space for the log on the current device.

I/O Analysis Pane

I/O statistics gives you a succinct overview of the datasource’s overall performance. The ratios on the Home page are:

Server Reads

Total Server Reads reflect the total number of physical reads performed by the database server since the last refresh.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > I/O Analysis Pane

Metrics

Large numbers of physical reads could reflect a too small data or procedure cache. You should examine the data and procedure cache hit rates to determine the overall effectiveness of logical vs. physical I/O.

Server Writes

This value reflects the total number of physical writes performed by the database server since the last refresh.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > I/O Analysis Pane

Transaction Log Writes

Transaction Log Writes refers to the number of times Sybase ASE wrote a transaction log page to disk since the last refresh. When the current log page becomes full, Sybase ASE writes it out to disk. Sybase ASE also writes transaction log pages to disk after a transaction commits.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > I/O Analysis Pane

I/O Errors

The I/O Errors value reflects total number of I/O errors (errors during read and write operations) encountered by the database server since the last refresh.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > I/O Analysis Pane

Storage Analysis Pane

While DBAs focus on memory settings and tuning SQL, they frequently forget how dangerous and insidious storage problems can be. This is not a good mindset because storage headaches can play a major role in wrecking an otherwise well-running database.

Storage problems generally take one of two forms:

  • The ‘hit-the-wall’ variety that can bring things to a complete standstill.
  • The ‘performance vampire’ kind that slowly drains the performance of a database over time.

Storage problems have the capability to bring the curtain down on a database very quickly, as in the case of a transaction log running out of free space. But storage problems can also silently work behind the scenes to slowly, but surely rob a database of its performance. For example, a hub table in a busy database might be accessed very quickly when an application is first given life, but over time, if it develops a heavy forwarded row problem, it can cause things to run very differently.

The Storage Analysis section of the Performance Analyst Home page displays the Total Used and Free Space of all tablespaces in the database. It also provides a count of the number of databases, files, and file groups that exist on the server. For more detailed information on database space, you can use the Performance Analyst Space home page or the Space Detail.

The following ratios succinctly communicate the general overall performance levels of the datasource:

Total Used

The Total Used statistic shows the total amount of space currently in use by the Sybase ASE Server.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Storage Analysis Pane

Total Free

The Total Free statistic shows the total amount of free space available to the Sybase ASE Server

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Storage Analysis Pane

Correction

A low percentage of free space indicates that additional space should be allocated to your database(s)/devices.

Number of Databases

This value indicates the total number of database currently defined on the Sybase ASE server.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Storage Analysis Pane

Number of Devices

This value indicates the total number of devices currently defined on the Sybase ASE server.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Storage Analysis Pane

Workload Analysis Pane

When your phone starts ringing with complaints of performance slowdowns, some of the first questions you should answer are:

  • Who is currently connected to the database?
  • What resources are they using?
  • What are they currently executing?

The Workload Analysis section of the Performance Analyst Home page provides insight into the leading resource hogs of a server, as well as a count of total active and inactive processes.

Workload Analysis statistics include:

Top I/O Process

The Top I/O Process statistic identifies the current SPID with the highest percentage of I/O activity.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Workload Analysis Pane

Metrics

If any one session uses more than 50% of a total resource (CPU, memory, etc.) drill-down into the session to investigate what work is currently executing.

Top Memory Process

The Top Memory Process statistic identifies the current SPID with the highest percentage of memory consumption.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Workload Analysis Pane

Metrics

If any one session uses more than 50% of a total resource (CPU, memory, etc.) drill-down into the session to investigate what work is currently executing.

Top CPU Process

The Top CPU Process statistic identifies the current SPID with the highest percentage of CPU consumption.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Workload Analysis Pane

Metrics

If any one session uses more than 50% of a total resource (CPU, memory, etc.) drill-down into the session to investigate what work is currently executing.

Active Processes

The Active Processes statistic is the total number of active and open threads reported on the Server. Active Processes displays the number of processes actively performing work.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Workload Analysis Pane

Inactive Processes

The Inactive Sessions statistic represents the total number of threads logged onto the server that are currently idle.

Location

Sybase ASE Performance Analyst Statistics > Home View (DBArtisan - Sybase ASE Performance Analyst) > Workload Analysis Pane

Metrics

A large number of inactive processes could indicate user sessions that have mistakenly been left logged on. Since each user thread consumes a portion of memory on the server, to reduce resource usage, you should sever any sessions that do not need a connection.

Correction

Drill down into the Session Waits page and check sessions that have many seconds idle and/or that have been logged on for very long periods of time, as indicated by the logon time column. After verifying that a session is no longer necessary, you can KILL it.