Foglight for Sybase 5.7.5.40 - User and Reference Guide

Investigating Network Activity

Investigating Network Activity

The Foglight Cartridge for Sybase assists your investigation into network usage. Network activity refers to the number of bytes and packets that are sent and received by your network.

To start with the SYBM_OverviewNetwork graph:

•	Use the SYBM_OverviewNetwork table view to get a server-wide look at network activity detailed by bytes and packets both sent and received.

Watch for packet errors. An increase in errors paired with a decrease in traffic can highlight network issues that may need attention by your network administrator.

Average packet size can help you size your Adaptive Server.

In most situations, the number of packets being transferred is more pertinent than the size of the packets. It should be noted that overall network performance also includes the time needed by the CPU and operating system to process a network packet. This overhead for each packet affects performance the most. Larger packets decrease the overall overhead costs and achieves higher physical throughput, provided that you have enough data to be sent.

To view more detailed information:

•	Drill down on the Bytes Received or Packets Received columns to view the SYBM_NetworkBytes graph or SYBM_NetworkPackets graph views respectively. These graph views display the number of bytes or packets data received and sent through the network.

Investigating Parallel Queries

Investigating Parallel Queries

Parallel query optimization is the process of analyzing a query and choosing the best combination of parallel and serial access methods to yield the fastest response time for the query. In addition to the costing performed for serial query optimization, parallel optimization analyzes the cost of parallel access methods for each combination of join orders, join types, and indexes.

To start with the SYBM_ProcessesParallel graph:

•	Use the graph to tune your server parameters to effectively carry out your parallel query strategy. The Foglight Cartridge for Sybase assists your investigation into parallel query limits and adjustments based on availability of threads with the SYBM_ProcessesParallel graph view.

A high value for the “number of plans altered” can indicate that the number of worked processes configured were not adequate for the Adaptive Server to complete the queries using the degree of parallelism determined by the optimizer.

Measure this against the number of parallel queries attempted to determine if the number of plans altered is an issue.

Use the max threads and the users with threads values to help size your environment needs for parallel queries.

Investigating Procedure Cache

Investigating Procedure Cache

The procedure cache is used for stored procedures, triggers, and short-term memory needs such as statistics and query plans for parallel queries.

If more than one user uses a procedure or trigger simultaneously, there will be multiple copies of it in cache. If the procedure cache is too small, a user trying to execute stored procedures or queries that fire triggers receives an error message and must resubmit the query. Space becomes available when unused plans age out of the cache.

The Foglight Cartridge for Sybase assists your investigation of Procedure Cache usage patterns. This information helps a DBA tune the size of memory allocated to the procedure cache in an efficient manner.

To start with the SYBM_ProcedureCache graph:

•	The SYBM_ProcedureCache graph view displays the number of procedure load, requests, writes, and stalls encountered during the collection period.

On a production server, you want to minimize the procedure reads from disk. When a user needs to execute a procedure, Adaptive Server should be able to find an unused tree or plan in the procedure cache for the most common procedures. The percentage of times the server finds an available plan in cache is called the cache hit ratio. Keeping a high cache hit ratio for procedures in cache improves performance.

Investigating Resource Utilization

Investigating Resource Utilization

Foglight lets you track several key resource utilizations to help you plan Adaptive Server capacity.

To start with the SYBM_ResourceOverview graph:

•	Open the SYBM_ResourceOverview graph view which shows the utilization percentage for locks, connections, and memory.

To view more detailed information:

•	Drill down on the Connections (%) line to see the SYBM_ResourceConnections graph view. This view tracks used and free connections.

•	Drill down on the Locks (%) line to see how many locks are used and free in the SYBM_ResourceLocks graph view. See Investigating Locking for more detail information on tracking Sybase Adaptive Server Locking effectiveness and how to handle configuring the number of locks on an Adaptive Server.

You can increase the “number of user connections”. If your Adaptive Server is running out of user connections, use the SP_configure system procedure to increase their number. Take note of the @@max_connections global variable to determine how many connections the server supports. Adding additional engines increases the @@max_connections value and, in turn, you can increase the number of user connections. Be aware that decreasing the number of engines requires that the number of user connections be lowered as well. Both of these parameters are not dynamic and require a reboot of the system. Also, increasing the number of connections has a direct impact on memory allocation for the Adaptive Server. Review this in the Sybase system administration guide for full details.

You can use the SYBM_Processes graph view to see the distribution of the connections on your Adaptive Server (idle, active, blocked, etc.).

•	Drill down on the Logical Memory (%) line to the SYBM_ResourceMemory graph view to determine current allocation for physical and logical memory.