sybase ase pnt tools

Sybase ASE Performance & Tuning

Sarosh Siddiqui [email protected]

What is Performance & Tuning

Tuning Tool Box

◦ Tools and Uti l i t ies

◦ Set Commands

◦ System Procedures

◦ Monitoring Tables

◦ sp_sysmon

AGENDA

Performance is characterized by amount of work accomplished by a system compared to the time and resources used.

Performance may involve one or more of the following. ◦ Throughput : Number of transactions the db can do

in a given time period ◦ Response Time : Time it takes to get a response for

an inquiry ◦ Scalability : The ability to support large number of

users and large data without impacting performance

Tuning

Tuning is the process of tacking the appropriate actions within the limit of available resources.

Tuning is tradeoffs between

◦ Consistency or Concurrency (Locking)

◦ Select and Update (Workload)

◦ Space and Time (Indexing)

Tuning

Consistency or Concurrency:

Adaptive Server is designed for a high number of

simultaneous users. It makes use of various locking mechanisms to prevent users from interfering with each other as they compete for the same recourse.

Tuning

Selects or Updates:

The tradeoff between selects (read-only) and updates ( read

and/or write) becomes important when you are determining the proper database structures for an application.

Index may speed up selects but slow down updates.

Tuning

Space or Time:

The tradeoff between space and time is a classic computer

system issue.

Normalized data takes less space, but may require complicated joins and thereby slow access time .

DBMS

OS

Hardware

Network

Application

General Principles of Performance & Tuning

Performance and tuning is done for a specific server, or a group

of identical servers with identical workloads, in a specific

environment

There are no „magic number for performance and tuning

configuration parameters. Just consider all the factors that can

have an impact on performance.

Continue ….

General Principles of Performance & Tuning

The Performance impacts on:

• Server Computer Hardware and Operating System

• Client Computer Hardware and Operating System

• The Size, structure, number, and characteristics of databases(s)

• Front-end Application

• Query

• Network Architecture

So, you much determine which tuning parameter value

optimize performance for one server or group of identical

servers in your specific environment.

Performance & Tuning is an art, not a science

There are many environmental factors that can have an impact

on performance. Thee are many tuning strategy to chose from,

and number of parameters that can be set.

In the face of this complexity, all you can do is to use your

knowledge and experience to make right judgment, about

which configurations might work, try each, then measure

performance and compare the results.

• Ut i l i t ies :

Tools that exist as separate executables / binaries in

Adaptive Server.

• Set Commands:

Tools run at the session level that are preceded by keyword set

Tuning Tool Box

• System Procedures:

Tools in the collection of store procedure that are

provided with the server and primarily resides in sybsystemprocs system database

• Monitor ing Tables :

A Set of system tables containing monitoring and

diagnostic information.

sp_sysmon : a system procedure that produces

Adaptive Server performance data. It includes suggestions for interpreting its output and deducing possible implications.

Tuning Tool Box

• isql -p:

Interactive sql provides a performance statistics with

option (-p).

• optdiag:

Displays optimizer statistics or loads updated

statistics into system tables.

• Query Process Metrics: Query Processing (QP)

matrix identify and compare empirical matrix values in query execution.

Utilities

• Precise i3 indepth for Sybase: Precise Indepth for Sybase, subsequently referred to as Indepth for Sybase, provides you with the application performance management you need, by supplying you with the tools necessary for proactive monitoring, diagnosing, detecting, and correcting of performance problems, before they become visible in your Sybase databases and related applications.

Utilities

Interactive sql with –p option capture single / batch

execution time statistics.

From prompt

$isql –Uuser_id –Ppassword –p 1> select name from sysdatabases where name = 'pubs2'

2> go 10

Execution Time (ms.): 0 Clock Time (ms.): 0









name

------------------------------

pubs2

(1 row affected)


10 xacts:

Isql -p

optdiag

• The optdiag utility display statistics form systabstatics and sysstatistics system tables.

• optdiag can also be used to update ststistics information.

