How do I know the current cache size of Adaptive server
Anywhere 7 database. Can I use it to optimize performance
and how can I increase it.

See CurrentCacheSize in either in the output from
call sa_eng_properties( );
or
select property('CurrentCacheSize')

<deepali.sahni (AT) ebookers (DOT) com> wrote

Thank You Nick.. How can I increase the cache size to
improve performance.

Deepali,

The cache is just that - a cache. The only 'tweak' to the cache you can
make is by making more of it available to the database server (See:
"dbsrv7 -c": http://download.sybase.com/pdfdocs/awg0704e/dbrfen7.pdf )

Unfortunately, since you're on a 32-bit process, and on version 7.0,
there are limitations to how much cache you can allocate, depending on
your operating system. If you're on a 32-bit version of Windows, even
with a full 4GB of RAM, you'll only be able to allocate up to ~1.8GB of
cache for the database server. (This is due to the limitation of Windows
splitting the 4GB into 2GB user process space and 2GB of system process
space:
http://www.microsoft.com/whdc/system...AE/PAEmem.mspx ).
If you're on Windows x64, you can get slightly more by default - it'll
be around ~2.7GB. (See the chart of 'maximum non-awe cache sizes' here:
http://dcx.sybase.com/1101en/dbadmin...-dbserver.html )

If you're already at the 1.8GB maximum on Windows x86, the only way to
allocate more cache (up to 2.7GB) would be to change the system
configuration to split the 4GB of address space into 3GB user space and
1GB system space. This is accomplished at the OS level by setting the
/3GB switch in the boot.ini:
http://msdn.microsoft.com/en-us/library/ms791558.aspx

---

To perform actual performance and tuning, sizing the cache is only one
very small part of this process. Additional time should be spent looking
at what operations are performed against the database server and how to
optimize them. This is a very involved process and there are no "silver
bullet" fixes:
http://iablog.sybase.com/paulley/200...ilver-bullets/

I'd recommend reading Glenn Paulley's papers 'Diagnosing Application
Performance Issues with SQL Anywhere':
http://iablog.sybase.com/paulley/wp-...08/07/perf.pdf

and his 'Capacity Planning' talk:
http://iablog.sybase.com/paulley/wp-...ng_webinar.ppt

Regards,

Deepali wrote:
--
Jeff Albion, Sybase iAnywhere

iAnywhere Developer Community :
http://www.sybase.com/developer/libr...ere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summ...&timeframe =0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/

Thank You so much Jeff.. The server is MS Win 2000 SP4 32
bit, has 2 GB RAM and 3.06 GHz CPU and a virtual server. Can
I use AWE. Besides that I am also planning to move .mlg file
to a different drive as both log file and db file are placed
on the same drive as mlg file.
I am also looking at indexing as an option.

How much % of improvement can I expect by that.
Thanks,
Deepali

Also, the current cache size is 1.3 GB.

You seem to be guessing at solutions to problems for which there is no
well-defined understanding. Perhaps you need to step back a bit,
evaluate and determine the problems that you have, and determine
appropriate solutions to those problems.

So, lets start from scratch:
*What performance problems are you seeing?
*Is the behaviour occurring in general or at specific times i.e.
specific queries or activities in the database?
*What have you done to narrow down the issue?


Certainly changing cache, adding *appropriate* indexes, and so forth may
yield some performance gains.

Lets look at you question regarding cache size. You will need to
determine if your application is cache starved. Allocating memory to a
bigger cache means that memory is not available for other purposes. So,
you want to ensure that you will get benefit. To determine if additional
cache is needed, look at you MinCacheSize and MaxCacheSize server
properties so that you know the systems memory range configuration. You
then want to look at PeakCacheSize (after running your server with a
typical load). If the PeakCacheSize is near or equal to MaxCacheSize,
your MaxCacheSize is set too low. If you are not currently setting the
-ch option at engine start, you make want to adjust the size at the
onset. I would suggest as a starting point to use a percentage of memory
i.e., -ch 75P. You want want to monitor these properties on a ongoing
basis so that the values can be tweaked as the system matures.



Deepali wrote:

Thank you for replying Chris..
I am relatively new to Sybase so I am trying to gather info
for a project to optimize performance for a ASA7 database. I
read ASA 7 database administration and sql user guide. But
the wizards I read have not been implemented in Version 7
like Index consultant and procedure profiling.
No one has any info as to why the server is so slow. Only
info that I have been provided with is that the server is
very slow.Crystal reports are being used to export data by
the user.
So, I tried using solutions mentioned in the guide as I
really don't have an idea as to where the problem is.
If you can guide me through, I will be really obliged.
and regarding the cache size, all mincachesize,
maxcachesize, peakcachesize and currentcachesize are 1331200
KB and sysytem has 2 GB RAM.
Thanks again, Deepali. Email: deepali.sahni (AT) ebookers (DOT) com