Solved

sp_configure "statement cache size"

Posted on 2011-02-25
1
1,448 Views
Last Modified: 2012-05-11
Hello All,

Any advice on configuration of 'statement cache size' because as usual Sybase manual is not clear cut on its example...


Many Thanks.
0
Comment
Question by:tranicus
1 Comment
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 34986254
I feel your frustration. I'm yet to discover any robust method of doing this either. :)

What I've done is pick a figure maybe 25% the size of procedure cache, and watch carefully what happens. sp_sysmon will give you a pretty good idea if that's too small or too large - which also means it's difficult to test this prior to production unless you have a way of simulating full production load.

Note if you're on a version of ASE that has this, you must also enable "literal autoparam" as statement cache is almost useless without it.

A corollary to that is if you are not on a version of ASE that has that, it may not help very much to use statement cache.

Test this most carefully in production - if statement cache is too small statements start failing with run-time errors!

Like most fancy cache techniques this is the luxury of having enough memory to play with. If you only have 2Gb of memory for ASE you probably don't have enough to use this sensibly... but try it and see, that's just a rule of thumb, not a natural law.

Lastly note that when statement cache is being used it will reduce procedure cache, so to preserve the status quo it's best if procedure cache is increased by the same amount you configure statement cache for. (See above re. "luxury of having plenty of memory".)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Agreggate sums in sql 1 523
detecting a database behind a website 3 367
SyBase Query Syntax Case When 7 127
SQL Syntax 10 70
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Read about the ways of improving workplace communication.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question