• Only a system administrator can run optdiag utility

Continue .....

optdiag

optdiag Syntax:

optdiag [binary] [simulate] statistics {-i input_file |

database[.owner[.[table[.column]]]] [-o output_file]} [-U

username] [-P password] [-I interfaces_file] [-S server]

[-v] [-h] [-s] [-Tflag_value] [-z language] [-J

client_charset] [-a display_charset]

Optdiag display the statistics for an entire database, for a single table and its indexes and columns, or for a particular column. To display statistics for all user tables in the pubtune database, placing the output in the pubtune.opt file, use the following command: optdiag statistics pubtune -Usa -Ppasswd -o pubtune.opt

Query Process Metrics

•Query processing (QP) metrics identify and compare empirical metric values in query execution. • When a query is executed, it is associated with a set of defined metrics that are the basis for comparison in QP metrics. The metrics captured include: •CPU execution time – the time, in milliseconds, it takes to execute the query. •Elapsed time – the difference in milliseconds between the time the command started and the current time, as taken from the operating system clock.

Continued ….


The metrics captured include: •Logical IO (LIO) reads – the number od Logical IO reads. •Physical IO (PIO) reads – the number of Physical IO reads. •Count – the number of times a query is executed. •Abort count – the number of times a query is aborted by the resource governor due to a resource limit being exceeded. Each metric has three values: minimum, maximum, and average. Count and abort count are not included.


You can activate and use QP metrics at the server level or at the session level. To activate at server level, use: sp_configure “enable metrics capture”, 1

To activate at session level, use:

set metrics_capture on/off

Access metric information using a select statemant with order by against the sysquerymetrics view

Monitor Server

Adaptive Server Enterprise Monitor™ (Adaptive Server Monitor) provides a way to monitor Adaptive Server performance in real time or in a historical data-gathering mode. System administrators can use this information to identify potential resource bottlenecks, to research current problems, and to tune for better performance. Adaptive Server Monitor provides feedback for tuning at several levels:

•Adaptive Server configuration

•Database design

•SQL statements in applications and stored procedures

Monitor Server - Components

Four components that gather or display Adaptive Server Performance data: •Adaptive Server Enterprise Monitor Server (Monitor Server) •Adaptive Server Enterprise Historical Server (Historical Server) •Monitor in Adaptive Server Enterprise plug-in for Sybase Central (Monitor View) •Adaptive Server Enterprise Monitor Client Library (Monitor Client Library)

DBArtisan

Embarcadero® DBArtisan®, the leading cross-platform

database administration tool, helps DBAs maximize

availability, performance, and security. DBArtisan boosts

productivity so DBAs can manage larger, more complex

databases. DBArtisan is a popular DBA tool. You may find some DBA job posting even has requirement on familiarity on this tool. The tool itself is very simple and not much different from the native tool from database vendors, the main strength is it provide same interface across all platform (Oracle, Sybase, DB2 and SQL Server).

Precise i3 Indepth for Sybase Performance management for Sybase environnements :

Precise i3 Indepth for Sybase provides DBAs with current and historical information that can identify database performance bottlenecks.

Precise i3 Indepth for Sybase

Indepth displays resource consumption by statements, logins, programs, and more.

• statistics io:

Get a count of read and write activity, both logical and

physical for a given statement.

• statistics time:

Get count of CPU time use and elapsed time for a

given statement.

set Commands

• showplan:

Show the plan chosen by the optimizer for handling a

particular statement, including access method, index use,

execution steps, I/O size, number of parallel worker

processors, join order, worktables used and so on.

• showplan and noexec: Without executing the statement, show the plan the

optimizer would chose.

set Commands

statistics io

Syntax:

set statistics io { on | off }

Example: 1> set statistics io on

2> go

Total writes for this command: 0

1> use pubs2

2> go


1> select * from authors

2> go

.

Table: authors scan count 1, logical reads: (regular=1 apf=0

total=1), physical reads: (regular=1 apf=0 total=1), apf IOs

