1.5.1 What are the nitty gritty details on Performance and Tuning?
1.5.2 What is best way to use temp tables in an OLTP environment?
1.5.3 What's the difference between clustered and non-clustered indexes?
1.5.4 Optimistic versus pessimistic locking?
1.5.5 How do I force an index to be used?
1.5.6 Why place tempdb and log on low numbered devices?
1.5.7 Have I configured enough memory for ASE?
1.5.8 Why should I use stored procedures?
1.5.9 I don't understand showplan's output, please explain.
1.5.10 Poor man's sp_sysmon.
1.5.11 View MRU-LRU procedure cache chain.
1.5.12 Improving Text/Image Type Performance
Before going any further, Eric Miner (eric.miner@sybase.com) has made available two presentations that he made at Techwave 1999. The first covers the use of optdiag. The second covers features in the way the optimiser works in ASE 11.9.2 and 12. These are Powerpoint slides converted to web pages, so they might be tricky to read with a text based browser!
We often think that high performance is defined as a fast data server, but the picture is not that simple. Performance is determined by all these factors:
Unlike some products which aim at performance on paper, Sybase aims at solving the multi-dimensional problem of delivering high performance for real applications.
OBJECTIVES
To gain an overview of important considerations and alternatives for the design, development, and implementation of high performance systems in the Sybase client/server environment. The issues we will address are:
Client Application and Physical Database Design design decisions will account for over 80% of your system's "tuneable" performance so ... plan your project resources accordingly !
It is highly recommended that every project include individuals who have taken Sybase Education's Performance and Tuning course. This 5-day course provides the hands-on experience essential for success.
Client Application Issues
This is short for search arguments. A search argument is essentially a constant value such as:
but not:
SELECT name FROM employee WHERE salary * 12 > 100000
Float & Int, Char & Varchar, Binary & Varbinary are Incompatible;
Int & Intn (allow nulls) OK
SELECT * FROM orders(2), order_detail(1) WHERE ...
This may cause portability issues should index id's vary/change by site !
Prior to ASE 10 cursors, many developers simulated cursors by using two or more connections (dbproc's) and divided the processing between them. Often, this meant one connection had a SELECT open while "positioned" UPDATEs and DELETEs were issued on the other connection. The approach inevitably leads to the following problem:
Since Connection A never requests a lock which Connection B holds, this is NOT a true server-side deadlock. It's really an "application" deadlock !
FETCH -> Open Client <- N rows
Buffers
ASE was not designed as a batch subsystem! It was designed as an RBDMS for large multi-user applications. Designers of batch-oriented applications should consider the following design alternatives to maximize performance :
Design Alternatives :
Many, if not most, applications and 3rd Party tools are coded to send queries with the DB-Library call dbsqlexec( ) which is a synchronous call ! It sends a query and then waits for a response from ASE that the query has completed !
Designing your applications for asynchronous queries provides many benefits:
Implementation Choices:
The two best alternatives are outlined below.
BEGIN TRAN
UPDATE pk_table SET nextkey = nextkey + 1
[WHERE table_name = @tbl_name]
COMMIT TRAN
/* Now retrieve the information */
SELECT nextkey FROM pk_table
WHERE table_name = @tbl_name]
In v4.9.x and below, only one row may be affected and the optimizer must know this in advance by choosing a UNIQUE index. System 10 eliminated this limitation.
This is the only way to guarantee logical seperation of objects on devices to reduce contention for internal resources.
Breaking up one logical database into multiple smaller databases increases the number number of transaction logs working in parallel.
Pre-v4.6 TDS Does Not Optimize Network Performance Current ASE TDS packet size limited to 512 bytes while network frame sizes are significantly larger (1508 bytes on Ethernet and 4120 bytes on Token Ring).
The specific protocol may have other limitations!
For example:
Optimal value = ((# connections using large packets large packetsize * 3) + an additional 1-2% of the above calculation for overhead)
Each connection using large packets has 3 network buffers: one to read; one to write; and one overflow.
If not enough memory is available in this pool, the server will give a smaller packet size, down to the default
$SYBASE/dataserver -T1610 -d /usr/u/sybase/master.dat ...
Your errorlog will indicate the use of this option with the message:
ASE booted with TCP_NODELAY enabled.
Resource Where to Look --------- -------------- CPU Performance SQL Monitor - CPU and Trends Physical I/O Subsystem OS Monitoring tools - iostat, sar... Transaction Log SQL Monitor - Device I/O and Device Hit Rate on Log Device ASE Network Polling SQL Monitor - Network and Benchmark Baselines Memory SQL Monitor - Data and Cache Utilization
It is imperative that you only use sp_configure to change those parameters that it currently maintains because the process of reconfiguring actually recalculates a number of other buildmaster parameters. Using the Buildmaster utility to change a parameter "managed" by sp_configure may result in a mis-configured server and cause adverse performance or even worse ...
Total Memory = Dataserver Executable Size (in bytes) +
Static Overhead of 1 Mb +
User Connections x 40,960 bytes +
Open Databases x 644 bytes +
Locks x 32 bytes +
Devices x 45,056 bytes +
Procedure Cache +
Data Cache
(Note from Ed: It appears that with ASE 12, Sybase have solved the problem of select/into locking the system tables for the duration of the operation. The operation is now split into two parts, the creation of the table followed byt the insert. The system tables are only locked for the first part, and so, to all intents and purposes, the operation acts like a create/insert pair whilst remaining minimally logged.
Our shop would like to inform folks of a potential problem when using temporary tables in an OLTP environment. Using temporary tables dynamically in a OLTP production environment may result in blocking (single-threading) as the number of transactions using the temporary tables increases.
This warning only applies for SQL that is being invoked frequently in an OLTP production environment, where the use of "select into..." or "create table #temp" is common. Application using temp tables may experience blocking problems as the number of transactions increases.
This warning does not apply to SQL that may be in a report or that is not used frequently. Frequently is defined as several times per second.
Our shop was working with an application owner to chase down a problem they were having during peak periods. The problem they were having was severe blocking in tempdb.
What was witnessed by the DBA group was that as the number of transactions increased on this particular application, the number of blocks in tempdb also increased.
We ran some independent tests to simulate a heavily loaded server and discovered that the data pages in contention were in tempdb's syscolumns table.
This actually makes sense because during table creation entries are added to this table, regardless if it's a temporary or permanent table.
We ran another simulation where we created the tables before the stored procedure used it and the blocks went away. We then performed an additional test to determine what impact creating temporary tables dynamically would have on the server and discovered that there is a 33% performance gain by creating the tables once rather than re-creating them.
Your mileage may vary.
To make things better, do the 90's thing -- reduce and reuse your temp tables. During one application connection/session, aim to create the temp tables only once.
Let's look at the lifespan of a temp table. If temp tables are created in a batch within a connection, then all future batches and stored procs will have access to such temp tables until they're dropped; this is the reduce and reuse strategy we recommend. However, if temp tables are created in a stored proc, then the database will drop the temp tables when the stored proc ends, and this means repeated and multiple temp table creations; you want to avoid this.
Recode your stored procedures so that they assume that the temporary tables already exist, and then alter your application so that it creates the temporary tables at start-up -- once and not every time the stored procedure is invoked.
That's it! Pretty simple eh?
The upshot is that you can realize roughly a 33% performance gain and not experience the blocking which is difficult to quantify due to the specificity of each application.
Basically, you cannot lose.
If you have an application that creates the same temp table many times within one connection, here's how to convert it to reduce and reuse temp table creations. Raymond Lew has supplied a detailed example for trying this.
open connection
loop until time to go
exec procedure vavoom_often
/* vavoom_often creates and uses #gocart for every call */
/* eg: select * into #gocart from gocart */
go
.
.
.
loop-end
close connection
open connection
/* Create the temporary table outside of the sproc */
select * into #gocart from gocart where 1 =2 ;
go
loop until time to go
exec procedure vavoom_often
/* vavoom_often reuses #gocart which */
/* was created before exec of vavoom_often */
/* - First statement may be a truncate table #gocart */
/* - Execute with recompile */
/* if your table will have more than 10 data pages */
/* as the optimizer will assume 10 data pages for temp tables */
go
.
.
.
loop-end
close connection
Note that it is necessary to call out the code to create the table and it becomes a pain in the butt because the create-table statement will have to be replicated in any stored proc and in the initialization part of the application - this can be a maintenance nuisance. This can be solved by using any macro package such as m4 or cpp. or by using and adapting the scripts from Raymond Lew.
Brian Black posted a stronger notice than this to the SYBASE-L list, and I would agree, that any use of select/into in a production environments should looked at very hard. Even in DSS environments, especially if they share tempdb with an OLTP environment, should use select/into with care.
From: Raymond Lew
At our company, we try to keep the database and the application loosely coupled to allow independent changes at the frontend or the backend as long as the interface stays the same. Embedding temp table definitions in the frontend would make this more difficult.
To get away from having to embed the temp table definitions in the frontend code, we are storing the temp table definitions in the database. The frontend programs retrieve the definitions and declare the tables dynamically at the beginning of each session. This allows for the change of backend procedures without changes in the frontend when the API does not change.
Enclosed below are three scripts. The first is an isql script to create the tables to hold the definitions. The second is a shell script to set up a sample procedure named vavoom. The third is shell script to demonstrate the structure of application code.
I would like to thank Charles Forget and Gordon Rees for their assistance on these scripts.
--start of setup------------------------------------------------------
/* Raymond Lew - 1996-02-20 */
/* This isql script will set up the following tables:
gocart - sample table
app_temp_defn - where temp table definitions are stored
app_temp_defn_group - a logical grouping of temp table definitions
for an application function
*/
/******************************/
/* gocart table - sample table*/
/******************************/
drop table gocart
go
create table gocart
(
cartname char(10) null
,cartcolor char(30) null
)
go
create unique clustered index gocart1 on gocart (cartname)
go
insert into gocart values ('go1','blue ')
insert into gocart values ('go2','pink ')
insert into gocart values ('go3','green ')
insert into gocart values ('go4','red ')
go
/****************************************************************/
/* app_temp_defn - definition of temp tables with their indexes */
/****************************************************************/
drop table app_temp_defn
go
create table app_temp_defn
(
/* note: temp tables are unique only in first 13 chars */
objectname char(20) not null
,seq_no smallint not null
,defntext char(255) not null
)
go
create unique clustered index app_temp_defn1
on app_temp_defn (objectname,seq_no)
go
insert into app_temp_defn
values ('#gocart',1,'select * into #gocart')
insert into app_temp_defn
values ('#gocart',2,' from gocart where 1=2 ')
go
insert into app_temp_defn
values ('#gocartindex',1,
"create unique index gocartindex on #gocart (cartname) ")
go
insert into app_temp_defn
values ('#gocart1',1, 'select * into #gocart1 from gocart where 1=2')
go
/***********************************************************************/
/* app_temp_defn_group - groupings of temp definitions by applications */
/***********************************************************************/
drop table app_temp_defn_group
go
create table app_temp_defn_group
(
appname char(8) not null
,objectname char(20) not null
)
go
create unique clustered index app_temp_defn_group1
on app_temp_defn_group (appname,objectname)
go
insert into app_temp_defn_group values('abc','#gocart')
insert into app_temp_defn_group values('abc','#gocartindex')
go
/***********************************************************/
/* get_temp_defn - proc for getting the temp defn by group */
/***********************************************************/
drop procedure get_temp_defn
go
create procedure get_temp_defn
(
@appname char(8)
)
as
if @appname = ''
select defntext
from app_temp_defn
order by objectname, seq_no
else
select defntext
from app_temp_defn a
, app_temp_defn_group b
where a.objectname = b.objectname
and b.appname = @appname
order by a.objectname, a.seq_no
return
go
/* let's try some tests */
exec get_temp_defn ''
go
exec get_temp_defn 'abc'
go
--end of setup --------------------------------------------------
--- start of make.vavoom --------------------------------------------
#!/bin/sh
# Raymond Lew - 1996-02-20
#
# bourne shell script for creating stored procedures using
# app_temp_defn table
#
# demo procedure vavoom created here
#
# note: you have to change the passwords, id and etc. for your site
# note: you might have to some inline changes to make this work
# check out the notes within the body
# get the table defn's into a text file
#
# note: next line :you will need to end the line immediately after eot \
isql -Ukryten -Pjollyguy -Sstarbug -w255 << eot \
| grep -v '\-\-\-\-' | grep -v 'defntext ' | grep -v ' affected' > tabletext
exec get_temp_defn ''
go
eot
# note: prev line :you will need to have a newline immediately after eot
# go mess around in vi
vi tabletext
#
# create the proc vavoom after running the temp defn's into db
#
isql -Ukryten -Pjollyguy -Sstarbug -e << eot |more
`cat tabletext`
go
drop procedure vavoom
go
create procedure vavoom
(
@color char(10)
)
as
truncate table #gocart1 /* who knows what lurks in temp tables */
if @color = ''
insert #gocart1 select * from gocart
else
insert #gocart1 select * from gocart where cartcolor=@color
select @color '@color', * from #gocart1
return
go
exec vavoom ''
go
exec vavoom 'blue'
go
eot
# note: prev line :you will need to have a newline immediately after eot
exit
# end of unix script
--- end of make.vavoom --------------------------------------------
--- start of defntest.sh -------------------------------------------
#!/bin/sh
# Raymond Lew 1996-02-01
#
# test script: demonstrate with a bourne shell how an application
# would use the temp table definitions stored in the database
#
# note: you must run setup and make.vavoom first
#
# note: you have to change the passwords, id and etc. for your site
# note: you might have to some inline changes to make this work
# check out the notes within the body
# get the table defn's into a text file
#
# note: next line :you will need to end the line immediately after eot \
isql -Ukryten -Pjollyguy -Sstarbug -w255 << eot \
| grep -v '\-\-\-\-' | grep -v 'defntext ' | grep -v ' affected' > tabletext
exec get_temp_defn ''
go
eot
# note: prev line :you will need to have a newline immediately after eot
# go mess around in vi
vi tabletext
isql -Ukryten -Pjollyguy -Sstarbug -e << eot | more
`cat tabletext`
go
exec vavoom ''
go
exec vavoom 'blue'
go
eot
# note: prev line :you will need to have a newline immediately after eot
exit
# end of unix script
--- end of defntest.sh -------------------------------------------
That's all, folks. Have Fun
I'd like to talk about the difference between a clustered and a non-clustered index. The two are very different and it's very important to understand the difference between the two to in order to know when and how to use each.
I've pondered hard to find the best analogy that I could think of and I've come up with ... the phone book. Yes, a phone book.
Imagine that each page in our phone book is equivalent to a Sybase 2K data page. Every time we read a page from our phone book it is equivalent to one disk I/O.
Since we are imagining, let's also imagine that our mythical ASE (that runs against the phone book) has only enough data cache to buffer 200 phone pages. When our data cache gets full we have to flush an old page out so we can read in a new one.
Fasten your seat belts, because here we go...
A phone book lists everyone by last name. We have an A section, we have a B section and so forth. Within each section my phone book is clever enough to list the starting and ending names for the given page.
The phone book is clustered by last name.
create clustered index on phone_book (last_name)
It's fast to perform the following queries on the phone book:
Searches that don't work well:
In order to determine the answer to the two above we'd have to search the entire phone book. We can call that a table scan.
To help us solve the problem above we can build a non-clustered index.
create nonclustered index on phone_book (phone_number)
Our non-clustered index will be built and maintained by our Mythical ASE as follows:
The phone numbers will be kept in ascending order.
any time we insert, update or delete new numbers, our M-ASE will maintain this secondary data structure. It's such a nice Server.
Now when we ask the question:
Find the address of those whose phone number is 440-1300
we don't look at the phone book directly but go to our new data structure and it tells us which page and row within the page the above phone number can be found. Neat eh?
Draw backs? Well, yes. Because we probably still can't answer the question:
Find the address of those whose prefix is 440
This is because of the data structure being used to implement non-clustered indexes. The structure is a list of ordered values (phone numbers) which point to the actual data in the phone book. This indirectness can lead to trouble when a range or a match query is issued.
The structure may look like this:
------------------------------------ |Phone Number | Page Number/Row | ==================================== | 440-0000 | 300/23 | | 440-0001 | 973/45 | | 440-0002 | 23/2 | | ... | | | 440-0030 | 973/45 | | 440-0031 | 553/23 | | ... | | ------------------------------------
As one can see, certain phone numbers may map to the same page. This makes sense, but we need to consider one of our constraints: our Server only has room for 200 phone pages.
What may happen is that we re-read the same phone page many times. This isn't a problem if the phone page is in memory. We have limited memory, however, and we may have to flush our memory to make room for other phone pages. So the re-reading may actually be a disk I/O.
The Server needs to decide when it's best to do a table scan versus using the non-clustered index to satisfy mini-range type of queries. The way it decides this is by applying a heuristic based on the information maintained when an update statistics is performed.
In summary, non-clustered indexes work really well when used for highly selective queries and they may work for short, range type of queries.
Having suffered many table corruption situations (with 150 ASEs who wouldn't? :-)), I'd say always have a clustered index. With a clustered index you can fish data out around the bad spots on the table thus having minimal data loss.
When you cluster, build the cluster to satisfy the largest percentage of range type queries. Don't put the clustered index on your primary key because typically primary keys are increasing linearly. What happens is that you end up inserting all new rows at the end of the table thus creating a hot spot on the last data page.
For detail rows, create the clustered index on the commonly accessed foreign key. This will aid joins from the master to it.
Use nonclustered index to aid queries where your selection is very selective. For example, primary keys. :-)
This is the same problem another poster had ... basically locking a record to ensure that it hasn't changed underneath ya.
fcasas@ix.netcom.com has a pretty nifty solution if you are using ct-lib (I'll include that below -- hope it's okay Francisco ... :-)) ...
Basically the problem you are facing is one of being a pessimist or an optimist.
I contend that your business really needs to drive this.
Most businesses (from my experience) can be optimistic.
That is, if you are optimistic that the chances that someone is going to change something from underneath the end-user is low, then do nothing about it.
On the other hand, if you are pessimistic that someone may change something underneath the end-user, you can solve it at least as follows:
Use a timestamp on a header table that would be shared by the common data. This timestamp field is a Sybase datatype and has nothing to do with the current time. Do not attempt to do any operations on this column other than comparisons. What you do is when you grab data to present to the end-user, have the client software also grab the timestamp column value. After some thing time, if the end-user wishes to update the database, compare the client timestamp with what's in the database and it it's changed, then you can take appropriate action: again this is dictated by the business.
If users are sharing tables but columns are not shared, there's no way to detect this using timestamps because it's not sufficiently granular.
... Also are you coding to ct-lib directly? If so there's something that you could have done, or may still be able to do if you are using cursors.
With ct-lib there's a ct_describe function that lets you see key data. This allows you to implement optimistic locking with cursors and not need timestamps. Timestamps are nice, but they are changed when any column on a row changes, while the ct_describe mechanism detects changes at the columns level for a greater degree of granularity of the change. In other words, the timestamp granularity is at the row, while ct_describes CS_VERSION_KEY provides you with granularity at the column level.
Unfortunately this is not well documented and you will have to look at the training guide and the manuals very closely.
Further if you are using cursors do not make use of the
[for {read only | update [of column_name_list]}]
of the select statement. Omitting this clause will still get you data that can still be updated and still only place a shared lock on the page. If you use the read only clause you are acquiring shared locks, but the cursor is not updatable. However, if you say
update [of ...
will place updated locks on the page, thus causing contention. So, if you are using cursors don't use the above clause. So, could you answer the following three questions:
You need to be coding with ct-lib ...
Do nothing and be optimistic. We do a lot of that in our shop and it's really not that big of a problem.
Users may clobber each other's changes ... then they'll come looking for you to clobber you! :-)
In System 11, the binding of the internal ordinal value is alleviated so that instead of using the ordinal index value, the index name can be used instead:
select ... from my_table (index my_first_index)
All indexes have an ordinal value assigned to them. For example, the following query will return the ordinal value of all the indexes on my_table:
select name, indid
from sysindexes
where id = object_id("my_table")
Assuming that we wanted to force the usuage of index numbered three:
select ... from my_table(3)
Note: using a value of zero is equivalent to forcing a table scan. Whilst this sounds like a daft thing to do, sometimes a table scan is a better solution than heavy index scanning.
It is essential that all index hints be well documented. This is good DBA practice. It is especially true for Sybase System 10 and below.
One scheme that I have used that works quite well is to implement a table similar to sysdepends in the database that contains the index hints.
create table idxdepends
(
tblname varchar(32) not null -- Table being hinted
,depname varchar(50) not null -- Proc, trigger or app that
-- contains hint.
,idxname varchar(32) not null -- Index being hinted at
--,hintcount int null -- You may want to count the
-- number of hints per proc.
)
Obviously it is a manual process to keep the table populated, but it can save a lot of trouble later on.
System 10 and below.
In System 10 and Sybase 4.X, the I/O scheduler starts at logical device (ldev) zero and works up the ldev list looking for outstanding I/O's to process. Taking this into consideration, the following device fragments (disk init) should be added before any others:
System 10 and below.
At some point you'll wonder if your ASE has been configured with sufficient memory. We hope that it's not during some crisis but that's probably when it'll happen.
The most important thing in setting up memory for a ASE is that it has to be large enough to accommodate:
By not setting the ASE up correctly it will affect the performance of it. A delicate balance needs to be struck where your ASE is large enough to accommodate the users but not too large where it adversely affects the CPU Server (such as causing swapping).
Assumptions made of the reader:
As the ASE starts up, it pre-allocates its structures to support the configuration. The memory that remains after the pre-allocation phase is the available cache.
The available cache is partitioned into two pieces:
The idea is to determine if the buffer cache and the procedure cache are of adequate size. As a DBA you can use dbcc memusage to ascertain this.
The information provided from a dbcc memusage, daunting at first, but taken in sections, is easy to understand and provides the DBA with the vital information that is necessary to determine if more memory is required and where it is required.
If the procedure cache is too small, user connections will get sporadic 701's:
There is insufficient system memory to run this query.
If the buffer cache is too small, response time may be poor or spiky.
The following text describes how to interpret the output of dbcc memusage and to correlate this back to the fundamental question:
Does my ASE have enough memory?
Before delving into the world of dbcc memusage some definitions to get us through.
This is a bit simplistic but it'll do. Read on for more info though.
The cache is maintained as a doubly linked list. The head of the list is where the most recently used pages are placed. Naturally towards the tail of the chain are the least recently used pages. If a page is requested and it is found on the chain, it is moved back to the front of the chain and the information is relayed, thus saving a physical I/O.
But wait! this recycling is not done forever. When a checkpoint occurs any dirty pages are flushed. Also, the parameter cbufwashsize determines how many times a page containing data can be recycled before it has to be flushed out to disk. For OAM and index pages the following parameters apply coamtrips and cindextrips respectively.
The size of procedure cache is determined by the percentage of remaining memory configured for this Server parameter after ASE memory needs are met.
When the ASE starts up it pre-allocates its data structures to support the current configuration. For example, based on the number of user connections, additional netmem, open databases and so forth the dataserver pre-allocates how much memory it requires to support these configured items.
What remains after the pre-allocation is the available cache. The available cache is divided into buffer cache and procedure cache. The sp_configure "procedure cache" parameter determines the percentage breakdown. A value of 20 would read as follows:
20% of the available cache is dedicated to the procedure cache and 80% is dedicated to the buffer cache.
dbcc memusage takes a snapshot of your ASE's current memory usage and reports this vital information back to you. The information returned provides information regarding the use of your procedure cache and how much of the buffer cache you are currently using.
An important piece of information is the size of the largest query plan. We'll talk about that more below.
It is best to run dbcc memusage after your ASE has reached a working set. For example, at the end of the day or during lunch time.
Running dbcc memusage will freeze the dataserver while it does its work. The more memory you have configured for the ASE the longer it'll take. Our experience is that for a ASE with 300MB it'll take about four minutes to execute. During this time, nothing else will execute: no user queries, no sp_who's...
In order to run dbcc memusage you must have sa privileges. Here's a sample execution for discussion purposes:
1> /* send the output to the screen instead of errorlog */
2> dbcc traceon(3604)
3> go
1> dbcc memusage
2> go
Memory Usage:
Meg. 2K Blks Bytes
Configured Memory:300.0000 153600 314572800
Code size: 2.6375 1351 2765600
Kernel Structures: 77.6262 39745 81396975
Server Structures: 54.4032 27855 57045920
Page Cache:129.5992 66355 135894640
Proc Buffers: 1.1571 593 1213340
Proc Headers: 25.0840 12843 26302464
Number of page buffers: 63856
Number of proc buffers: 15964
Buffer Cache, Top 20:
DB Id Object Id Index Id 2K Buffers
6 927446498 0 9424
6 507969006 0 7799
6 959446612 0 7563
6 116351649 0 7428
6 2135014687 5 2972
6 607445358 0 2780
6 507969006 2 2334
6 2135014687 0 2047
6 506589013 0 1766
6 1022066847 0 1160
6 116351649 255 987
6 927446498 8 897
6 927446498 10 733
6 959446612 7 722
6 506589013 1 687
6 971918604 0 686
6 116351649 6 387
Procedure Cache, Top 20:
Database Id: 6
Object Id: 1652357121
Object Name: lp_cm_case_list
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 16
Size of plans: 0.323364 Mb, 339072.000000 bytes, 176 pages
----
Database Id: 6
Object Id: 1668357178
Object Name: lp_cm_subcase_list
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 10
Size of plans: 0.202827 Mb, 212680.000000 bytes, 110 pages
----
Database Id: 6
Object Id: 132351706
Object Name: csp_get_case
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 9
Size of plans: 0.149792 Mb, 157068.000000 bytes, 81 pages
----
Database Id: 6
Object Id: 1858261845
Object Name: lp_get_last_caller_new
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 2
Size of plans: 0.054710 Mb, 57368.000000 bytes, 30 pages
...
1> /* redirect output back to the errorlog */
2> dbcc traceoff(3604)
3> go
The output may appear overwhelming but it's actually pretty easy to parse. Let's look at each section.
This section provides a breakdown of the memory configured for the ASE.
Memory Usage:
Meg. 2K Blks Bytes
Configured Memory:300.0000 153600 314572800
Code size: 2.6375 1351 2765600
Kernel Structures: 77.6262 39745 81396975
Server Structures: 54.4032 27855 57045920
Page Cache:129.5992 66355 135894640
Proc Buffers: 1.1571 593 1213340
Proc Headers: 25.0840 12843 26302464
Number of page buffers: 63856
Number of proc buffers: 15964
The Configured Memory does not equal the sum of the individual components. It does in the sybooks example but in practice it doesn't always. This is not critical and it is simply being noted here.
The Kernel Structures and Server structures are of mild interest. They can be used to cross-check that the pre-allocation is what you believe it to be. The salient line items are Number of page buffers and Number of proc buffers.
The Number of proc buffers translates directly to the number of 2K pages available for the procedure cache.
The Number of page buffers is the number of 2K pages available for the buffer cache.
As a side note and not trying to muddle things, these last two pieces of information can also be obtained from the errorlog:
... Number of buffers in buffer cache: 63856. ... Number of proc buffers allocated: 15964.
In our example, we have 15,964 2K pages (~32MB) for the procedure cache and 63,856 2K pages (~126MB) for the buffer cache.
The buffer cache contains the data pages that the ASE will be either flushing to disk or transmitting to a user connection.
If this area is too small, the ASE must flush 2K pages sooner than might be necessary to satisfy a user connection's request.
For example, in most database applications there are small edit tables that are used frequently by the application. These tables will populate the buffer cache and normally will remain resident during the entire life of the ASE. This is good because a user connection may request validation and the ASE will find the data page(s) resident in memory. If however there is insufficient memory configured, then these small tables will be flushed out of the buffer cache in order to satisfy another query. The next time a validation is requested, the tables will have to be re-read from disk in order to satisfy the request. Your performance will degrade.
Memory access is easily an order of magnitude faster than performing a physical I/O.
In this example we know from the previous section that we have 63,856 2K pages (or buffers) available in the buffer cache. The question to answer is, "do we have sufficient buffer cache configured?"
The following is the output of the dbcc memusage regarding the buffer cache:
Buffer Cache, Top 20:
DB Id Object Id Index Id 2K Buffers
6 927446498 0 9424
6 507969006 0 7799
6 959446612 0 7563
6 116351649 0 7428
6 2135014687 5 2972
6 607445358 0 2780
6 507969006 2 2334
6 2135014687 0 2047
6 506589013 0 1766
6 1022066847 0 1160
6 116351649 255 987
6 927446498 8 897
6 927446498 10 733
6 959446612 7 722
6 506589013 1 687
6 971918604 0 686
6 116351649 6 387
| Value | Definition |
|---|---|
| 0 | Table data |
| 1 | Clustered index |
| 2-250 | Nonclustered indexes |
| 255 | Text pages |
It's obvious that the first 10 items take up the largest portion of the buffer cache. Sum these values and compare the result to the amount of buffer cache configured.
Summing the 10 items nets a result of 45,263 2K data pages. Comparing that to the number of pages configured, 63,856, we see that this ASE has sufficient memory configured.
I follow the following rules of thumb to determine when I need more buffer cache:
If the procedure cache is not of sufficient size you may get sporadic 701 errors:
There is insufficient system memory to run this query.
In order to calculate the correct procedure cache one needs to apply the following formula (found in ASE Troubleshooting Guide - Chapter 2, Procedure Cache Sizing):
proc cache size = max(# of concurrent users) * (size of the largest plan) * 1.25
The flaw with the above formula is that if 10% of the users are executing the largest plan, then you'll overshoot. If you have distinct classes of connections whose largest plans are mutually exclusive then you need to account for that:
ttl proc cache = proc cache size * x% + proc cache size * y% ...
The max(# of concurrent users) is not the number of user connections configured but rather the actual number of connections during the peak period.
To compute the size of the largest [query] plan take the results from the dbcc memusage's, Procedure Cache section and apply the following formula:
query plan size = [size of plans in bytes] / [number of plans]
We can compute the size of the query plan for lp_cm_case_list by using the output of the dbcc memusage:
... Database Id: 6 Object Id: 1652357121 Object Name: lp_cm_case_list Version: 1 Uid: 1 Type: stored procedure Number of trees: 0 Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages Number of plans: 16 Size of plans: 0.323364 Mb, 339072.000000 bytes, 176 pages ---- ...
Entering the respective numbers, the query plan size for lp_cm_case_list is 21K:
query plan size = 339072 / 16
query plan size = 21192 bytes or 21K
The formula would be applied to all objects found in the procedure cache and the largest value would be plugged into the procedure cache size formula:
| Object | Query Plan Size |
|---|---|
| lp_cm_case_list | 21K |
| lp_cm_subcase_list | 21K |
| csp_get_case | 19K |
| lp_get_last_caller_new | 28K |
The size of the largest [query] plan is 28K.
Entering these values into the formula:
proc cache size = max(# of concurrent users) * (size of the largest plan) * 1.25
proc cache size = 491 connections * 28K * 1.25
proc cache size = 17,185 2K pages required
Our example ASE has 15,964 2K pages configured but 17,185 2K pages are required. This ASE can benefit by having more procedure cache configured.
This can be done one of two ways:
- procedure cache =
- [ proposed procedure cache ] /
( [ current procedure cache ] + [ current buffer cache ] )The new procedure cache would be 22%:
procedure cache = 17,185 / ( 15,964 + 63,856 )
procedure cache = .2152 or 22%
- mem size =
- ([ proposed procedure cache ]) /
([ current procedure cache ] / [ current configured memory ])The new memory size would be 165,399 2K pages, assuming that the procedure cache is unchanged:
mem size = 17,185 / ( 15,964 / 153,600 )
mem size = 165,399 2K pages
There are many advantages to using stored procedures (unfortunately they do not handle the text/image types):
As recently pointed out in the Sybase-L list, the showplan information that was here is terribly out of date. It was written back when the output from ASE and MS SQL Server were identical. (To see just how differenet they have become, have a look at the O'Reilly book "Transact-SQL Programming". It does a line for line comparison.) The write up in the Performance and Tuning Guide is excellent, and this section was doing nothing but causing problems.
If you do have a need for the original document, then it can be found here, but it will no longer be considered part of the official FAQ.
This is needed for System 10 and Sybase 4.9.2 where there is no sp_sysmon command available.
Fine tune the waitfor for your application. You may need TS Role -- see Q3.1.
use master
go
dbcc traceon(3604)
dbcc monitor ("clear", "all", "on")
waitfor delay "00:01:00"
dbcc monitor ("sample", "all", "on")
dbcc monitor ("select", "all", "on")
dbcc traceon(8399)
select field_name, group_name, value
from sysmonitors
dbcc traceoff(8399)
go
dbcc traceoff(3604)
go
dbcc procbuf gives a listing of the current contents of the procedure cache. By repeating the process at intervals it is possible to watch procedures moving down the MRU-LRU chain, and so to see how long procedures remain in cache. The neat thing about this approach is that you can size your cache according to what is actually happening, rather than relying on estimates based on assumptions that may not hold on your site.
To run it:
dbcc traceon(3604) go dbcc procbuf go
If you use sqsh it's a bit easier to grok the output:
dbcc traceon(3604); dbcc procbuf;|fgrep <pbname>
See Q1.5.7 regarding procedure cache sizing.
If you know that you are going to be using a text/insert column immediately, insert the row setting the column to a non-null value.
There's a noticeable performance gain.
Unfortunately, text and image datatypes cannot be passed as parameters to stored procedures. The address of the text or image location must be created and returned where it is then manipulated by the calling code. This means that transactions involving both text and image fields and stored procedures are not atomic. However, the datatypes can still be declared as not null in the table definition.
Given this example -
create table key_n_text ( key int not null, notes text not null )
This stored procedure can be used -
create procedure sp_insert_key_n_text @key int, @textptr varbinary(16) output as /* ** Generate a valid text pointer for WRITETEXT by inserting an ** empty string in the text field. */ insert key_n_text ( key, notes ) values ( @key, "" ) select @textptr = textptr(notes) from key_n_text where key = @key return 0 go
The return parameter is then used by the calling code to update the text field, via the dbwritetext() function if using DB-Library for example.