http://goo.gl/EmKxy0

Home > ASE, Developement, Start Sybase, Sybase ASE/REP Interview Questions > Basic tools for performance tuning

Basic tools for performance tuning

I have mentioned few basic performance tuning tools used to check the the health of query and stored procedure. These tools are basically sybase commands only.

 

Sybase optimizer is basically a cost based optimizer which calculates the cost for each query ,in terms of how much I/O performs, and then decide the appropriate plan.

For calculating the cost, optimizer uses the statistic of table columns and index data which happens to be stored in the form of histogram. It then chooses the access method that the optimizer determines has the least cost.

Sybase has a command called “update statistics” to store the statistics in “systatics” table.

Syntax

—-

update statistic table_name {(column list) | (index name)]

Tools

—-

1. Set statistics io

2. set statistic time

3. set showplan

4. set noexec

5. set fmtonly

 

set statistics io

————

syntax

set statistic io on

go

Description:It will show the number of logical reads and physical reads  and total I/O reads

Table: Author scan count 1,

logical reads: (regular=465 apf=0 total=465),

physical reads: (regular=324 apf=234 total=558), apf IOs used=234

Total actual I/O cost for this command: 1023.

Total writes for this command: 0

 

set statistic time

————

syntax

set statistic time on

go

Description: It will show the time taken to parse and compile the query, execution time etc.

Parse and Compile Time 35.   (CPU ticks)

SQL Server cpu time: 35ms.

Execution Time 140.

SQL Server cpu time:14000 ms. SQL Server elapsed time: 60123ms.

 

set showplan

———–

syntax

set showplan on

go

Description: It will show the query paln of query.

 

set noexec on

———–

syntax

set noexec on

go

Description: If you want to see the query plan without executing the query then use both commands together as below

set showplan on

go

set noexec on

go

select * from Author

go

set fmtonly on

———–

syntax

set fmtonly on

go

Describe: It is used to show the query plan of a stored procedure without executing the stored procedure. Use both commands together as below.

set showplan on

go

set fmtonly on

go

exec getAuthorsData

go

Check the Sybase Wiki @ sybasewiki.com
  1. No comments yet.
  1. No trackbacks yet.