used=0


(23 rows affected)

statistics io

Defination:

Scan Count: The number of times the tables accessed

Logical reads: Logical reads are the total number of

physical read (including physical and cache read) reported

in 2k pages.

Physical reads: Physical I/O is the total number of physical

reads. If the I/O size is larger than 2k, each larger I/O

count as a single physical read.

Total writes from this command: The number of pages

written to disk.

statistics time Syntax:

set statistics time { on | off }

Example: 1> use pubs2

2> go

1> set statistics time on

2> go

Execution Time 0.

SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

1> select * from authors

2> go

Parse and Compile Time 0.

SQL Server cpu time: 0 ms.

.

Execution Time 0.

SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

(23 rows affected)

statistics time Defination:

Parse and Compile time: The number of CPU ticks taken to parse, optimize, and compile the statement.

Execution time: The number of CPU ticks taken to execute the statement.

CPU time: Total number of CPU milliseconds taken to execute the query.

Elapsed time: The difference between the time the command started and the current time, as taken from the operating system clock.

showplan and noexec Syntax:

set showplan [,noexec] { on | off }

Example: 1> set showplan, noexec on

2> go

1> select name from sysdatabases where name = 'pubs2'

2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).

1 operator(s) under root

The type of query is SELECT.

ROOT:EMIT Operator

|SCAN Operator

| FROM TABLE

| sysdatabases

| Using Clustered Index.

| Index : cdatabases

| Forward Scan.

| Positioning by key.

| Index contains all needed columns. Base table will not be read.

| Keys are:

| name ASC

| Using I/O Size 2 Kbytes for index leaf pages.

| With LRU Buffer Replacement Strategy for index leaf pages.

showplan and noexec Defination:

QUERY PLAN: Marks the beginning of each query plan.

STEP: Sequential number of each step and each statement .

The type of query: Report the type of query select, insert, update query.

Nested iteration: Indicates the execution of data retiieval

Loop.

Table scan: Report when the query perform a table scan

showplan and noexec Defination:

Ascending scan: Indicates the direction of the scan (Ascending or Descending )

Positioning at start of table: Indicates where the scan begin.

Using I/O Size: The I/O size being used: (2, 4, 8, 16k).

With LRU Buffer Replacement Strategy: The buffer replacement strategy being used.

System procedures are: •Shortcuts for retrieving information from the system tables •Mechanisms for performing database administration and other tasks that involve:

•Process Activity: what is active on the system, what lock have been taken, what work is being performed? •Configuration: How do you view and control characteristics of the server that can be manipulated ( for example total memory used) •Task Activity: What is happening in the system in response to the actions of users on the system.

System Procedures

System Procedures Process Activity

sp_who Syntax: sp_who [ login name | spid”]

Display a list of currently active processes to include a reference to inter-process blocking and related delays

sp_lock Syntax: sp_lock [spid1, spid2 ..]

Display a list of table, page, and rows lock active on the system that limit access for users.

sp_showplan Syntax: sp_showplan spid, null, null, null

Display the optimizers query plan for a currently running processes.

sp_object_stats Syntax: sp_object_stats interval [, top_n [,

dbname, objname [, rpt_option ]]]

Show lock contention, lock wait time, and deadlock statistics for tables and indexes.

System Procedures Configuration

sp_configure Syntax: sp_configure [configname [,

configvalue] | group_name |

non_unique_parameter_fragment]

sp_configure "configuration

file", 0, {"write" | "read" |

"verify" | "restore"}

"file_name"

Displays configuration parameters by group, their current values, their default values, the value to which they have most recently been set, and the amount of memory used by this setting. Displays only the parameters whose display level is the same as or below that of the user.

sp_cacheconfig Syntax: sp_cacheconfig [cachename [

,"cache_size[P|K|M|G]" ]

[,logonly | mixed ] [,strict |

relaxed ] ] [,

"cache_partition=[1|2|4|8|16|32

|64]"]

Creates, configures, reconfigures, and drops data caches, and provides information about them

System Procedures Configuration

sp_poolconfig Syntax: To create a memory pool in an

existing cache, or to change pool

size:

sp_poolconfig cache_name [,

"mem_size [P|K|M|G]", "config_poolK"

[, "affected_poolK"]] To change a

pool’s wash size:

sp_poolconfig cache_name, "io_size

", "wash=size[P|K|M|G]" To change a

pool’s asynchronous prefetch

percentage:

sp_poolconfig cache_name, "io_size

",

"local async prefetch limit=percent

"

Creates, drops, resizes, and provides information about memory pools within data caches.

sp_monitorconfig Syntax: sp_monitorconfig "configname" [ ,

“result_tbl_name”] [, “full”]

Displays cache usage statistics regarding metadata descriptors for indexes, objects, and databases.

System Procedures Performance

sp_sysmon Syntax: sp_sysmon begin_sample

sp_sysmon interval [,

noclear,[,section [, applmon]]]

sp_sysmon { end_sample | interval }

[, section [, applmon] ] sp_sysmon {

end_sample | interval } [, applmon ]

Displays performance information.

sp_monitor Syntax: sp_monitor [ connection, [cpu |

diskio | elapsed time ] ] [event,

[spid ] ] [procedure, [ dbname, [

procname, [, summary | detail ] ] ]

] [ enable ] [ disable ] [

statement, [ cpu | diskio | elapsed

time ] ] [ help], [ connection |

statement | procedure | event ] ]

Displays statistics about Adaptive Server.

• MDA tables

• How i t work

• Overv iew

• Insta l la t ion and Conf igurat ion

• Examples & appl icat ions

Monitoring Tables

Starting in ASE 12.5.0.3, a new feature called 'MDA tables„

is available to ASE users. These MDA tables provide

access to low-level monitoring information in ASE; since

the MDA tables can be accessed with regular SQL select

statements, they're much easier to use than products like

Monitor Server/Historical Server.

MDA = 'Monitoring Data Access” (or “Monitoring and Diagnostics for ASE” or “Monitoring and Diagnostic Access or “Monitoring and Diagnostic API ”)

Monitoring Tables

Only users with the mon_role role can access Adaptiver

Server‟s monitoring tables.

By deafult, Adaptive Server does not collect the

monitoring information required by the monitoring tables.

You must use sp_configure to configure Adaptive

Server to start collecting the monitoring information.

proxy tables mapped to native RPCs

Monitoring Tables

• Only users with the mon_role role can access Adaptiver

Server‟s monitoring tables.

• By deafult, Adaptive Server does not collect the

monitoring information required by the monitoring tables.

• You must use sp_configure to configure Adaptive

Server to start collecting the monitoring information.

• 35 proxy tables in the master database

• All named 'mon%' (i.e. 'monObjectActivity')

• Can be queried with regular SQL

Monitoring Tables

MDA licensing:

• no license needed • included in ASE base product

Monitoring Tables

Monitoring Tables

Out-of-the-box: no MDA tables present.

Installation steps: Add a 'loopback' server alias name

sp_addserver loopback, null, @@servername

(assuming @@servername is also in the interfaces file) Test this configuration: exec loopback…sp_who (note: 3 dots!)

Run $SYBASE/ASE-12_5/scripts/installmontables

(when successful, prints no output whatsoever!)

Assign mon_role to logins allowed MDA access (incl. sa) grant role mon_role to sa Test this configuration: select * from master..monState

Monitoring Tables System Configuration

There are 18 sp_configure parameters that control the behavior of Monitoring Tables. Grant mon_role to a uset for accessing the Monitoring Tables. These parameters may be displayed by referencing the “Monitoring Group”

sp_configure Monitoring

Component Integration Services must be enabled to use Monitoring tables. sp_configure “enable cis”, 1

sp_sysmon, a system procedure that produces Adaptive

Server performance data. It includes suggestions for

interpreting its output and deducing possible

implications. When you invoke sp_sysmon, it clears all accumulated data from a set of counters that will be used during the sample interval to accumulate the results of user and system activity. At the end of the sample interval, the procedure reads the values in the counters, prints the report, and stops executing. sp_sysmon contributes 5 to 7% overhead while it runs on a single CPU server, and more on multiprocessor servers. The

amount of overhead increases with the number of CPUs.

sp_sysmon Store Procedure System Monitor

You can run sp_sysmon both before and after tuning Adaptive Server configuration parameters to gather data for comparison. This data gives you a basis for performance tuning and lets you observe the results of configuration changes. Use sp_sysmon when the system exhibits the behavior you want to investigate. For example, if you want to find out how the system behaves under typically loaded conditions, run sp_sysmon when conditions are normal and typically loaded. In many tests, it is best to start the applications, and then start sp_sysmon when the caches have had a chance to reach a steady state. If you are trying to measure capacity, be sure that the amount of work you give the server keeps it busy for the duration of the test.

sp_sysmon When to run

In general, sp_sysmon produces valuable information when you use it:

•Before and after cache or pool configuration changes

•Before and after certain sp_configure changes

•Before and after the addition of new queries to your application mix

•Before and after an increase or decrease in the number of Adaptive

Server engines

•When adding new disk devices and assigning objects to them

•During peak periods, to look for contention or bottlenecks

•During stress tests to evaluate an Adaptive Server configuration for a

maximum expected application load

•When performance seems slow or behaves abnormally

sp_sysmon When to run

There are two ways to use sp_sysmon:

1. Using a fixed time interval to provide a sample for a specified number of minutes

2. Using the begin_sample and end_sample parameters to start and stop sampling

You can also tailor the output to provide the information you need: • You can print the entire report. • You can print just one section of the report, such as “Cache Management” or “Lock Management.”

sp_sysmon Invoking

To invoke sp_sysmon, execute the following command using isql: sp_sysmon interval [, section [, applmon]]

interval must be in the form “hh:mm:ss”. To run sp_sysmon for 10 minutes, use this command: sp_sysmon "00:10:00"

The following command prints only the “Data Cache Management” section of the report: sp_sysmon "00:10:00", dcache

sp_sysmon Fixed time intervals

With the begin_sample and end_sample parameters, you can

invoke sp_sysmon to start sampling, issue queries, and end the sample and print the results at any point in time. For example: sp_sysmon begin_sample

execute proc1

execute proc2

select sum(total_sales) from titles

sp_sysmon end_sample

Note : On systems with many CPUs and high activity, counters can overflow if the sample period is too long. If you see negative results in your sp_sysmon output, reduce your sample time.

sp_sysmon Using begin_sample and end_sample

Partial Syntax: sp_sysmon {interval [, section]}

List of few Report Section and Parameter

sp_sysmon Specifying report sections for output

Report Section Parameter

Application Management appmgmt

Cache Wizard cache wizard

Data Cache Management dcache

Disk I/O Management diskio

Houskeeper Task Activity housekeeper

Index Management indexmgmt

Kernel Utilization kernel

Lock Management locks

Memory Management memory

It is easier to handle the output when looking a part of the report Example: 1> exec sp_sysmon “00:10:00”, dcache

2> go

sp_sysmon Specifying report sections for output

Partial Syntax: sp_sysmon {interval [, section] [,applmon]}

Application Detail Parameter

sp_sysmon Specifying report sections with applmon

Parameter Information Reported

appl_only CPU, I/O, priority changes, and resource limit violations by application name.

appl_and_login CPU, I/O, priority changes, and resource limit violations by application name and login name. Can be used with all sections.

no_appl Skips the application and login section of the report. This is the default.

Example: runs sp_sysmon for 5 minutes and prints the “Application Management” section, including the application and login detail report sp_sysmon "00:05:00", appmgmt, appl_and_login

Thanks

sybase ase pnt tools

Documents